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 DBMS_ASSERT.noop('Literal with no quotes.') FROM dual;
DBMS_ASSERT.NOOP('LITERALWITHNOQUOTES.')
----------------------------------------------------------------------------------------------------
Literal with no quotes.
1 row selected.
SQL> SELECT DBMS_ASSERT.noop('Literal with ''single'' quotes.') FROM dual;
DBMS_ASSERT.NOOP('LITERALWITH''SINGLE''QUOTES.')
----------------------------------------------------------------------------------------------------
Literal with 'single' quotes.
1 row selected.
SQL> SELECT DBMS_ASSERT.noop('Literal with ''''two single'''' quotes.') FROM dual;
DBMS_ASSERT.NOOP('LITERALWITH''''TWOSINGLE''''QUOTES.')
----------------------------------------------------------------------------------------------------
Literal with ''two single'' quotes.
1 row selected.
SQL> SELECT DBMS_ASSERT.noop('Literal with "double" quotes.') FROM dual;
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 first character of the name is alphabetic.
- The name only contains alphanumeric characters or the "_", "$", "#"
- Quoted names must be enclosed by double quotes and may contain any characters, including quotes provided they are represented by two quotes in a row ("").
- The function ignores leading and trailing white spaces are ignored
- The length of the input string is not validated.
The "ORA-44003: invalid SQL name" exception is raised when the input string does not conform.
SQL> SELECT DBMS_ASSERT.simple_sql_name('valid_name') FROM dual;
DBMS_ASSERT.SIMPLE_SQL_NAME('VALID_NAME')
----------------------------------------------------------------------------------------------------
valid_name
1 row selected.
SQL> SELECT DBMS_ASSERT.simple_sql_name('"12 valid name"') FROM dual;
DBMS_ASSERT.SIMPLE_SQL_NAME('"VALIDNAME"')
----------------------------------------------------------------------------------------------------
"12 valid name"
1 row selected.
SQL> SELECT DBMS_ASSERT.simple_sql_name('1nvalid_name') FROM dual;
SELECT 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 DBMS_ASSERT.qualified_sql_name('object_name') FROM dual;
DBMS_ASSERT.QUALIFIED_SQL_NAME('OBJECT_NAME')
----------------------------------------------------------------------------------------------------
object_name
1 row selected.
SQL> SELECT DBMS_ASSERT.qualified_sql_name('schema_name.object_name') FROM dual;
DBMS_ASSERT.QUALIFIED_SQL_NAME('SCHEMA_NAME.OBJECT_NAME')
----------------------------------------------------------------------------------------------------
schema_name.object_name
1 row selected.
SQL> SELECT DBMS_ASSERT.qualified_sql_name('schema_name.object_name@dblink_name') FROM dual;
DBMS_ASSERT.QUALIFIED_SQL_NAME('SCHEMA_NAME.OBJECT_NAME@DBLINK_NAME')
----------------------------------------------------------------------------------------------------
schema_name.object_name@dblink_name
1 row selected.
SQL> SELECT DBMS_ASSERT.qualified_sql_name('1nvalid_schema_name.object_name@dblink_name') FROM dual;
SELECT 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 DBMS_ASSERT.schema_name('SYS') FROM dual;
DBMS_ASSERT.SCHEMA_NAME('SYS')
----------------------------------------------------------------------------------------------------
SYS
1 row selected.
SQL> SELECT DBMS_ASSERT.schema_name('sys') FROM dual;
SELECT 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 DBMS_ASSERT.sql_object_name('dbms_assert') FROM dual;
DBMS_ASSERT.SQL_OBJECT_NAME('DBMS_ASSERT')
----------------------------------------------------------------------------------------------------
dbms_assert
1 row selected.
SQL> SELECT DBMS_ASSERT.sql_object_name('sys.dbms_assert') FROM dual;
DBMS_ASSERT.SQL_OBJECT_NAME('SYS.DBMS_ASSERT')
----------------------------------------------------------------------------------------------------
sys.dbms_assert
1 row selected.
SQL> SELECT DBMS_ASSERT.sql_object_name('sys.dbms_assert@dblink') FROM dual;
DBMS_ASSERT.SQL_OBJECT_NAME('SYS.DBMS_ASSERT@DBLINK')
----------------------------------------------------------------------------------------------------
sys.dbms_assert@dblink
1 row selected.
SQL> SELECT DBMS_ASSERT.sql_object_name('"dbms_assert"') FROM dual;
SELECT 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 DBMS_ASSERT.enquote_name('quoted and uppercase') FROM dual;
DBMS_ASSERT.ENQUOTE_NAME('QUOTEDANDUPPERCASE')
----------------------------------------------------------------------------------------------------
"QUOTED AND UPPERCASE"
1 row selected.
SQL> SELECT DBMS_ASSERT.enquote_name('"remains quoted and lowercase"') FROM dual;
DBMS_ASSERT.ENQUOTE_NAME('"REMAINSQUOTEDANDLOWERCASE"')
----------------------------------------------------------------------------------------------------
"remains quoted and lowercase"
1 row selected.
SQL> SELECT DBMS_ASSERT.enquote_name('pairs of ""quotes"" are allowed') FROM dual;
DBMS_ASSERT.ENQUOTE_NAME('PAIRSOF""QUOTES""AREALLOWED')
----------------------------------------------------------------------------------------------------
"PAIRS OF ""QUOTES"" ARE ALLOWED"
1 row selected.
SQL> SELECT DBMS_ASSERT.enquote_name('individual "quotes" are not allowed') FROM dual;
SELECT 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(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 DBMS_ASSERT.enquote_literal('literal without quotes') FROM dual;
DBMS_ASSERT.ENQUOTE_LITERAL('LITERALWITHOUTQUOTES')
----------------------------------------------------------------------------------------------------
'literal without quotes'
1 row selected.
SQL> SELECT DBMS_ASSERT.enquote_literal('literal without ''''quotes') FROM dual;
DBMS_ASSERT.ENQUOTE_LITERAL('LITERALWITHOUT''''QUOTES')
----------------------------------------------------------------------------------------------------
'literal without ''quotes'
1 row selected.
SQL> SELECT DBMS_ASSERT.enquote_literal('literal without ''quotes') FROM dual;
SELECT 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...
 |