Dynamic SQL Injection With Oracle ERP Cloud

In this previous article, we learned how to design and Develop an Oracle Cloud BI Report. We will use the same Report and convert it Into a Dynamic SQL Injection based Report.

Log in to Oracle Cloud Applications and Go to Tools in the Navigator and click on Reports and Analytics. Click on Browse Catalog to launch the BI workspace.

Data Model Update

• Log in to Oracle and open the Data Model created in the previous steps. Please update the Data Model with below PLSQL block.

• As you can see in the screenshot above, query1 is a variable, that will be passed to the Data Model as a base64 encoded string. We will use a standard out of the box function to decode the string to a SQL query and pass it to a cursor to open as a Reference Cursor.
• Please note that the type of SQL is a Procedure Call.
• Please check “Bind Parameter Value As Comma Separated String” in case you want to pass multiple SQL statements.
• Please click OK to save the query screen and save the data model.
• You can optionally specify the row tag name for the XML output.

Adding Parameters

• Please click on the Parameters tab and add a parameter to pass the SQL query as input.
• Add another parameter, xdo_cursor, which acts as a CURSOR output for the result set.

• Generate base64 encoded string using any Database tool (e.g. SQL Developer).
• Connect to the Oracle Database using SQL developer and generated base64 String as below. The input SQL statement is as follows: Select person_id from per_all_people_f
  
• As reviewed from the output above, Base64 encoded string is c2VsZWN0IHBlcnNvbl9pZCBmcm9tIHBlcl9hbGxfcGVvcGxlX2Y=

Generate Data Model Output

• Please click on Data Sets, “QueryEmployees,” and click on the data tab and paste the SQL encoded string in the input variable as below:

• Click the View button to see the data as shown below:

• As you can see, we have passed the SQL query as an input to generate the output successfully.  In a real-time scenario, we can create a common Data Model and pass SQL queries from the integration layer to get the data. 

I hope this will prove to be a useful article for your ERP Cloud work.