8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 10g » Here

DBMS_ASSERT - Sanitize User Input to Help Prevent SQL Injection

The DBMS_ASSERT package was introduced in Oracle 10g Release 2 and backported to Release 1 in the Oracle October 2005 Critical Patch Update. The package contains a number of functions that can be used to sanitize user input and help to guard against SQL injection in applications that don't use bind variables.

Related articles.

Introduction

SQL injection is a code injection technique that takes advantage of loose coding of database applications. You can read some introductory information about SQL injection here:

Code injection takes advantage of un-sanitised user entry. Concatenating user input to create SQL statements is evil! Imagine using the following statement.

sql = "SELECT * FROM users WHERE name ='" + userName + "';"

What if the user enters this the following value?

' or '1'='1

You end up with the following statement sent to the server, which will display all the users in the table.

SELECT * FROM users WHERE name = '' OR '1'='1';

If you are not using bind varibles, you must sanitise user entry!

Bobby Table
XKCD : Exploits of a Mom

There are several distinct types of SQL injection, including the following.

Why do we care about SQL injection?

“In a 2012 study, security company Imperva observed that the average web application received 4 attack campaigns per month, and retailers received twice as many attacks as other industries.”
Imperva Web Application Attack Report

"In our 17-month data set, SQL Injections now represent nearly two-thirds of all web application attacks."
Web Attacks and Gaming Abuse (June 2019)

The remainder of this article describes the DBMS_ASSERT package.

NOOP

The NOOP (No Operation) function performs no error checking and returns the input string as it was entered.

SQL> SELECT sys.DBMS_ASSERT.noop('Literal with no quotes.') FROM dual;

SYS.DBMS_ASSERT.NOOP('LITERALWITHNOQUOTES.')
----------------------------------------------------------------------------------------------------
Literal with no quotes.

1 row selected.

SQL> SELECT sys.DBMS_ASSERT.noop('Literal with ''single'' quotes.') FROM dual;

SYS.DBMS_ASSERT.NOOP('LITERALWITH''SINGLE''QUOTES.')
----------------------------------------------------------------------------------------------------
Literal with 'single' quotes.

1 row selected.

SQL> SELECT sys.DBMS_ASSERT.noop('Literal with ''''two single'''' quotes.') FROM dual;

SYS.DBMS_ASSERT.NOOP('LITERALWITH''''TWOSINGLE''''QUOTES.')
----------------------------------------------------------------------------------------------------
Literal with ''two single'' quotes.

1 row selected.

SQL> SELECT sys.DBMS_ASSERT.noop('Literal with "double" quotes.') FROM dual;

SYS.DBMS_ASSERT.NOOP('LITERALWITH"DOUBLE"QUOTES.')
----------------------------------------------------------------------------------------------------
Literal with "double" quotes.

1 row selected.

SQL>

SIMPLE_SQL_NAME

The SIMPLE_SQL_NAME function checks the input string conforms to the basic characteristics of a simple SQL name.

The "ORA-44003: invalid SQL name" exception is raised when the input string does not conform.

SQL> SELECT sys.DBMS_ASSERT.simple_sql_name('valid_name') FROM dual;

SYS.DBMS_ASSERT.SIMPLE_SQL_NAME('VALID_NAME')
----------------------------------------------------------------------------------------------------
valid_name

1 row selected.

SQL> SELECT sys.DBMS_ASSERT.simple_sql_name('"12 valid name"') FROM dual;

SYS.DBMS_ASSERT.SIMPLE_SQL_NAME('"VALIDNAME"')
----------------------------------------------------------------------------------------------------
"12 valid name"

1 row selected.

SQL> SELECT SYS.DBMS_ASSERT.simple_sql_name('1nvalid_name') FROM dual;
SELECT SYS.DBMS_ASSERT.simple_sql_name('1nvalid_name') FROM dual
       *
ERROR at line 1:
ORA-44003: invalid SQL name
ORA-06512: at "SYS.DBMS_ASSERT", line 215

SQL>

