The Risk of SQL Injection in Oracle APEX Dynamic Report

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 result. Let's understand this by example. The below query is intended to return the department number and department name for the department name entered by the user in the P25_DEPTNAME field. 

L_sql := ' SELECT DEPTNO,DEPTNAME FROM DEPT WHERE DEPTNAME = '''||:P25_DEPTNAME||'' ;

When the department name 'SALES' is supplied by the user, the resulting query returns the result as intended:

SELECT DEPTNO,DEPTNAME FROM DEPT WHERE DEPTNAME = 'SALES' ;

But what if the user enters the following value in the P25_DEPTNAME text field?

' UNION SELECT USERID,USERNAME FROM USERS;

The resulting query is then:

SELECT DEPTNO,DEPTNAME FROM DEPT WHERE DEPTNAME = '' UNION SELECT USERID, USERNAME FROM USERS;

This will then return USERID and USERNAME from the USERS table and the attacker will have access to users' data. 

Let's understand this in more detail taking the example of a dynamic report. Consider the report page shown below which is the same report that I explained in the previous post. In addition, here I added two new fields, Filter Condition and Report Query. Users can filter the report data by providing the condition in the Filter Condition field. Report Query field will display the final query used to generate the report.  The code for the report region is listed below.

DECLARE
    p_table VARCHAR2(20)  := :P51_TABLE;
    p_col   VARCHAR2(500) := REPLACE(:P51_COLUMNS,':',',');
    L_query VARCHAR2(1000);
BEGIN
    L_query := 'SELECT '|| p_col ||' FROM '||p_table;

    IF :P2_WHERE IS NOT NULL THEN
        L_query := L_query ||' WHERE '|| :P51_WHERE; 
    END IF;

    RETURN L_query;    
END;    

Entering the DNAME = 'SALES' condition in the Filter Condition field and clicking Generate Report button displays the DEPNO and DNAME fields from the DEPT table which is expected.

But now take a closer look at the Filter Condition filed below. User injected SQL code using this field which modifies the query as display in the Report Query filed. Now instead of displaying results from the DEPT table, this query will return the users' data from the USERS table.

This incorrectly created dynamic report now gives direct access to your underlying schema to the attacker. Using ALL_OBJECTS and ALL_TAB_COLUMNS dictionary views, an attacker can know the structure of any table and query that table.

In general, whenever you are concatenating the user-supplied values directly into the SQL statement, you will always end up having a SQL Injection issue. 

The Golder Rules:

• Never trust user input.
• Avoid text-based input for the dynamic reports if possible. Instead, use list-based input that forces the user to select a value from the list.
• Never concatenate user-supplied values directly into SQL statements without sanitizing them.
• If you are concatenating schema names, table names, column names, etc., supplied by the user, always validate them using the DBMS_ASSERT package. 
• Explicitly check for possible code fragments or some dangerous characters by writing APEX Validation for validating the user input. For example, you may refuse to accept Filter Condition that contains UNION keyword, comment characters(--), or semicolon(;).

The Solution:

To implement these rules in the Build Report page, I created three validations of type Function Body (returning boolean) to validate table name, column name, and filter condition fields.

The Build Report page should have the structure as shown in the below image.

Validate Table Name Validation: 

The below listing shows the PL/SQL code for validation of the table name field that returns false if the table name is invalid. SQL_OBJECT_NAME routine of  DBMS_ASSERT package checks the table name provided in the P51_TABLE field is a valid database object or not. It will throw an ORA-44002: invalid object name exception if the provided value is not an object in the database. 

BEGIN
    IF :P51_TABLE = DBMS_ASSERT.SQL_OBJECT_NAME(:P51_TABLE)
    THEN
        return true;
    END IF;    
EXCEPTION
    WHEN OTHERS THEN
        return false;        
END;

Validate Column Name Validation:

The below listing shows the PL/SQL code for validation of the column name field.  SIMPLE_SQL_NAME function of  DBMS_ASSERT package checks the input string conforms to the basic characteristics of a simple SQL name. If the input string is not a valid SQL name then an ORA-44003: invalid SQL name will be raised. 

DECLARE
    p_col   VARCHAR2(50);
    L_str_arr apex_t_varchar2;
BEGIN
    --Validate column names
    L_str_arr := APEX_STRING.SPLIT(:P51_COLUMNS, ':'); 
     
     FOR i IN 1..L_str_arr.COUNT
     LOOP
        p_col := DBMS_ASSERT.SIMPLE_SQL_NAME(L_str_arr(i));
     END LOOP;
     RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN
    RETURN FALSE;
END ;

Validate Filter Condition Validation:

The below listing shows the PL/SQL code for validation of the filter condition. Here, I blacklisted certain values such as union, select, semicolon(;), or comment characters(--). This validation will return false if the string contains as of these values. 

RETURN NOT REGEXP_LIKE (:P51_WHERE, 'union|Select|;|--', 'i');

Now if an attacker tries to inject the code using the Filter Condition field, the validation will fail and he will get an error as shown in the below image.

Summary

The data-centric applications created using Oracle APEX make heavy use of SQL and PL/SQL and because of that Oracle APEX applications are more vulnerable to SQL Injection if proper care is not taken during the development of dynamic SQL queries. The best way to prevent SQL Injection is to use static SQL and you will always be safe from it. However, this may not always possible and developers must have to take preventive steps during the development stage to safeguard the applications against SQL Injection attacks.