Avoid SQL injection with DBMS_ASSERT

A simple internet search will reveal some serious computer system hacks that were, fundamentally, a result of SQL injection. If you plan on doing any significant amount of database development you need to be aware of what SQL injection is and how to identify and mitigate it within your system.

In Oracle the easiest way to avoid SQL injection is to simply use static SQL with bind variables. Stick to this strategy and you can’t go wrong. However, there may be some scenarios where it isn’t possible to use simple queries with binds and dynamic SQL is required. One scenario that regularly crops up in applications I’ve worked on is a generic search screen; uses are presented with a screen containing a number of fields for entering search criteria. The system dynamically generates an SQL statement using only the fields that have been filled in. Once you’re dealing with dynamic SQL you’ve got a potential SQL injection problem. Thankfully Oracle has provided a package to help us; DBMS_ASSERT.

I’ll illustrate the use of DBMS_ASSERT using a simple dynamic SQL routine. The following DO_SQL procedure takes as input parameters a schema name, a table name, a column name and a “starts with” data filter for the column requested. The routine dynamically generates an SQL statement of the form:

SELECT <column_name>
FROM   <schema_name>.<table_name>
WHERE  <column_name> LIKE '<starts_with>%';

and returns a cursor with the results from the generated statement. The routine, wrapped into a simple SQL*PLus test harness, is:

VARIABLE rc REFCURSOR

DECLARE

   l_table_owner VARCHAR2(100);
   l_table_name  VARCHAR2(100);
   l_column_name VARCHAR2(100);
   l_starts_with VARCHAR2(100);

   PROCEDURE do_sql (p_table_owner IN  VARCHAR2
                    ,p_table_name  IN  VARCHAR2
                    ,p_column_name IN  VARCHAR2
                    ,p_starts_with IN  VARCHAR2
                    ,p_rc          OUT SYS_REFCURSOR)
   AS                 
      l_sql_string VARCHAR2(32767);
   BEGIN
      l_sql_string := 'SELECT ' || p_column_name ||
                      ' FROM  ' || p_table_owner ||'.' || p_table_name ||
                      ' WHERE ' || p_column_name || ' LIKE ''' || p_starts_with || '%'''; 

      OPEN p_rc FOR l_sql_string;      
   END do_sql;
   
BEGIN   
   l_table_owner  := 'SCOTT';
   l_table_name   := 'DEPT';                  
   l_column_name  := 'DNAME';                 
   l_starts_with  := 'A';

   do_sql (p_table_owner => l_table_owner
          ,p_table_name  => l_table_name
          ,p_column_name => l_column_name
          ,p_starts_with => l_starts_with
          ,p_rc          => :rc);
END;
/

PRINT rc

Running the routine using the inputs of:

• schema name: SCOTT
• table name: DEPT
• column_name: DNAME
• starts with: A

produces the following:

DNAME
--------------
ACCOUNTING

Before we get to DBMS_ASSERT let’s now look at how we might exploit this routine using SQL injection. Let’s change the inputs to the following:

• schema name: SCOTT
• table name: DEPT
• column_name: DNAME
• starts with: A” UNION ALL SELECT username FROM all_users —

which produces the output:

DNAME
----------------------
SCOTT
APEX_APPS
OEHRAPP
--snip --
SYSDG
SYSBACKUP
SYSTEM
AUDSYS
SYS

44 rows selected.

In the above I managed to obtain a listing of the users in the database by “extending” the query to run SELECT USERNAME FROM ALL_USERS. By modifying the input parameters I managed to execute the query:

SELECT DNAME 
FROM   SCOTT.DEPT 
WHERE  DNAME LIKE 'A' 
UNION  ALL 
SELECT username 
FROM   all_users --%'

Note, the double hyphen at the end, which denotes the start of a comment in SQL, is to remove the %’ that the generated SQL puts on the end.

Let’s now look at how we can protect ourselves against this using DBMS_ASSERT. We’ll look at the following routines from the package:

• SCHEMA_NAME
• SQL_OBJECT_NAME
• SIMPLE_SQL_NAME
• QUALIFIED_SQL_NAME
• ENQUOTE_NAME
• ENQUOTE_LITERAL

SCHEMA_NAME

This routine can be used to ensure that a value provided for a schema is actually a schema within the database. If the value isn’t a schema name then an exception is thrown. In our example, we would use the routine as follows:

l_sql_string := 'SELECT ' || p_column_name ||
                ' FROM  ' || DBMS_ASSERT.SCHEMA_NAME(p_table_owner) ||'.' || p_table_name ||
                ' WHERE ' || p_column_name || ' LIKE ''' || p_starts_with || '%'''; 

Providing a value that is not a schema in the database results in:

ORA-44001: invalid schema

SQL_OBJECT_NAME

Similar to the SCHEMA_NAME routine, this one checks that the name provided is a valid SQL object name that exists in the database. We could use this routine to check that the table we want to query exists, i.e.:

l_sql_string := 'SELECT ' || p_column_name ||
                ' FROM  ' || DBMS_ASSERT.SQL_OBJECT_NAME(p_table_owner||'.' || p_table_name)  ||
                ' WHERE ' || p_column_name || ' LIKE ''' || p_starts_with || '%'''; 

