Oracle DBMS_ASSERT Built-In Package
Versions 10.2 - 19.3

Security Advisory
More than 25% of all data and database attacks involve the use, at some point, of SQL*Injection to escalate privileges. This technique is so efficient that even though only about a quart of all attacks utilize it, it is responsible, in part, for almost 90% of the data accessed or stolen.

In less sophisticated databases the only way to prevent SQL Injection is through perfect application coding. In Oracle Databases, however, Oracle Corp. makes available to Developers and DBAs a tool they developed for internal purposes that can be used to make these attacks impossible. It is the DBMS_ASSERT package.

The problem with DBMS_ASSERT is the problem with much of Oracle's best technology ... it is free, already in the database, and fully documented. Which hardly sounds like a problem except that it is. When something is free and already distributed to customers account managers don't know it exists, presales engineers don't talk about it, and even frequent conference speakers such as ACE Director Alum Daniel Morgan don't put together presentations about it. This web page is your opportunity to learn about this package and includes detailed demos showing how it works and how it can be easily implemented.
 
Recommended Security Rules

 NEVER
  • allow any SQL or PL/SQL statement utilizing dynamic SQL without sanitizing its inputs with DBMS_ASSERT
 WITH GREAT CARE
  • Implement third party products without reviewing their source code or having the vendor/OEM identify any use of dynamic SQL and how they have mitigated risk against SQL Injection attack
 CAUTIONS
  • Any statement that contains "DBMS_SQL" is dynamic SQL
  • Any statement that contains "EXECUTE IMMEDIATE" is dynamic SQL
  • Any statement that contains a SQL statement or the keyword "BEGIN" within parentheses
 
How Oracle Works
Dynamically resolves code enclosed in parentheses Everyone with more than a few days of exposure to SQL can write and understand this following statement

SQL> SELECT table_name
  2  FROM user_tables
  3  WHERE rownum = 1;

TABLE_NAME
------------------------------
TS$

this is what we think SQL is supposed to look like. Now consider the following

SQL> SELECT table_name
  2  FROM user_tables
  3  WHERE rownum = (SELECT 3-2 FROM dual);

TABLE_NAME
------------------------------
ICOL$

Oracle could not execute the statement until it first resolved the value on the right-side of the equals sign.
But why stop there?


SELECT (SELECT 'DB' FROM DUAL) || (SELECT 'Sec' FROM DUAL) || (SELECT 'Wor' FROM dual) || 'x' AS RESULT
FROM (SELECT 'DUAL' FROM dual)
WHERE (SELECT 1 FROM dual) = (SELECT 1 FROM dual)
AND (SELECT 2 FROM dual) BETWEEN (SELECT 1 FROM dual) AND (SELECT 3 FROM dual)
AND NVL((SELECT NULL FROM dual), (SELECT 'z' FROM dual)) = (SELECT 'z' FROM dual)
ORDER BY (SELECT 1 FROM dual);

RESULT
---------
DBSecWorx

There is no clause of a "simple" SELECT statement that cannot be made dynamic ... and some of the executing code could be malicious if you do not use protection. It is this type of fully documented, yet unexpected, behavior that creates a vulnerability. If you didn't know this could happen ... how could you prevent it?
The OR Condition Conditions such as AND, OR, NOT, LIKE, BETWEEN, and EXISTS are common elements of most programming languages: But do we really understand what they mean? In this section we will look at how OR can be used to aid a SQL Injection attack. The following demo is very simplified to demonstrate how it might work. The fact that your application code is not this simplistic means it is not a website demo ... it does not mean you are secure.

First we will create a table to hold authentication credentials.


CREATE TABLE auth_user(
usr_id VARCHAR2(30),
pwd_id VARCHAR2(30));

INSERT INTO auth_user (usr_id, pwd_id) VALUES ('Morgan', 'MySecret');
COMMIT;

SELECT * FROM auth_user;

