| Security Advisory |
To gain a full understanding of NDS it is critical that you read the docs, review the links at the bottom of the page: DBMS_ASSERT, DBMS_SQL, DBMS_SYS_SQL, and REF CURSORS. But, most importantly, that you write some.
Because it is only through writing dynamic SQL, and learning how to inject malicious code into it, that you can become competent at recognizing malicious code and preventing its execution.
In the mid 2000's I taught a class about NDS where I showed how essentially any statement could be made to work ... one student later proudly showed me the following:
BEGIN
EXECUTE IMMEDIATE COMMIT;
END;
/
There are a number security concerns with respect to dynamic SQL
- By definition, dynamic SQL is capable of executing statements that cannot be anticipated or tested in advance
- Statements that accept parameters originating in applications are the prime target for SQL Injection attacks
- Very few developers and DBAs are familiar with how to use DBMS_ASSERT to prevent SQL Injection
- Very few developers and DBAs have extensive experience using Bind Variables
- Object dependencies when creating objects with dynamic SQL will likely not be visible in the DBA_DEPENDENCIES view
|
| |
| Recommended Security Rules |
NEVER
- use any form of dynamic SQL without using the DBMS_ASSERT package to sanitize the inputs.
WITH GREAT CARE
- Test any code using dynamic SQL to assure that a SQL Injection attack is not possible.
CAUTIONS
- In addition to the use of DBMS_ASSERT use Bind Variables whenever possible.
- Do not rely on the expertise of anyone that does not know how to craft and deploy a SQL Injection attack to validate the code.
|
| |
| How Oracle Works |
Most SQL statements are inflexible.
If in one case you want to select code from the first three columns in a table based on the customer_id and in another case based on the customer's tax identification number ... you must write and test two entirely separate statements.
The good news about writing the extra code is that it will likely be more secure and execute a millisecond faster. The bad news is that if there is a change order, or a bug, you have to find, change, and retest them all.
With dynamic SQL, incurring a very small performance penalty in exchange for the flexibility, there is the ability to craft a statement dynamically based entirely on inputs and algorithms.
At the bottom of this page review the code in "Demonstration 5: Dynamic SQL Statement Creation" as it shows the incredible power dynamic SQL brings to Oracle.
The demonstration constructs the SELECT, FROM, WHERE, and ORDER BY clauses dynamically then executes the resulting statement.
A more complex version of this code, with lots of security and exception handling added, was written to load data in a website from a public utility in a major US city. |
Doing SQL in PL/SQL with EXECUTE IMMEDIATE
You cannot perform DDL in a PL/SQL block, for example truncating or creating a table except through the use of Native Dynamic SQL
In the first example the TRUNCATE TABLE statement is a single hard coded string. The name of the table can not be altered at run time by passing in a parameter. The lack of flexibility makes it less useful but incapable of being misused in a SQL Injection attack.
In the second example the TRUNCATE TABLE statement is created by concatenating a fixed string with a variable that could be a parameter passed in from an application. This usage is far more flexible but introduces the potential for SQL Injection. |
conn uwclass/uwclass@pdbdev
CREATE TABLE test (
testcol VARCHAR2(20));
Table created.
SQL> desc test
Name Null? Type
----------------------------- -------- --------------------
TESTCOL VARCHAR2(20)
INSERT INTO test VALUES ('ABC');
1 row created.
INSERT INTO test VALUES ('DEF');
1 row created.
INSERT INTO test VALUES ('xyz');
1 row created.
COMMIT;
Commit complete.
SELECT COUNT(*) FROM test;
COUNT(*)
---------
3
BEGIN
TRUNCATE TABLE test;
END;
/
TRUNCATE TABLE test;
ERROR at line 2:
ORA-06550: line 2, column 12:
PLS-00103: Encountered the symbol "TABLE" when expecting
one of the following:
:= . ( @ % ;
The symbol ":= was inserted before "TABLE" to continue.
This is a horrible error message. Why doesn't it just say DDL can not be performed in PL/SQL except with dynamic SQL.
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE test';
END;
/
PL/SQL procedure successfully completed.
SELECT COUNT(*) FROM test;
COUNT(*)
---------
0
INSERT INTO test VALUES ('GHI');
1 row created.
INSERT INTO test VALUES ('JKL');
1 row created.
INSERT INTO test VALUES ('uvw');
1 row created.
COMMIT;
Commit complete.
SELECT COUNT(*) FROM test;
COUNT(*)
---------
3
DECLARE
x user_tables.table_name%TYPE := 'TEST';
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x;
END;
/
PL/SQL procedure successfully completed.
SELECT COUNT(*) FROM test;
COUNT(*)
---------
0 |
Lack of dependency metadata
Note with care, both procedures are dependent upon and access DBA_ALL_TABLES. Yet in the dynamic_test procedure, because the object is contained within a string, it is not recognized as a dependency.
One other point we would like to make in association with this demo. Never, ever, query DBA_TABLES if you want to know the names of the tables that exist. There are a lot of types of tables you will never find in DBA_TABLES.
And, for the reason, if you want a list of all of the objects in your database do not query DBA_OBJECTS. We could easily create thousands of objects in your database that would be invisible in DBA_OBJECTS ... we we mean objects like packages, procedures, and triggers.
Follow the link below on Edition Based Redefinition to learn the details. |
CREATE OR REPLACE PROCEDURE static_test AUTHID DEFINER IS
i INTEGER;
BEGIN
SELECT COUNT(*)
INTO i
FROM dba_all_tables;
dbms_output.put_line(i);
END static_test;
/
CREATE OR REPLACE PROCEDURE dynamic_test AUTHID DEFINER IS
execStr VARCHAR2(256) := 'SELECT COUNT(*) FROM
dba_all_tables';
i INTEGER;
BEGIN
EXECUTE IMMEDIATE execStr
INTO i;
dbms_output.put_line(i);
END dynamic_test;
/
SQL> exec static_test
2183
PL/SQL procedure successfully completed.
SQL> exec dynamic_test
2183
PL/SQL procedure successfully completed.
col referenced_owner format a16
col referenced_type format a15
SELECT name, referenced_owner, referenced_name, referenced_type
FROM dba_dependencies
WHERE name LIKE '%IC_TEST'
ORDER BY 1;
NAME REFERENCED_OWNER REFERENCED_NAME REFERENCED_ TYPE
------------- ---------------- ---------------------------- ----------------
DYNAMIC_TEST SYS DBMS_OUTPUT PACKAGE
DYNAMIC_TEST SYS STANDARD PACKAGE
DYNAMIC_TEST SYS SYS_STUB_FOR_PURITY_ANALYSIS PACKAGE
STATIC_TEST SYS DBA_ALL_TABLES VIEW
STATIC_TEST SYS DBMS_OUTPUT PACKAGE
STATIC_TEST SYS STANDARD PACKAGE
STATIC_TEST SYS SYS_STUB_FOR_PURITY_ANALYSIS PACKAGE |
| |
| Syntax and Usage |
| Basic Dynamic SQL |
EXECUTE IMMEDIATE <SQL_statement_string> |
EXECUTE IMMEDIATE
'GRANT dba TO scott'; |
| Dynamic SQL with Bind Variables and USING Clause |
EXECUTE IMMEDIATE <sql_statement_string_with_bind_variable>
USING <substitution_value>; |
conn uwclass/uwclass@pdbdev
CREATE TABLE t (
mycol NUMBER(5));
BEGIN
FOR i IN 1 .. 10000
LOOP
EXECUTE IMMEDIATE 'INSERT INTO t VALUES (:x)'
USING i;
END LOOP;
END;
/
SELECT COUNT(*) FROM t;
SELECT * FROM t; |
| Dynamic SQL with INTO Clause |
EXECUTE IMMEDIATE <sql_statement_string_with_bind_variable>
USING OUT <parameter>; |
conn uwclass/uwclass@pdbdev
DECLARE
sqlstr VARCHAR2(256);
i PLS_INTEGER;
BEGIN
sqlstr := 'SELECT COUNT(*) FROM user_objects';
EXECUTE IMMEDIATE sqlstr
INTO i;
dbms_output.put_line(i);
END;
/ |
| |
| |
| Demonstration 1: Create Table |
| Create Table |
conn uwclass/uwclass@pdbdev
DECLARE
x VARCHAR2(200);
BEGIN
x := 'CREATE TABLE xyz (col1 NUMBER(10), col2 VARCHAR2(20), col3 DATE)';
EXECUTE IMMEDIATE x;
END;
/
desc xyz |
| |
| Demonstration 2: Create or Alter Table |
| Creates a table if it does not exist or alters it, adding a new column if it does. |
conn uwclass/uwclass@pdbdev
DECLARE
i PLS_INTEGER;
x VARCHAR2(200);
BEGIN
SELECT COUNT(*)
INTO i
FROM all_all_tables
WHERE table_name = 'XYZ';
IF i = 0 THEN
x := 'CREATE TABLE xyz(col1 NUMBER(10), col2 VARCHAR2(20), col3 DATE)';
EXECUTE IMMEDIATE x;
ELSE
x := 'ALTER TABLE xyz ADD(new_col VARCHAR2(100))';
EXECUTE IMMEDIATE x;
END IF;
END;
/
SELECT COUNT(*)
FROM all_all_tables
WHERE table_name = 'XYZ';
desc xyz |
| |
| Demonstration 3: Bind Variables and Using Clause |
| More working example code combining use of Bind Variables and a Using Clause |
conn uwclass/uwclass@pdbdev
CREATE TABLE dept_new (
department_no VARCHAR2(10),
department_name VARCHAR2(30),
location VARCHAR2(30));
CREATE OR REPLACE PROCEDURE nds_demo (
deptnum dept_new.department_no%TYPE,
deptname dept_new.department_name%TYPE,
location dept_new.location%TYPE) AUTHID CURRENT_USER IS
stmt_str VARCHAR2(100);
BEGIN
stmt_str := 'INSERT INTO dept_new
VALUES(:deptno, :dname, :loc)';
EXECUTE IMMEDIATE stmt_str USING deptnum, deptname, location;
END nds_demo;
/
desc nds_demo
exec nds_demo('100', 'Accounting', 'Los Angeles, CA')
SELECT * FROM dept_new; |
| |
| Demonstration 4: Dynamic SQL with a RETURNING Clause |
This was, without a doubt, the most difficult dynamic SQL statement I have ever constructed for two reasons.
1. The syntax as near as I can tell was never published before by either Oracle or anyone else.
2. To make it work appears to require two separate RETURNING statements.
I did it as an academic exercise and have so far never found a reason to use it in an application but, if I find that opportunity, I won't be learning how on the customer's dime. And, since it is posted here, you won't need to reinvent the wheel. |
conn uwclass/uwclass@pdbdev
CREATE TABLE dept_ret (
deptno NUMBER(2),
dname VARCHAR2(30),
location VARCHAR2(30));
CREATE SEQUENCE seq;
DECLARE
sql_stmt VARCHAR2(128);
dno dept_ret.deptno%TYPE;
BEGIN
sql_stmt := 'INSERT INTO dept_ret (deptno, dname, location) ' ||
'VALUES (seq.NEXTVAL+1, ''PERSONNEL'', ''SEATTLE'') ' ||
'RETURNING deptno INTO :retval';
EXECUTE IMMEDIATE sql_stmt RETURNING INTO dno;
dbms_output.put_line(TO_CHAR(dno));
END;
/
SELECT * FROM dept_ret; |
| |
| Demonstration 5: Dynamic SQL Statement Creation |
conn uwclass/uwclass@pdbdev
CREATE OR REPLACE PROCEDURE revmp.load_recs(nav_lvl VARCHAR2, esrloc VARCHAR2, sectloc NUMBER, ocaval VARCHAR2) AUTH_ID DEFINER IS
IClause VARCHAR2(200) := 'INSERT INTO find_recs (servord_no, rec_name, rec_street, rec_city, rec_zip, assigned_to_esr)';
SClause VARCHAR2(200) := ' SELECT DISTINCT s.servord_no, p.package_name, ';
FClause VARCHAR2(200) := ' FROM servord_package p, service_order s, feed f';
WClause VARCHAR2(300) := ' WHERE p.package_no = s.package_no AND s.servord_no = f.servord_no';
OClause VARCHAR2(100) := ' ORDER BY TO_NUMBER(servord_no)';
XString VARCHAR2(1000);
Xval PLS_INTEGER := 1;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE find_recs';
IF nav_lvl = 'S' THEN
SClause := SClause || 'p.package_street, p.package_city, p.package_zip_code,
s.assigned_to_esr';
WClause := WClause || '(+)';
ELSIF nav_lvl = 'F' THEN
SClause := SClause || 'f.feed_to_address, f.feed_to_city, ' ||
'f.feed_to_zip_code, s.assigned_to_esr';
ELSIF nav_lvl = 'U' THEN
SClause := SClause || 'a.service_address, a.service_city, a.service_zip_code,
s.assigned_to_esr';
FClause := FClause || ', service_address a';
WClause := WClause || ' AND s.servord_no = a.servord_no';
END IF;
IF esrloc IS NOT NULL THEN
Xval := Xval + 10;
WClause := WClause || ' AND s.esr_location = :E';
END IF;
IF sectloc IS NOT NULL THEN
Xval := Xval + 100;
WClause := WClause || ' AND f.geocode_section = :S';
END IF;
IF ocaval = 'O' THEN
Xval := Xval + 1000;
WClause := WClause || ' AND servord_compl_date IS NULL';
ELSIF ocaval = 'C' THEN
Xval := Xval + 1000;
WClause := WClause || ' AND servord_compl_date IS NOT NULL';
END IF;
Xstring := IClause || SClause || FClause || WClause || OClause;
IF Xval = 1 THEN
EXECUTE IMMEDIATE Xstring;
ELSIF Xval = 11 THEN
EXECUTE IMMEDIATE Xstring USING esrloc;
ELSIF Xval = 101 THEN
EXECUTE IMMEDIATE Xstring USING sectloc;
ELSIF Xval = 111 THEN
EXECUTE IMMEDIATE Xstring USING esrloc, sectloc;
ELSIF Xval = 1001 THEN
EXECUTE IMMEDIATE Xstring;
ELSIF Xval = 1011 THEN
EXECUTE IMMEDIATE Xstring USING esrloc;
ELSIF Xval = 1101 THEN
EXECUTE IMMEDIATE Xstring USING sectloc;
ELSIF Xval = 1111 THEN
EXECUTE IMMEDIATE Xstring USING esrloc, sectloc;
END IF;
COMMIT;
END;
/ |
| |
| Demonstration 6: Using Dynamic SQL To Run An Anonymous Block Executing A Procedure |
conn uwclass/uwclass@pdbdev
CREATE OR REPLACE PROCEDURE run_me(inval VARCHAR2) AUTHIC DEFINER IS
BEGIN
dbms_output.put_line(inval);
END run_me;
/
CREATE OR REPLACE PROCEDURE process_line(procno VARCHAR2) AUTHID DEFINER IS
BEGIN
EXECUTE IMMEDIATE 'BEGIN run_me(:b); END;'
USING procno;
END process_line;
/
desc run_me
desc process_line
set serveroutput on
exec process_line('It Works') |