Native Dynamic SQL, DBMS_SQL, Bind Variables, Identifier Validation, and Injection Safety

Learning outcomes

ServiceHub needs an administrative report whose selected columns and sort key are chosen at runtime. A developer concatenates status text and a requested column name into SQL. One crafted status changes the predicate, while another input attempts to turn an identifier into SQL syntax. Dynamic SQL is sometimes necessary; unsafe text construction is not.

1. Dynamic SQL has two Oracle interfaces

Native Dynamic SQL (NDS) is PL/SQL syntax such as EXECUTE IMMEDIATE and OPEN ... FOR. It is simpler and is the default when you know the number and types of inputs/outputs at compile time. DBMS_SQL exposes cursor numbers plus parse/bind/describe/define/fetch APIs. Oracle documents DBMS_SQL as required when the select list or placeholder set is unknown until runtime, or when a stored program returns an implicit query result.

DECLARE  l_count NUMBER;  l_status VARCHAR2(12) := 'OPEN';BEGIN  EXECUTE IMMEDIATE    'SELECT COUNT(*) FROM servicehub_dyn_case WHERE status_code=:b'    INTO l_count    USING l_status;  DBMS_OUTPUT.PUT_LINE('count='||l_count);END;/

2. Bind data; do not concatenate data

A bind placeholder preserves the SQL structure while the value is supplied separately. This improves cursor reuse and prevents a data value from changing the SQL grammar.