USR_ID  PWD_ID
------- ---------
Morgan  MySecret

Next we create a simple PL/SQL program to accept a string that might be passed in from a Dot Net or JDBC application, execute the pass-thru SQL as dynamic SQL with EXECUTE IMMEDIATE and return a value if it succeeds (1) and a zero (0) if it fails.

CREATE OR REPLACE PROCEDURE validate_pwd(app_gen_string VARCHAR2) AUTHID CURRENT_USER IS
 x INTEGER;
BEGIN
  EXECUTE IMMEDIATE app_gen_string INTO x;
  dbms_output.put_line(x);
END validate_pwd;
/

And, for demo purposes, we will use an autonomous block with a single line of code to emulate the application.

In this first example the password "MySecret" is passed in and the program returns a 1 indicating it is valid.


BEGIN
  validate_pwd('SELECT COUNT(*) FROM auth_user WHERE pwd_id = ''MySecret''');
END;
/
1

In this second example the password we pass "No Clue" which is invalid and the program returns a 0.

BEGIN
  validate_pwd('SELECT COUNT(*) FROM auth_user WHERE pwd_id = ''No Clue''');
END;
/
0

In this third example the password we pass "No Clue" and inject "1 = 1" with the OR operator.

BEGIN
  validate_pwd('SELECT COUNT(*) FROM auth_user WHERE pwd_id = ''No Clue'' OR ''1'' = ''1''');
END;
/
1

As the third example demonstrates, while "No Clue" is clearly not a valid password, 1 always equals, the WHERE clause returns TRUE, and the query returns a count of rows where 1=1 ... which is all of them.

There are numerous ways a vulnerability such as this can creep into application code. The approach taken by an attacker is to either find a known application with a known vulnerability, to review source code which is often amazingly easy to acquire, or to brute-force discover a weakness through probing APIs.
 
DBMS_ASSERT Package Information
AUTHID CURRENT_USER
Dependencies SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_ASSERT'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_ASSERT';


Returns 440 objects in version 18.3
Documented Yes
Exceptions
Error Code Reason
ORA-44001 INVALID_SCHEMA_NAME
ORA-44002 INVALID_OBJECT_NAME
ORA-44003 INVALID_SQL_NAME
ORA-44004 INVALID_QUALIFIED_SQL_NAME
First Available 10.2.0.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC, AUDSYS, DBSFWUSER. and DVSYS
Source {ORACLE_HOME}/rdbms/admin/dbmsasrt.sql
Subprograms
 
ENQUOTE_LITERAL
Enquote a string literal. Add leading and trailing single quotes to a string literal dbms_assert.enquote_literal(str IN VARCHAR2) RETURN VARCHAR2;
set serveroutput on

DECLARE
 table_name user_tables.table_name%TYPE := 'SERVERS';
BEGIN
  dbms_output.put_line(table_name);
  table_name := dbms_assert.enquote_literal(table_name);
  dbms_output.put_line(table_name);
END;
/
 
ENQUOTE_NAME
This function encloses a name in double quotes dbms_assert.enquote_name(
str        IN VARCHAR2,
capitalize IN BOOLEAN DEFAULT TRUE)
RETURN VARCHAR2;
set serveroutput on

DECLARE
 table_name user_tables.table_name%TYPE := 'SERVERS';
BEGIN
dbms_output.put_line(table_name);
  table_name := dbms_assert.enquote_name(table_name, FALSE);
  dbms_output.put_line(table_name);
END;
/
 
NOOP
Returns the value without any checking. This should be used only for proof-of-concept where the use of other DBMS_ASSERT functionality is being considered.

