Oracle Native Dynamic SQL
Versions 8.1.5 - 19.3

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
Capture from above EXECUTE IMMEDIATE <SQL_statement_string>
 
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')

Related Topics
DBMS_ASSERT
DBMS_SQL
DBMS_SYS_SQL
EDITION BASED REDEFINITION
REF CURSORS