Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Non Standard SQL restrictions on DML/DDL in BIP 12c

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?
Answers
-
Hi,
DML/DDL keywords are not allowed as part of a SQL querys in BI Publisher data models.
The error received is the intended functionality.
To solve the issue, change the data model and remove all DML/DDL keywords from the sql selects, column names, and / or aliases in any sql query.
Note: If any of the replies in this thread were helpful/correct, kindly mark it accordingly as this would help others who can also benefit from it.
Regards,
Violeta
0 -
Violeta
Can you please confirm that this a deliberate change in product behaviour that was introduced with 12c? Because the code shown above certainly works in 11g and is being used every week by the client to generate their reports.
If there is no longer any way for this code to execute without error then we will need to rewrite it, but I need a definite statement from Oracle before we can justify that to the client
0 -
Rjebb,
For an answer to your question, please open a SR with BI Publisher team.
Regards,
Violeta
0 -
Try this Property and restart the servers.
cd $DOMAIN_HOME/config/fmwconfig/biconfig/bipublisher/Admin/Configuration
xdo.cfg --> add this property in <properties> section
<property name="server.ALLOW_DB_MODIFICATIONS">true</property>
xmlp-server-config.xml --> Add this property at end of
<property name="server.ALLOW_DB_MODIFICATIONS" value="true"></property>
0 -
Hi,
You can refer existing MOS doc for your reference -
SQLInjectionError DML/DDL Operations Not Allowed In BIP 12c (Doc ID 2693837.1)
Regards,
Arjun
0