QUALIFIED_SQL_NAME

The QUALIFIED_SQL_NAME function checks the input string conforms to the basic characteristics of a qualified SQL name. A qualified name can be made up of several simple SQL names representing the names of the schema, object and database links. The supported syntax for qualified SQL names is displayed below.

<local qualified name> ::= <simple name> {'.' <simple name>}
<database link name> ::= <local qualified name> ['@' <connection string>]
<connection string> ::= <simple name>
<qualified name> ::= <local qualified name> ['@' <database link name>]

In most cases the following syntax is sufficient.

[SCHEMA-NAME.]OBJECT-NAME[@DBLINK-NAME]

The "ORA-44004: invalid qualified SQL name" exception is raised when the input string does not conform to the characteristics of a SQL name.

SQL> SELECT sys.DBMS_ASSERT.qualified_sql_name('object_name') FROM dual;

SYS.DBMS_ASSERT.QUALIFIED_SQL_NAME('OBJECT_NAME')
----------------------------------------------------------------------------------------------------
object_name

1 row selected.

SQL> SELECT sys.DBMS_ASSERT.qualified_sql_name('schema_name.object_name') FROM dual;

SYS.DBMS_ASSERT.QUALIFIED_SQL_NAME('SCHEMA_NAME.OBJECT_NAME')
----------------------------------------------------------------------------------------------------
schema_name.object_name

1 row selected.

SQL> SELECT sys.DBMS_ASSERT.qualified_sql_name('schema_name.object_name@dblink_name') FROM dual;

SYS.DBMS_ASSERT.QUALIFIED_SQL_NAME('SCHEMA_NAME.OBJECT_NAME@DBLINK_NAME')
----------------------------------------------------------------------------------------------------
schema_name.object_name@dblink_name

1 row selected.

SQL> SELECT sys.DBMS_ASSERT.qualified_sql_name('1nvalid_schema_name.object_name@dblink_name') FROM dual;
SELECT sys.DBMS_ASSERT.qualified_sql_name('1nvalid_schema_name.object_name@dblink_name') FROM dual
       *
ERROR at line 1:
ORA-44004: invalid qualified SQL name
ORA-06512: at "SYS.DBMS_ASSERT", line 320
ORA-06512: at "SYS.DBMS_ASSERT", line 260


SQL>

SCHEMA_NAME

The SCHEMA_NAME function checks that the input string represents an existing schema name. The function is case sensitive and accepts quoted schema names. The "ORA-44001: invalid schema" exception is raised when the input string does not match an existing schema name.

SQL> SELECT sys.DBMS_ASSERT.schema_name('SYS') FROM dual;

SYS.DBMS_ASSERT.SCHEMA_NAME('SYS')
----------------------------------------------------------------------------------------------------
SYS

1 row selected.

SQL> SELECT sys.DBMS_ASSERT.schema_name('sys') FROM dual;
SELECT sys.DBMS_ASSERT.schema_name('sys') FROM dual
       *
ERROR at line 1:
ORA-44001: invalid schema
ORA-06512: at "SYS.DBMS_ASSERT", line 355


SQL>

SQL_OBJECT_NAME

The SQL_OBJECT_NAME function checks that the input string represents an existing object. The function is not case sensitive, unless the input is quoted. If a database link is specified, only the syntax off the name is checked, not the existence of the object at the remote location. The "ORA-44002: invalid object name" exception is raised when the input string does not match an existing object name.

SQL> SELECT sys.DBMS_ASSERT.sql_object_name('dbms_assert') FROM dual;

SYS.DBMS_ASSERT.SQL_OBJECT_NAME('DBMS_ASSERT')
----------------------------------------------------------------------------------------------------
dbms_assert

1 row selected.

SQL> SELECT sys.DBMS_ASSERT.sql_object_name('sys.dbms_assert') FROM dual;

SYS.DBMS_ASSERT.SQL_OBJECT_NAME('SYS.DBMS_ASSERT')
----------------------------------------------------------------------------------------------------
sys.dbms_assert

1 row selected.

