Summary
Non Standard SQL restrictions on DML/DDL in BIP 12c
Content
I am working to migrate a set of custom BI reports that were developed by a 3rd party for one of our clients
We are moving from BIP 11g (11.1.1.7.160119) to BIP 12c (12.2.1.2.0) on WIndows x64, with a SQL Server back end
I have copied all the reports and associated data models, have set up the data source, roles and folder permssions, and can get most of the reports to run. There is however a set of reports which all have a similar structure and are failing with the error "SQLInjection Error: DML / DDL Operations not allowed". I have attached the engine log with the stack dump.
These reports all work OK in the old environment. I have looked at the data model and can see that they use non standard SQL. The start of one of the reports looks like this:
DECLARE @workstreamname as NVARCHAR(50);
DECLARE @workstreamCursor as CURSOR;
SET @workstreamCursor = CURSOR FOR
SELECT ws_workstream FROM WSLIST2 ORDER BY WS_ID ;
IF object_id('tempdb..#gate3_details') IS NULL
BEGIN
CREATE TABLE #gate3_details(
[REPORT_TYPE] [VARCHAR](100) NOT NULL,
[PJ_P6_PROJ_ID] [VARCHAR](100) NULL,
[PJ_P6_PROJ_NAME] [VARCHAR](100) NOT NULL,
[PJ_NMC_SITE_MGR] [VARCHAR](100) NULL,
[PJ_NMC_DELIVERY_MGR] [VARCHAR](100) NULL,
[PREV_GATE_MISS_ALIGNMENT] [INT] NULL,
[CURR_GATE_MISS_ALIGNMENT] [INT] NULL,
[GATE_MISS_ALIGNMENT_PER] [VARCHAR](20) NULL
)
END
else
DELETE from #gate3_details
insert into #gate3_details
select report_type,pj_p6_proj_id,pj_p6_proj_name, ...
So the author is conditionally creating a SQL Server temp table and then starts populating it with query results - this is presumably what is triggering the error in the new system.
I have discovered a passing reference to a file named NSQConfig.ini in the BIP documentation which looked as if it contained a setting that might allow this code to execute, but having created the file, created the correct folder and put it there it has made no difference at all, despite shutting down and restarting WebLogic.
I cannot find any documentation that states whether BIP 12c now simply refuses to parse DML/DDL in non standard SQL or whether this can be configured somewhere. Does anyone have an answer for this?
