Using DBMS_ASSERT

If the condition which determines the property asserted in a function is not met then a value error is raised. Otherwise the input value is returned via return value. Most functions return the value unchanged, however, several functions modify the value.

ENQUOTE_LITERAL Function
This function enquotes a string literal.
Syntax
DBMS_ASSERT.ENQUOTE_LITERAL (
str VARCHAR2)
RETURN VARCHAR2;
Usage Notes
Add leading and trailing single quotes to a string literal.
Verify that all single quotes except leading and trailing characters are paired with adjacent single quotes.

ENQUOTE_NAME Function
This function encloses a name in double quotes.
Syntax
DBMS_ASSERT.ENQUOTE_NAME (
str VARCHAR2,
capitalize BOOLEAN DEFAULT TRUE)
RETURN VARCHAR2;
If TRUE or defaulted, alphabetic characters of str which was not in quotes are translated to upper case

NOOP Functions
This function returns the value without any checking.
Syntax
DBMS_ASSERT.NOOP (
str VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET str%CHARSET;
DBMS_ASSERT.NOOP (
str CLOB CHARACTER SET ANY_CS)
RETURN CLOB CHARACTER SET str%CHARSET;

QUALIFIED_SQL_NAME Function
This function verifies that the input string is a qualified SQL name.
Syntax
DBMS_ASSERT.QUALIFIED_SQL_NAME (
str VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET str%CHARSET;Exceptions
ORA44004: string is not a qualified SQL name

SCHEMA_NAME Function
This function verifies that the input string is an existing schema name.
Syntax
DBMS_ASSERT.SCHEMA_NAME (
str VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET str%CHARSET;
Exceptions
ORA44001: Invalid schema name
Usage Notes
By definition, a schema name need not be just a simple SQL name. For example, "FIRST LAST" is a valid schema name. As a consequence, care must be taken to quote the output of schema name before concatenating it with SQL text.

SIMPLE_SQL_NAME Function
This function verifies that the input string is a simple SQL name.
Syntax
DBMS_ASSERT.SIMPLE_SQL_NAME (
str VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET str%CHARSET;
Exceptions
ORA44003: string is not a simple SQL name
Usage Notes
The input value must be meet the following conditions:
The name must begin with an alphabetic character. It may contain alphanumeric characters as well as the characters _, $, and # in the second and subsequent character positions.
Quoted SQL names are also allowed.
Quoted names must be enclosed in double quotes.
Quoted names allow any characters between the quotes.
Quotes inside the name are represented by two quote characters in a row, for example, "a name with "" inside" is a valid quoted name.
The input parameter may have any number of leading and/or trailing white space characters.
The length of the name is not checked.

SQL_OBJECT_NAME Function
This function verifies that the input parameter string is a qualified SQL identifier of an existing SQL object.
Syntax
DBMS_ASSERT.SQL_OBJECT_NAME (
str VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET str%CHARSET;
Exceptions

ORA44002: Invalid object name

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

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.

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.

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_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.

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.

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.

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 342
ORA-06512: at "SYS.DBMS_ASSERT", line 411
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 342
ORA-06512: at "SYS.DBMS_ASSERT", line 411
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 316
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 316
ORA-06512: at "TEST.GET_TAB_ROW_COUNT", line 5
ORA-06512: at line 1


SQL>