SQL> SELECT sys.DBMS_ASSERT.sql_object_name('sys.dbms_assert@dblink') FROM dual;

SYS.DBMS_ASSERT.SQL_OBJECT_NAME('SYS.DBMS_ASSERT@DBLINK')
----------------------------------------------------------------------------------------------------
sys.dbms_assert@dblink

1 row selected.

SQL> SELECT sys.DBMS_ASSERT.sql_object_name('"dbms_assert"') FROM dual;
SELECT sys.DBMS_ASSERT.sql_object_name('"dbms_assert"') FROM dual
       *
ERROR at line 1:
ORA-44002: invalid object name
ORA-06512: at "SYS.DBMS_ASSERT", line 417
ORA-06512: at "SYS.DBMS_ASSERT", line 412


SQL>

ENQUOTE_NAME

The ENQUOTE_NAME function encloses the input string within double quotes, unless they are already present. It also checks that all other quotes are present in adjacent pairs. If individual individual quotes are found, the "ORA-06502: PL/SQL: numeric or value error" exception is raised. By default, the output of non-quoted input strings is capitalized, but this functionality can be modified in PL/SQL by setting the capitalize parameter to FALSE.

SQL> SELECT sys.DBMS_ASSERT.enquote_name('quoted and uppercase') FROM dual;

SYS.DBMS_ASSERT.ENQUOTE_NAME('QUOTEDANDUPPERCASE')
----------------------------------------------------------------------------------------------------
"QUOTED AND UPPERCASE"

1 row selected.

SQL> SELECT sys.DBMS_ASSERT.enquote_name('"remains quoted and lowercase"') FROM dual;

SYS.DBMS_ASSERT.ENQUOTE_NAME('"REMAINSQUOTEDANDLOWERCASE"')
----------------------------------------------------------------------------------------------------
"remains quoted and lowercase"

1 row selected.

SQL> SELECT sys.DBMS_ASSERT.enquote_name('pairs of single ''quotes'' are allowed') FROM dual;

SYS.DBMS_ASSERT.ENQUOTE_NAME('PAIRSOFSINGLE''QUOTES''AREALLOWED')
--------------------------------------------------------------------------------
"PAIRS OF SINGLE 'QUOTES' ARE ALLOWED"

1 row selected.

SQL> SELECT sys.DBMS_ASSERT.enquote_name('individual "quotes" are not allowed') FROM dual;
SELECT sys.DBMS_ASSERT.enquote_name('individual "quotes" are not allowed') FROM dual
       *
ERROR at line 1:
ORA-44003: invalid SQL name
ORA-06512: at "SYS.DBMS_ASSERT", line 514
ORA-06512: at "SYS.DBMS_ASSERT", line 215
ORA-06512: at "SYS.DBMS_ASSERT", line 506
ORA-06512: at line 1


SQL> SELECT sys.DBMS_ASSERT.enquote_name('individual ""quotes"" are not allowed') FROM dual;
SELECT sys.DBMS_ASSERT.enquote_name('individual ""quotes"" are not allowed') FROM dual
       *
ERROR at line 1:
ORA-44003: invalid SQL name
ORA-06512: at "SYS.DBMS_ASSERT", line 514
ORA-06512: at "SYS.DBMS_ASSERT", line 215
ORA-06512: at "SYS.DBMS_ASSERT", line 506
ORA-06512: at line 1


SQL> SET SERVEROUTPUT ON
SQL> EXEC DBMS_OUTPUT.put_line(SYS.DBMS_ASSERT.enquote_name('quoted and remains lowercase', FALSE));
"quoted and remains lowercase"

PL/SQL procedure successfully completed.

SQL>

ENQUOTE_LITERAL

The ENQUOTE_LITERAL function encloses the input string within single quotes, and checks that all other single quotes are present in adjacent pairs. If individual single quotes are found, the "ORA-06502: PL/SQL: numeric or value error" exception is raised.

SQL> SELECT sys.DBMS_ASSERT.enquote_literal('literal without quotes') FROM dual;

