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.
Mark Hoxey 