Rutvik Prajapati

In my last post , I explained how to create a report using dynamic SQL. In that post, I only focused on the functionality of how to create the dynamic report and totally overlooked the security risk that comes with it in APEX. Thanks to Alex for highlighting that security risk.  A dynamic report gives the user the flexibility to create the report at the run time based on the input provided by the user. However, it also invites a potential risk of SQL injection if proper care is not taken during the development. As a developer, we must develop applications by keeping security in mind. In this blog, I will explain SQL Injection, how incorrectly created dynamic reports can invite a SQL injection risk, and then explain the steps to prevent the SQL Injection attack.  What is SQL Injection? SQL Injection is a code injection technique in which a malicious user(attacker) inserts the SQL code into a text-based entry field to manipulate the syntax of the query that returns the unintended resul

In Oracle APEX, most of the time you create a report based on a table or a SQL query. For both these options, the structure of the query has been decided at the time of creating the report. But sometimes it's not possible to define the query while creating the report and you need to build the query at the runtime depending on the user's input. You can create a report based on a dynamic SQL by setting the source type of the report to be Function Body returning SQL Query. The source of such a report is a function that computes the SQL query and returns it as a string. Consider the report page shown below. It has two regions. The top region enables a user to select the desired table and select the columns to display. Clicking the Generate Report button displays the report in the bottom region. The top region Specify your Report  is a static region that has two items and a button. The first item named P51_TABLE is a select list that displays the name of the tables. It is defined b