SYS.DBMS_ASSERT.ENQUOTE_LITERAL('LITERALWITHOUTQUOTES')
----------------------------------------------------------------------------------------------------
'literal without quotes'

1 row selected.

SQL> SELECT sys.DBMS_ASSERT.enquote_literal('literal without ''''quotes') FROM dual;

SYS.DBMS_ASSERT.ENQUOTE_LITERAL('LITERALWITHOUT''''QUOTES')
----------------------------------------------------------------------------------------------------
'literal without ''quotes'

1 row selected.

SQL> SELECT sys.DBMS_ASSERT.enquote_literal('literal without ''quotes') FROM dual;
SELECT sys.DBMS_ASSERT.enquote_literal('literal without ''quotes') FROM dual
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_ASSERT", line 438
ORA-06512: at "SYS.DBMS_ASSERT", line 528


SQL>

Example 1

This example shows how SQL injection can be used to retrieve additional data when used against badly written dynamic SQL. Create the following schema objects.

CREATE TABLE open_tab (
  code        VARCHAR2(5),
  description VARCHAR2(50)
);

INSERT INTO open_tab VALUES ('ONE', 'Description for ONE');
INSERT INTO open_tab VALUES ('TWO', 'Description for TWO');
COMMIT;

CREATE TABLE secret_tab (
  code        VARCHAR2(5),
  description VARCHAR2(50)
);

INSERT INTO secret_tab VALUES ('CODE1', 'SECRET 1');
INSERT INTO secret_tab VALUES ('CODE2', 'SECRET 2');
COMMIT;

The following procedure uses unnecessary dynamic SQL to display a description for the supplied value.

CREATE OR REPLACE PROCEDURE get_open_data(p_code IN VARCHAR2) AS
  l_sql     VARCHAR2(32767);
  c_cursor  SYS_REFCURSOR;
  l_buffer  VARCHAR2(32767);
