| 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; |
| |
| SIMPLE_SQL_NAME_LIST |
| Verifies that the input string is a comma delimited list of simple SQL names |
dbms_assert.simple_sql_name_list(str IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET Str%CHARSET; |
SELECT dbms_assert.simple_sql_name_list('UWCLASS,SCOTT,HR,SH')
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; |