Overload 1
dbms_assert.noop(str IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET str%CHARSET;
SELECT dbms_assert.noop('SERVERS')
FROM dual;
Overload 2 dbms_assert.noop(str IN CLOB CHARACTER SET ANY_CS)
RETURN CLOB CHARACTER SET str%CHARSET;
DECLARE
 cin  CLOB := 'SERVERS';
 cout CLOB;
BEGIN
  cout := dbms_assert.noop(cin);
  dbms_output.put_line(cout);
END;
/
 
QUALIFIED_SQL_NAME
Verify that the input string is a qualified SQL name dbms_assert.qualified_sql_name(str IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET str%CHARSET;
set serveroutput on

DECLARE
 table_name user_tables.table_name%TYPE := 'SERVERS';
BEGIN
dbms_output.put_line(table_name);
  table_name := dbms_assert.qualified_sql_name(table_name);
  dbms_output.put_line(table_name);
END;
/

DECLARE
 table_name user_tables.table_name%TYPE := 'BEGIN GRANT DBA TO myuser END;';
BEGIN
  dbms_output.put_line(table_name);
  table_name := dbms_assert.qualified_sql_name(table_name);
  dbms_output.put_line(table_name);
END;
/
 
SCHEMA_NAME
Verifies that the input string is an existing schema name dbms_assert.schema_name(str IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET str%CHARSET;
SELECT dbms_assert.schema_name('UWCLASS')
FROM dual;

SELECT dbms_assert.schema_name('UWCLASZ')
FROM dual;

CREATE TABLE user_pwd (
username VARCHAR2(30),
password VARCHAR2(30));

INSERT INTO user_pwd VALUES ('UWCLASS', 'UWCLASS');
INSERT INTO user_pwd VALUES ('MORGAN', 'AceDir');

CREATE OR REPLACE PROCEDURE ckpwd (usr IN VARCHAR2, pwd IN VARCHAR2) IS
 v_query  VARCHAR2(100);
 v_output PLS_INTEGER;
BEGIN
  v_query := q'{SELECT COUNT(*) FROM user_pwd}' || ' ' ||
  q'{WHERE username = '}' || dbms_assert.schema_name(usr) ||
  q'{' AND password = '}' || pwd || q'{'}';

  dbms_output.put_line(CHR(10) || 'Built the following statement: ' || CHR(10) || v_query);

  EXECUTE IMMEDIATE v_query INTO v_output;

  dbms_output.put_line(CHR(10) || usr || ' is authenticated');
  dbms_output.put_line(TO_CHAR(v_output));
EXCEPTION
  WHEN dbms_assert.invalid_schema_name THEN
    dbms_output.put_line(CHR(10) || ' access denied');
END ckpwd;
/

set serveroutput on

exec ckpwd('UWCLASS', 'UWCLASS');
exec ckpwd('MORGAN', 'ACEDIR');
 
SIMPLE_SQL_NAME
Verifies that the input string is a simple SQL name dbms_assert.simple_sql_name(str IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET str%CHARSET;
SELECT dbms_assert.simple_sql_name('SERVERS1')
FROM dual;

SELECT dbms_assert.simple_sql_name('1SERVERS')
FROM dual;
 
SQL_OBJECT_NAME
This function verifies that the input parameter string is a qualified SQL identifier of an existing SQL object dbms_assert.sql_object_name(str IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET str%CHARSET;
SELECT dbms_assert.sql_object_name('UWCLASS.SERVERS')
FROM dual;

SELECT dbms_assert.sql_object_name('UWCLASS.SERVERZ')
FROM dual;
 
Related Queries
Find dynamic SQL in unwrapped database source code.

In version 18.3 this query returns more than 1,500 rows
conn / as sysdba

SELECT owner, name, type, line, text, 'DBMS_SQL used' AS REASON
FROM dba_source
WHERE UPPER(text) LIKE '%DBMS_SQL%'
UNION ALL
SELECT owner, name, type, line, text, 'Native Dynamic used'
FROM dba_source
WHERE UPPER(text) LIKE '%EXECUTE IMMEDIATE%'
ORDER BY 1,2,3;

Related Topics
DBMS_SQL
Native Dynamic SQL
Object Privileges
OWM_ASSERT_PKG
System Privileges