BEGIN
  l_sql := 'SELECT description FROM open_tab WHERE code = ''' || p_code || '''';

  OPEN c_cursor FOR l_sql;
  LOOP
    FETCH c_cursor
    INTO  l_buffer;
    EXIT WHEN c_cursor%NOTFOUND;

    DBMS_OUTPUT.put_line(l_buffer);
  END LOOP;
END;
/

When run as intended, it produces the expected output.

SQL> SET SERVEROUTPUT ON
SQL> EXEC get_open_data('ONE');
Description for ONE

PL/SQL procedure successfully completed.

SQL>

The following output shows how to SQL inject it to display all information from the table.

SQL> EXEC get_open_data('ONE'' OR ''1''=''1');
Description for ONE
Description for TWO

PL/SQL procedure successfully completed.

SQL>

In addition, we can use UNION to display data from other tables as well.

SQL> EXEC get_open_data('ONE'' UNION SELECT description FROM secret_tab WHERE ''1''=''1');
Description for ONE
SECRET 1
SECRET 2

PL/SQL procedure successfully completed.

SQL>

Using DBMS_ASSERT.ENQUOTE_LITERAL function, we can protect this procedure as follows.

CREATE OR REPLACE PROCEDURE get_open_data(p_code IN VARCHAR2) AS
  l_sql     VARCHAR2(32767);
  c_cursor  SYS_REFCURSOR;
  l_buffer  VARCHAR2(32767);
BEGIN
  l_sql := 'SELECT description FROM open_tab WHERE code = ' ||
            sys.DBMS_ASSERT.ENQUOTE_LITERAL(p_code);

  OPEN c_cursor FOR l_sql;
  LOOP
    FETCH c_cursor
    INTO  l_buffer;
    EXIT WHEN c_cursor%NOTFOUND;

    DBMS_OUTPUT.put_line(l_buffer);
  END LOOP;
END;
/

The following output shows the procedure now works as expected, but prevents these basic SQL injection attacks.

SQL> SET SERVEROUTPUT ON
SQL> EXEC get_open_data('ONE');
Description for ONE

PL/SQL procedure successfully completed.

SQL> EXEC get_open_data('ONE'' OR ''1''=''1');
BEGIN get_open_data('ONE'' OR ''1''=''1'); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_ASSERT", line 438
ORA-06512: at "SYS.DBMS_ASSERT", line 528
ORA-06512: at "TEST.GET_OPEN_DATA", line 6
ORA-06512: at line 1


SQL> EXEC get_open_data('ONE'' UNION SELECT description FROM secret_tab WHERE ''1''=''1');
BEGIN get_open_data('ONE'' UNION SELECT description FROM secret_tab WHERE ''1''=''1'); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_ASSERT", line 438
ORA-06512: at "SYS.DBMS_ASSERT", line 528
ORA-06512: at "TEST.GET_OPEN_DATA", line 6
ORA-06512: at line 1

SQL>

Example 2

This example shows how badly written dynamic SQL can be used to perform a type of DOS attack. Create the following procedure to count the number of rows in a specified table.

CREATE OR REPLACE PROCEDURE get_tab_row_count(p_table_name IN VARCHAR2) AS
  l_sql     VARCHAR2(32767);
  l_count   NUMBER;
BEGIN
  l_sql := 'SELECT COUNT(*) INTO :l_count FROM ' || p_table_name;

  EXECUTE IMMEDIATE l_sql INTO l_count;

  DBMS_OUTPUT.put_line('l_count = ' || l_count);
END;
/

When used as designed, it counts the number of rows in the table.

SQL> SET SERVEROUTPUT ON
SQL> EXEC get_tab_row_count('open_tab');
l_count = 2

PL/SQL procedure successfully completed.

SQL>

We can SQL inject it to create a cartesian product against another table.

SQL> EXEC get_tab_row_count('open_tab, all_objects');
l_count = 112632

PL/SQL procedure successfully completed.

SQL>

Imagine the impact if we had passed in a parameter like 'massive_table, massive_table, massive_table'. We could add significant load to the system, possibly causing a DOS attack.

The following function using the DBMS_ASSERT.SQL_OBJECT_NAME procedure to check the parameter value is a valid object name.

CREATE OR REPLACE PROCEDURE get_tab_row_count(p_table_name IN VARCHAR2) AS
  l_sql     VARCHAR2(32767);
  l_count   NUMBER;
BEGIN
  l_sql := 'SELECT COUNT(*) INTO :l_count FROM ' ||
            sys.DBMS_ASSERT.SQL_OBJECT_NAME(p_table_name);

  EXECUTE IMMEDIATE l_sql INTO l_count;

  DBMS_OUTPUT.put_line('l_count = ' || l_count);
END;
/

The following output shows the procedure is now protected from this type of attack.

SQL> EXEC get_tab_row_count('open_tab');
l_count = 2

PL/SQL procedure successfully completed.

SQL> EXEC get_tab_row_count('test.open_tab');
l_count = 2

PL/SQL procedure successfully completed.

SQL> EXEC get_tab_row_count('open_tab, all_objects');
BEGIN get_tab_row_count('open_tab, all_objects'); END;

*
ERROR at line 1:
ORA-44002: invalid object name
ORA-06512: at "SYS.DBMS_ASSERT", line 415
ORA-44004: invalid qualified SQL name
ORA-06512: at "SYS.DBMS_ASSERT", line 320
ORA-06512: at "SYS.DBMS_ASSERT", line 260
ORA-06512: at "SYS.DBMS_ASSERT", line 391
ORA-06512: at "TEST.GET_TAB_ROW_COUNT", line 5
ORA-06512: at line 1


SQL> EXEC get_tab_row_count('missing_tab');
BEGIN get_tab_row_count('missing_tab'); END;

*
ERROR at line 1:
ORA-44002: invalid object name
ORA-06512: at "SYS.DBMS_ASSERT", line 417
ORA-06512: at "SYS.DBMS_ASSERT", line 412
ORA-06512: at "TEST.GET_TAB_ROW_COUNT", line 5
ORA-06512: at line 1


SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.