This would be a safer test than just a test to check that the schema exists. Providing a value that is not an object in the database results in:

ORA-44002: invalid object name

SIMPLE_SQL_NAME

This routine checks that the value provided satisfies the rules for an object name without actually verifying that such an object exists in the database. In our example we would use it as follows:

l_sql_string := 'SELECT ' || p_column_name ||
                ' FROM  ' || DBMS_ASSERT.SIMPLE_SQL_NAME(p_table_owner) ||'.'
                          || DBMS_ASSERT.SIMPLE_SQL_NAME(p_table_name) ||
                ' WHERE ' || p_column_name || ' LIKE ''' || p_starts_with || '%'''; 

If we wanted to ensure that the column reference is actually a column, as opposed to some sort of calculation, then we could apply SIMPLE_SQL_NAME to the column reference too, i.e.:

l_sql_string := 'SELECT ' || DBMS_ASSERT.SIMPLE_SQL_NAME(p_column_name) ||
                ' FROM  ' || DBMS_ASSERT.SIMPLE_SQL_NAME(p_table_owner) ||'.'
                          || DBMS_ASSERT.SIMPLE_SQL_NAME(p_table_name) ||
                ' WHERE ' || p_column_name || ' LIKE ''' || p_starts_with || '%'''; 

Providing a parameter that violates the naming rules results in:

ORA-44003: invalid SQL name

QUALIFIED_SQL_NAME

While the SIMPLE_SQL_NAME can only be used to validate each component of an overall object name the QUALIFIED_SQL_NAME routine can be used for a fully qualified object name, inclusive of database link component if required. In our example we would use it as follows:

l_sql_string := 'SELECT ' || p_column_name ||
                ' FROM  ' || DBMS_ASSERT.QUALIFIED_SQL_NAME(p_table_owner || '.' || p_table_name) ||
                ' WHERE ' || p_column_name || ' LIKE ''' || p_starts_with || '%'''; 

Passing in an invalid name results in:

ORA-44004: invalid qualified SQL name

Once again, this routine does not verify that an SQL object exists with such a name, only if the name is a valid name. In our example, running the routine with a valid name that does not exist results in;

ORA-00942: table or view does not exist

ENQUOTE_NAME

Oracle will accept names using any character, including spaces and punctuation, if the name is enclosed in double quotes. For example “A silly column name!” is a legitimate name. DBMS_ASSERT.ENQUOTE_NAME wraps a name in double quotes to handle this situation. By default it will also make all alphabetic characters upper case unless the second parameter is set to FALSE. Names already enclosed in double quotes are left alone.

In our example, we would use the routine as follows:

l_sql_string := 'SELECT ' || DBMS_ASSERT.SIMPLE_SQL_NAME(DBMS_ASSERT.ENQUOTE_NAME(p_column_name)) ||
                ' FROM  ' || DBMS_ASSERT.QUALIFIED_SQL_NAME(DBMS_ASSERT.ENQUOTE_NAME(p_table_owner) || '.' 
                          || DBMS_ASSERT.ENQUOTE_NAME(p_table_name)) ||
                ' WHERE ' || p_column_name || ' LIKE ''' || p_starts_with || '%'''; 

Once a name is enclosed in double quotes it will pass the checking done within DBMS_ASSERT.SIMPLE_SQL_NAME.
An exception is thrown by DBMS_ASSERT.ENQUOTE_NAME if the string provided contains a double quote in any position other than the first and last character:

ORA-44003: invalid SQL name

ENQUOTE_LITERAL

This routine will enclose a string literal inside single quotes, making it suitable for string literal parameters within SQL. In our example we would use it as:

l_sql_string := 'SELECT ' || DBMS_ASSERT.SIMPLE_SQL_NAME(p_column_name) ||
                ' FROM  ' || DBMS_ASSERT.QUALIFIED_SQL_NAME(p_table_owner || '.' || p_table_name) ||
                ' WHERE ' || p_column_name || ' LIKE ' || DBMS_ASSERT.ENQUOTE_LITERAL(p_starts_with || '%') ; 

If we were to attempt the SQL injection attack noted at the start of the post then we would get the following exception:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_ASSERT", line 409
ORA-06512: at "SYS.DBMS_ASSERT", line 493
ORA-06512: at line 16
ORA-06512: at line 36

which is what DBMS_ASSERT throws if the string contains a single quote, not doubled up with another single quote.

Summary

SQL injection is not a theoretical security problem, it’s a very real problem. In Oracle the best way to mitigate it is use static SQL statements. This may not always be possible though and so you will need to take steps to prevent dynamically generated SQL from being abused. Oracle provides you with DBMS_ASSERT for just this purpose. The routines contained in DBMS_ASSERT go a long way to protect the various parts of an SQL statement from being used for injection.

Lastly, please be aware that if you do something as silly as to allow applications to call a routine similar to:

PROCEDURE do_sql (p_sql_string)
AS
BEGIN
    EXECUTE IMMEDIATE p_sql_string;
END do_sql;

then there is very, very little that can do done for your security.

Advertisement