Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

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. There are currently no references to this package in the 10g Release 2 documentation or on Metalink. 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.

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 146

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 an 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 191

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 243

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 283

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 double quotes are present in adjacent pairs. If individual double 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 ""quotes"" are allowed') FROM dual;

SYS.DBMS_ASSERT.ENQUOTE_NAME('PAIRSOF""QUOTES""AREALLOWED')
----------------------------------------------------------------------------------------------------
"PAIRS OF ""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-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_ASSERT", line 308
ORA-06512: at "SYS.DBMS_ASSERT", line 343
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 308
ORA-06512: at "SYS.DBMS_ASSERT", line 358

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.