-- p_status is treated as trusted SQL text here. That is the bug.l_sql := 'SELECT COUNT(*) FROM servicehub_dyn_case ' ||         'WHERE status_code = ''' || p_status || '''';EXECUTE IMMEDIATE l_sql INTO l_count;-- If p_status is: OPEN'' OR ''1''=''1-- the predicate is changed and all rows can match.

l_sql := 'SELECT COUNT(*) FROM servicehub_dyn_case ' ||         'WHERE status_code = :status';EXECUTE IMMEDIATE l_sql INTO l_count USING p_status;-- The exact text OPEN'' OR ''1''=''1 is now only a VARCHAR2 value.-- It cannot add an OR predicate.

3. Identifiers are SQL grammar, not bind data

You cannot bind a table name, column name, keyword, or sort direction in the same way you bind a value. If a runtime identifier is genuinely necessary, first restrict it to the business-approved set and then validate/quote it with an Oracle-supported mechanism.

CREATE OR REPLACE FUNCTION servicehub_safe_order_column(  p_name IN VARCHAR2) RETURN VARCHAR2 AUTHID DEFINERIS  l_name VARCHAR2(128) := UPPER(TRIM(p_name));BEGIN  IF l_name NOT IN ('CASE_ID','STATUS_CODE','AMOUNT') THEN    RAISE_APPLICATION_ERROR(-20210,'unsupported order column');  END IF;  RETURN DBMS_ASSERT.SIMPLE_SQL_NAME(l_name);END;/

DBMS_ASSERT.SIMPLE_SQL_NAME checks that text is a syntactically valid simple SQL name. That does not prove the object/column is authorized for this API, and it does not replace an allowlist. SQL_OBJECT_NAME can additionally validate a qualified object reference exists, but existence still is not application authorization.

IF UPPER(p_direction) NOT IN ('ASC','DESC') THEN  RAISE_APPLICATION_ERROR(-20211,'unsupported sort direction');END IF;l_sql := 'SELECT case_id,status_code,amount ' ||         'FROM servicehub_dyn_case ORDER BY ' ||         servicehub_safe_order_column(p_column) || ' ' ||         UPPER(p_direction);

4. DBMS_SQL is for genuinely dynamic shapes

When the caller can choose an arbitrary projection, NDS cannot declare a fixed INTO record at compile time. DBMS_SQL can parse the statement, bind variables, describe the columns, define output buffers, execute, and fetch rows generically.

DECLARE  l_cur      INTEGER := DBMS_SQL.OPEN_CURSOR;  l_cols     INTEGER;  l_desc     DBMS_SQL.DESC_TAB3;  l_rows     INTEGER;BEGIN  DBMS_SQL.PARSE(    l_cur,    'SELECT case_id,status_code,amount FROM servicehub_dyn_case ' ||    'WHERE amount >= :min_amount',    DBMS_SQL.NATIVE  );  DBMS_SQL.BIND_VARIABLE(l_cur, ':min_amount', 100);  DBMS_SQL.DESCRIBE_COLUMNS3(l_cur, l_cols, l_desc);  l_rows := DBMS_SQL.EXECUTE(l_cur);  FOR i IN 1..l_cols LOOP    DBMS_OUTPUT.PUT_LINE(i||':'||l_desc(i).col_name);  END LOOP;  DBMS_SQL.CLOSE_CURSOR(l_cur);EXCEPTION  WHEN OTHERS THEN    IF DBMS_SQL.IS_OPEN(l_cur) THEN DBMS_SQL.CLOSE_CURSOR(l_cur); END IF;    RAISE;END;/

Real generic fetch code must call DEFINE_COLUMN/COLUMN_VALUE using the described data types. Do not adopt DBMS_SQL for a query whose shape is already known merely because it looks more advanced.

5. AUTHID still governs dynamic SQL

Dynamic SQL does not bypass PL/SQL privilege semantics. A definer-rights unit (AUTHID DEFINER, the default) executes SQL under the definer's security context; needed cross-schema object privileges for definer-rights stored code must be granted directly rather than only through roles. An invoker-rights unit (AUTHID CURRENT_USER) resolves/checks relevant external references with the invoker and is additionally subject to the INHERIT PRIVILEGES security model.

Never accept arbitrary SQL text into a privileged definer-rights package. That turns the package into a privilege-escalation interface even if every input string passes a syntax validator.

6. Reproducible lab: demonstrate injection then repair it

BEGIN EXECUTE IMMEDIATE 'DROP TABLE servicehub_dyn_case PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/CREATE TABLE servicehub_dyn_case(  case_id NUMBER PRIMARY KEY,  status_code VARCHAR2(12) NOT NULL,  amount NUMBER(10,2) NOT NULL);INSERT INTO servicehub_dyn_case VALUES(1,'OPEN',100);INSERT INTO servicehub_dyn_case VALUES(2,'CLOSED',200);INSERT INTO servicehub_dyn_case VALUES(3,'HOLD',300);COMMIT;

CREATE OR REPLACE PROCEDURE sh12_unsafe_count(  p_status IN VARCHAR2,  p_count OUT NUMBER) AUTHID DEFINER IS  l_sql VARCHAR2(4000);BEGIN  l_sql := 'SELECT COUNT(*) FROM servicehub_dyn_case '||           'WHERE status_code='''||p_status||'''';  EXECUTE IMMEDIATE l_sql INTO p_count;END;/

CREATE OR REPLACE PROCEDURE sh12_safe_count(  p_status IN VARCHAR2,  p_count OUT NUMBER) AUTHID DEFINER ISBEGIN  EXECUTE IMMEDIATE    'SELECT COUNT(*) FROM servicehub_dyn_case WHERE status_code=:b'    INTO p_count    USING p_status;END;/

VARIABLE n NUMBEREXEC sh12_unsafe_count(q'[OPEN' OR '1'='1]', :n);PRINT n-- Unsafe result: 3 rows matched because the SQL grammar changed.EXEC sh12_safe_count(q'[OPEN' OR '1'='1]', :n);PRINT n-- Safe result: 0, because that entire string is a data value.EXEC sh12_safe_count('OPEN', :n);PRINT n-- Safe result: 1.

DROP PROCEDURE sh12_unsafe_count;DROP PROCEDURE sh12_safe_count;BEGIN EXECUTE IMMEDIATE 'DROP FUNCTION servicehub_safe_order_column'; EXCEPTION WHEN OTHERS THEN NULL; END;/DROP TABLE servicehub_dyn_case PURGE;

7. Production judgment

Prefer static SQL when the statement is known. Use NDS for runtime text with a known bind/result shape and DBMS_SQL for true metadata-driven shapes. Bind values, allowlist identifiers/fragments, validate names with DBMS_ASSERT where appropriate, and keep privileged dynamic APIs narrowly scoped. Log statement templates/signatures—not secrets or raw sensitive bind values.

No option, pack, restart, or COMPATIBLE change is required. Lesson 3 moves from runtime-generated SQL to runtime-generated work: Oracle Scheduler executes database jobs independently of application requests and therefore needs explicit time, identity, overlap, retry, and observability contracts.

Authoritative references

• PL/SQL Dynamic SQL — native dynamic SQL versus DBMS_SQL selection
• DBMS_SQL Package — runtime describe/bind/fetch cases
• DBMS_ASSERT — identifier/literal assertion routines
• SQL Injection — bind and validation defenses
• Database Security Guide — definer/invoker rights privilege model