PL/SQL & T-SQL (Oracle and SQL Server) Module 13 – Dynamic SQL and security in PL/SQL
Dynamic SQL in PL/SQL without injection
Run PL/SQL dynamic SQL with EXECUTE IMMEDIATE and bind variables, check names with an allowlist and DBMS_ASSERT, and pick OPEN FOR or DBMS_SQL.
What you will learn
- Run dynamic SQL with EXECUTE IMMEDIATE, passing values in USING and reading results with INTO and RETURNING INTO
- Show why a value concatenated into statement text can change the statement, and fix it with a bind variable
- Validate a table or column name with an allowlist and DBMS_ASSERT before it enters statement text
- Choose OPEN FOR or DBMS_SQL when the binds or the select list are known only at run time
Before you start
What you’ll learn · Before you start
What you will learn
- Run dynamic SQL with EXECUTE IMMEDIATE, passing values in USING and reading results with INTO and RETURNING INTO
- Show why a value concatenated into statement text can change the statement, and fix it with a bind variable
- Validate a table or column name with an allowlist and DBMS_ASSERT before it enters statement text
- Choose OPEN FOR or DBMS_SQL when the binds or the select list are known only at run time
Before you start
On this page
A customer search that pastes the caller’s text into its WHERE clause matches all 4 practice customers for the test
input x' OR '1'='1, and fails with an error for an ordinary name such as Neha D’Souza. The same search with a bind
variable matches 0 rows for both, which is right, because no customer has either name.
You will run statements built at run time with EXECUTE IMMEDIATE, pass every value as a bind variable, check the
table and column names a bind cannot carry against an allowlist and DBMS_ASSERT, and choose OPEN FOR or DBMS_SQL
when the number of filters or columns is known only at run time.
EXECUTE IMMEDIATE runs text, and USING carries the values
native dynamic SQL: PL/SQL's built-in way to run a statement whose text is built at run time: EXECUTE IMMEDIATE for most statements and OPEN FOR for queries, with values passed in a USING clause. runs a statement that your code builds as a string. Its main
statement, EXECUTE IMMEDIATE, prepares the string and runs it in one operation. To read the balance of account 3,
EXECUTE IMMEDIATE 'SELECT balance FROM accounts WHERE account_id = :id' INTO v_balance USING 3 sets v_balance to
760.25. The :id in the text is a placeholder, and the 3 in USING is its
bind variable: A placeholder such as :id in a statement's text whose value is supplied separately when the statement runs, so the database uses the value only as data and never reads it as SQL.: the value reaches the database apart from the text. In a SQL statement,
placeholders match the USING list by position, not by name.
Oracle’s native dynamic SQL page says where the values and the results of each kind of statement go:
| The dynamic statement | Values in | Results out |
|---|---|---|
A SELECT of at most one row |
USING |
INTO variables or a record |
A SELECT of many rows |
USING |
BULK COLLECT INTO collections |
INSERT, UPDATE, DELETE or MERGE |
USING |
RETURNING INTO, when the text has a RETURNING clause |
An anonymous block or a CALL |
USING, with IN OUT or OUT modes |
the OUT binds in USING |
The script runs the first three rows of the table on the practice data: a one-row SELECT, the 3 savings accounts
with BULK COLLECT INTO, and a deposit of 500 whose new balance, 1260.25, comes back through
RETURNING INTO: A clause of INSERT, UPDATE, DELETE and MERGE that hands values of the changed row, such as a new identity value, back to variables in the same statement. More in “SQL inside PL/SQL: SELECT INTO and DML”. It ends with a NULL bind and a dynamic CREATE TABLE:
-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- Native dynamic SQL on the practice tables: a one-row SELECT with INTO and USING, a many-row SELECT with
-- BULK COLLECT INTO, an UPDATE with RETURNING INTO, a NULL passed through a variable that was never assigned,
-- and the implicit commit of DDL. Every value travels as a bind variable (:id, :k, :amt); only fixed text is in the
-- strings.
SET SERVEROUTPUT ON
DECLARE
TYPE id_list IS TABLE OF accounts.account_id%TYPE;
v_sql VARCHAR2(200);
v_balance accounts.balance%TYPE;
v_new accounts.balance%TYPE;
v_ids id_list;
v_no_memo ledger.memo%TYPE; -- never assigned, so NULL: USING does not accept the word NULL
BEGIN
-- At most one row: the column goes INTO a variable, the value comes from USING.
v_sql := 'SELECT balance FROM accounts WHERE account_id = :id';
EXECUTE IMMEDIATE v_sql INTO v_balance USING 3;
DBMS_OUTPUT.PUT_LINE('account 3 balance ' || TO_CHAR(v_balance, 'FM99990.00'));
-- Many rows: BULK COLLECT INTO a collection.
EXECUTE IMMEDIATE 'SELECT account_id FROM accounts WHERE kind = :k ORDER BY account_id'
BULK COLLECT INTO v_ids USING 'savings';
DBMS_OUTPUT.PUT_LINE('savings accounts ' || v_ids.COUNT);
-- DML with RETURNING: IN binds in USING, the OUT bind in RETURNING INTO; SQL%ROWCOUNT works as in static SQL.
EXECUTE IMMEDIATE
'UPDATE accounts SET balance = balance + :amt WHERE account_id = :id RETURNING balance INTO :nb'
USING 500, 3 RETURNING INTO v_new;
DBMS_OUTPUT.PUT_LINE('account 3 after the deposit ' || TO_CHAR(v_new, 'FM99990.00') || ', rows ' || SQL%ROWCOUNT);
-- A NULL value: bind the unassigned variable.
EXECUTE IMMEDIATE 'UPDATE ledger SET memo = :m WHERE entry_id = :e' USING v_no_memo, 4;
DBMS_OUTPUT.PUT_LINE('memo cleared on ' || SQL%ROWCOUNT || ' entry');
ROLLBACK;
END;
/
-- Expected:
-- account 3 balance 760.25
-- savings accounts 3
-- account 3 after the deposit 1260.25, rows 1
-- memo cleared on 1 entry
-- DDL commits the transaction before and after it runs, so a ROLLBACK after it has nothing left to undo.
BEGIN
UPDATE accounts SET balance = balance + 1 WHERE account_id = 1;
EXECUTE IMMEDIATE 'CREATE TABLE report_scratch (n NUMBER)';
ROLLBACK;
END;
/
SELECT balance FROM accounts WHERE account_id = 1;
-- Expected: 2501 (the UPDATE was committed by CREATE TABLE, so the ROLLBACK did not undo it)
-- Put the practice data back.
UPDATE accounts SET balance = balance - 1 WHERE account_id = 1;
COMMIT;
DROP TABLE report_scratch PURGE;"""execute_immediate_basics.sql in the lab: SQL*Plus runs it as LAB on a freshly loaded practice schema."""
from oracle_session import Lab
with Lab() as lab:
lab.sqlplus("basics/execute_immediate_basics.sql")Output
$ sqlplus -s lab@//localhost/FREEPDB1 @basics/execute_immediate_basics.sql
account 3 balance 760.25
savings accounts 3
account 3 after the deposit 1260.25, rows 1
memo cleared on 1 entry
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
BALANCE
----------
2501
1 row updated.
Commit complete.
Table dropped.
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 execute_immediate_basics_session.py
Two rules from the EXECUTE IMMEDIATE reference catch people out. USING does not accept the word NULL, so the
script binds a variable that was never assigned. And DDL commits: the database commits the open transaction before
and after every DDL statement, so the ROLLBACK after the dynamic CREATE TABLE undoes nothing, and account 1 keeps
the extra rupee its UPDATE added (2501).
A dynamic SELECT with no INTO
Wrong: EXECUTE IMMEDIATE 'SELECT balance FROM accounts WHERE account_id = :id' USING 3 with no INTO. Oracle’s
reference says such a statement never runs, so no row is read. Right: add INTO for one row, or BULK COLLECT INTO
for many.
Dynamic SQL is for text you cannot write in advance: a table or column chosen at run time, a WHERE clause whose
number of conditions varies, or DDL. When the text is fixed, Oracle’s “when you need dynamic SQL” page recommends
static SQL: compiling it checks the objects and the privileges and records the dependencies, and a PL/SQL variable in
a static statement is a bind variable already.
Checkpoint
Check: EXECUTE IMMEDIATE with USING, INTO and RETURNING INTO
A value pasted into the text can change the statement
Here is a search written the weak way: count_customers_weak builds
'SELECT COUNT(*) FROM customers WHERE full_name = ''' || p_name || '''' and runs it. The name becomes part of the
statement, so the parser reads whatever the name holds as SQL. Oracle’s SQL injection page traces every
SQL injection: A weakness where input pasted into a statement's text changes what the statement does; typed parameters, allowlists for names and least privilege prevent it. More in “Dynamic SQL with sp_executesql, safely” technique to one weakness: string input that is not validated and is
concatenated into a dynamic statement.
Step through three names, an ordinary customer, a name with an apostrophe and the check’s test input, first through the weak version and then through the fixed one:
The drawing shows the whole scene; its 5 steps are listed below.
Every step as text
- An ordinary name, pasted in. The weak version puts Meera Iyer between two quotes in the statement text. The parser reads one comparison, full_name against a literal, and 1 of the 4 customers matches. The check expects 1, so it passes.
- An apostrophe ends the literal. For Neha D'Souza the quote inside the name closes the literal after D. The parser then meets Souza where the comparison should end, so the text no longer parses and the call fails with ORA-03049. An ordinary customer breaks the search.
- The test input becomes code. The check's third input closes the literal and adds OR '1'='1', which the parser reads as a second comparison that is always true. All 4 customers match where none should, and the check fails.
- The fix sends fixed text. count_customers sends the same text on every call, full_name = :n, and passes the name separately with USING. The parser reads a comparison with a placeholder; no input ever reaches it.
- The value stays a value. The database compares full_name with each whole string as data. Meera Iyer matches 1 row, and the other two inputs match 0, exactly what the check expects for all three names.
The fix is the bind variable of the first section. count_customers sends the same text, full_name = :n, on every
call and passes the name with USING p_name. Oracle’s page calls bind variables the most effective guard, because
the database uses their values only as data and never interprets them. This model runs both versions on the 4
practice customers and checks each result against the rows the name should match. Press Edit, add a name to NAMES,
and run it again:
"""One customer search two ways, what DBMS_ASSERT accepts, and a sort column from an allowlist.
injection_demo_plsql.sql runs the search in Oracle; this model runs it on the four practice customers
with a small reader for WHERE text (comparisons with =, AND, OR, quoted literals and bind placeholders)
that reports a word it cannot read after a comparison the way Oracle AI Database 26ai does (ORA-03049).
The weak search pastes the name into the text; the fixed one binds it, as EXECUTE IMMEDIATE ... USING does.
A check compares each search with the rows the name should match. The DBMS_ASSERT part follows the
rules on Oracle's DBMS_ASSERT page; assert_matrix.sql makes the same calls in the database.
Edit NAMES or SORT_REQUESTS and run it again.
"""
import re
CUSTOMERS = [
{"customer_id": 1, "full_name": "Asha Rao", "city": "Pune"},
{"customer_id": 2, "full_name": "Vikram Nair", "city": "Kochi"},
{"customer_id": 3, "full_name": "Farida Shaikh", "city": "Lucknow"},
{"customer_id": 4, "full_name": "Meera Iyer", "city": "Chennai"},
]
NAMES = ["Meera Iyer", "Neha D'Souza", "x' OR '1'='1"]
class OracleError(Exception):
pass
TOKEN = re.compile(r"\s*(?:(?P<lit>'(?:[^']|'')*')|(?P<bind>:\w+)|(?P<word>[A-Za-z_][A-Za-z0-9_$#]*)|(?P<eq>=))")
def tokens(text):
"""The WHERE text as (kind, value, start) tokens. A quote that never closes ends the list with a "bad"
token, which is an error only when the reader gets that far."""
out, pos = [], 0
while text[pos:].strip():
m = TOKEN.match(text, pos)
if not m:
out.append(("bad", text[pos:].strip(), pos))
break
kind = m.lastgroup
val, start = m.group(kind), m.start(kind)
if kind == "lit":
out.append(("lit", val[1:-1].replace("''", "'"), start))
elif kind == "word" and val.upper() in ("AND", "OR"):
out.append(("op", val.upper(), start))
elif kind == "word":
out.append(("col", val.lower(), start))
else:
out.append((kind, val, start))
pos = m.end()
return out
def parse(toks, text):
"""condition := comparison {AND comparison} {OR ...}; AND binds tighter than OR, as in SQL."""
def comparison(i):
part = toks[i:i + 3]
if any(t[0] == "bad" for t in part):
raise OracleError("ORA-01756: quoted string not properly terminated")
if len(part) < 3 or part[1][0] != "eq" or "op" in (part[0][0], part[2][0]):
raise OracleError("ORA-00920: invalid relational operator")
return ("eq", part[0], part[2]), i + 3
def conjunction(i):
node, i = comparison(i)
while i < len(toks) and toks[i][:2] == ("op", "AND"):
right, i = comparison(i + 1)
node = ("and", node, right)
return node, i
node, i = conjunction(0)
while i < len(toks) and toks[i][:2] == ("op", "OR"):
right, i = conjunction(i + 1)
node = ("or", node, right)
if i < len(toks) and toks[i][0] == "col": # a word where the condition should end: 26ai names it
word, start = toks[i][1], toks[i][2]
raise OracleError(f"ORA-03049: SQL keyword '{word.upper()}' is not syntactically valid following "
f"'...WHERE {text[:start].rstrip()}'")
if i < len(toks) and toks[i][0] == "bad":
raise OracleError("ORA-01756: quoted string not properly terminated")
if i != len(toks):
raise OracleError("ORA-00933: SQL command not properly ended")
return node
def value(token, row, binds):
kind, v = token[0], token[1]
if kind == "col":
if v not in row:
raise OracleError(f'ORA-00904: "{v.upper()}": invalid identifier')
return row[v]
return binds[v] if kind == "bind" else v
def holds(node, row, binds):
if node[0] == "eq":
return value(node[1], row, binds) == value(node[2], row, binds)
left, right = holds(node[1], row, binds), holds(node[2], row, binds)
return (left and right) if node[0] == "and" else (left or right)
def search(where, binds=None):
tree = parse(tokens(where), where)
return [r["customer_id"] for r in CUSTOMERS if holds(tree, r, binds or {})]
def weak_search(name):
return search("full_name = '" + name + "'") # the name becomes part of the statement text
def fixed_search(name):
return search("full_name = :n", {":n": name}) # EXECUTE IMMEDIATE ... USING p_name
print("One search, checked against the rows each name should match")
for label, run in (("weak: name concatenated", weak_search), ("fixed: name bound with USING", fixed_search)):
print(f" {label}")
for name in NAMES:
want = [r["customer_id"] for r in CUSTOMERS if r["full_name"] == name]
try:
got = run(name)
shown = f"{len(got)} row" + ("" if len(got) == 1 else "s")
verdict = "check passed" if got == want else "check FAILED: the input changed the statement"
except OracleError as err:
shown, verdict = "error", f"check FAILED: {err}"
print(f" {name!r:<16} -> {shown:<7} {verdict}")
# DBMS_ASSERT, as its reference page states the rules (a model of one schema, not the package itself).
OBJECTS = {"ACCOUNTS", "CUSTOMERS", "LEDGER", "STOCK", "ORDERS"}
def simple_sql_name(s):
name = s.strip() # leading and trailing white space is allowed
quoted = len(name) > 2 and name[0] == name[-1] == '"' and '"' not in name[1:-1].replace('""', "")
plain = re.fullmatch(r"[A-Za-z][A-Za-z0-9_$#]*", name) is not None
if not (quoted or plain):
raise OracleError("ORA-44003: string is not a simple SQL name")
return s
def sql_object_name(s):
name = s.strip()
name = name[1:-1] if len(name) > 1 and name[0] == name[-1] == '"' else name.upper()
if name not in OBJECTS:
raise OracleError("ORA-44002: invalid object name")
return s
def enquote_name(s, capitalize=True):
if not (len(s) > 1 and s[0] == s[-1] == '"'):
s = '"' + (s.upper() if capitalize else s) + '"'
if '"' in s[1:-1].replace('""', ""):
raise OracleError("rejected: a double quote inside the name is not paired")
return s
def enquote_literal(s):
body = s[1:-1] if len(s) > 1 and s[0] == s[-1] == "'" else s
if "'" in body.replace("''", ""):
raise OracleError("ORA-06502: PL/SQL: value or conversion error")
return "'" + body + "'"
CALLS = [
("SIMPLE_SQL_NAME", simple_sql_name, "balance"),
("SIMPLE_SQL_NAME", simple_sql_name, "balance desc"),
("SIMPLE_SQL_NAME", simple_sql_name, '"Sort Key"'),
("SQL_OBJECT_NAME", sql_object_name, "accounts"),
("SQL_OBJECT_NAME", sql_object_name, "ledger"),
("SQL_OBJECT_NAME", sql_object_name, "accounts a"),
("ENQUOTE_NAME", enquote_name, "balance"),
("ENQUOTE_NAME, FALSE", lambda s: enquote_name(s, False), "balance"),
("ENQUOTE_LITERAL", enquote_literal, "Neha D'Souza"),
("ENQUOTE_LITERAL", enquote_literal, "Neha D''Souza"),
]
print("What each DBMS_ASSERT function does with an input")
for fn, call, arg in CALLS:
try:
outcome = "returns " + call(arg)
except OracleError as err:
outcome = str(err)
print(f" {fn:<20} {arg!r:<16} {outcome}")
# A sort column from an allowlist: the caller names a key, the code supplies the column.
ACCOUNTS = [
{"ACCOUNT_ID": 1, "KIND": "savings", "BALANCE": 2500.00},
{"ACCOUNT_ID": 2, "KIND": "current", "BALANCE": 18000.00},
{"ACCOUNT_ID": 3, "KIND": "savings", "BALANCE": 760.25},
{"ACCOUNT_ID": 4, "KIND": "savings", "BALANCE": 41250.50},
]
SORT_COLUMNS = {"id": "ACCOUNT_ID", "balance": "BALANCE", "kind": "KIND"}
SORT_REQUESTS = [("balance", True), ("ledger", False), ("balance desc", False)]
print("A sort key from the caller, mapped through the allowlist")
for key, descending in SORT_REQUESTS:
column = SORT_COLUMNS.get(key)
if column is None:
print(f" {key!r:<16} refused: not on the allowlist (ORA-20031 from the procedure)")
continue
order_by = f"ORDER BY {enquote_name(column, False)} {'DESC' if descending else 'ASC'}"
ranked = sorted(ACCOUNTS, key=lambda a: a[column], reverse=descending)
print(f" {key!r:<16} {order_by:<24} accounts {', '.join(str(a['ACCOUNT_ID']) for a in ranked)}")Output
One search, checked against the rows each name should match
weak: name concatenated
'Meera Iyer' -> 1 row check passed
"Neha D'Souza" -> error check FAILED: ORA-03049: SQL keyword 'SOUZA' is not syntactically valid following '...WHERE full_name = 'Neha D''
"x' OR '1'='1" -> 4 rows check FAILED: the input changed the statement
fixed: name bound with USING
'Meera Iyer' -> 1 row check passed
"Neha D'Souza" -> 0 rows check passed
"x' OR '1'='1" -> 0 rows check passed
What each DBMS_ASSERT function does with an input
SIMPLE_SQL_NAME 'balance' returns balance
SIMPLE_SQL_NAME 'balance desc' ORA-44003: string is not a simple SQL name
SIMPLE_SQL_NAME '"Sort Key"' returns "Sort Key"
SQL_OBJECT_NAME 'accounts' returns accounts
SQL_OBJECT_NAME 'ledger' returns ledger
SQL_OBJECT_NAME 'accounts a' ORA-44002: invalid object name
ENQUOTE_NAME 'balance' returns "BALANCE"
ENQUOTE_NAME, FALSE 'balance' returns "balance"
ENQUOTE_LITERAL "Neha D'Souza" ORA-06502: PL/SQL: value or conversion error
ENQUOTE_LITERAL "Neha D''Souza" returns 'Neha D''Souza'
A sort key from the caller, mapped through the allowlist
'balance' ORDER BY "BALANCE" DESC accounts 4, 2, 1, 3
'ledger' refused: not on the allowlist (ORA-20031 from the procedure)
'balance desc' refused: not on the allowlist (ORA-20031 from the procedure)
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 injection_model.py
Runs on this device, in your browser. The first run downloads Python (about 13.5 MB), which is kept for the next runs.
Your run, in this browser
The weak version passes for Meera Iyer, fails with ORA-03049 for Neha D’Souza and matches all 4 customers for the test input; the bound version passes all three. Oracle’s page for ORA-03049 gives this as one of its causes: a string that ends early, often at an apostrophe, so that the rest of it is read as keywords. The same check runs in the database below. Run it on your own practice schema only:
-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- One customer search written two ways, and a check that compares each with the rows a name should match.
-- The weak version pastes the name into the statement text; the fixed version binds it with USING. Run the
-- check on your own practice schema only. examples/model/injection_model.py prints the same counts for the same
-- three names.
SET SERVEROUTPUT ON
SET LINESIZE 200
-- WEAK: the name becomes part of the statement text.
CREATE OR REPLACE PROCEDURE count_customers_weak (p_name IN VARCHAR2, p_rows OUT PLS_INTEGER)
AUTHID DEFINER
IS
BEGIN
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM customers WHERE full_name = ''' || p_name || ''''
INTO p_rows;
END;
/
-- FIXED: the text never changes; the name travels as the value of :n.
CREATE OR REPLACE PROCEDURE count_customers (p_name IN VARCHAR2, p_rows OUT PLS_INTEGER)
AUTHID DEFINER
IS
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM customers WHERE full_name = :n'
INTO p_rows USING p_name;
END;
/
-- The check: static SQL gives the expected count, and each version must match it for every name.
DECLARE
TYPE name_list IS TABLE OF VARCHAR2(40);
v_names name_list := name_list('Meera Iyer', 'Neha D''Souza', 'x'' OR ''1''=''1');
v_want PLS_INTEGER;
v_got PLS_INTEGER;
FUNCTION verdict (p_got PLS_INTEGER, p_want PLS_INTEGER) RETURN VARCHAR2 IS
BEGIN
RETURN p_got || ' rows: ' || CASE WHEN p_got = p_want THEN 'check passed'
ELSE 'check FAILED: the input changed the statement' END;
END;
BEGIN
FOR i IN 1 .. v_names.COUNT LOOP
SELECT COUNT(*) INTO v_want FROM customers WHERE full_name = v_names(i);
BEGIN
count_customers_weak(v_names(i), v_got);
DBMS_OUTPUT.PUT_LINE(RPAD('weak ' || v_names(i), 22) || verdict(v_got, v_want));
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(RPAD('weak ' || v_names(i), 22) || 'check FAILED: ' || SQLERRM);
END;
count_customers(v_names(i), v_got);
DBMS_OUTPUT.PUT_LINE(RPAD('fixed ' || v_names(i), 22) || verdict(v_got, v_want));
END LOOP;
END;
/
-- Expected:
-- weak Meera Iyer 1 rows: check passed
-- fixed Meera Iyer 1 rows: check passed
-- weak Neha D'Souza check FAILED: ORA-03049: SQL keyword 'SOUZA' is not syntactically valid following '...WHERE full_name = 'Neha D''
-- fixed Neha D'Souza 0 rows: check passed
-- weak x' OR '1'='1 4 rows: check FAILED: the input changed the statement
-- fixed x' OR '1'='1 0 rows: check passed
-- The weak version has done its job as a test subject: never leave it in a schema.
DROP PROCEDURE count_customers_weak;"""injection_demo_plsql.sql in the lab: SQL*Plus runs it as LAB on a freshly loaded practice schema."""
from oracle_session import Lab
with Lab() as lab:
lab.sqlplus("injection/injection_demo_plsql.sql")Output
$ sqlplus -s lab@//localhost/FREEPDB1 @injection/injection_demo_plsql.sql Procedure created. Procedure created. weak Meera Iyer 1 rows: check passed fixed Meera Iyer 1 rows: check passed weak Neha D'Souza check FAILED: ORA-03049: SQL keyword 'SOUZA' is not syntactically valid following '...WHERE full_name = 'Neha D'' fixed Neha D'Souza 0 rows: check passed weak x' OR '1'='1 4 rows: check FAILED: the input changed the statement fixed x' OR '1'='1 0 rows: check passed PL/SQL procedure successfully completed. Procedure dropped.
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 injection_demo_plsql_session.py
Concatenated dates and numbers are input too. A date pasted into text is converted to characters with the session’s
NLS date format, which the session itself can change, so Oracle’s guidance is to bind the value or, when you cannot,
to convert it with an explicit format model such as TO_CHAR(d, 'YYYY-MM-DD') after the keyword DATE.
Doubling quotes by hand, or blocking apostrophes
Wrong: REPLACE(p_name, '''', '''''') before concatenating, or refusing every name with an apostrophe. The first is
easy to forget in one of many places, and OWASP’s input validation cheat sheet notes that blocking apostrophes turns
away real names without making a query safe. Right: bind the value with USING.
Checkpoint
Check: Why a concatenated value can change the statement
Names cannot be bound: map them first, then quote them
A report sorted by a column the caller picks cannot use a placeholder for that column: a bind carries a value, never a
table or column name. OWASP’s SQL injection prevention cheat sheet gives the defence for such parts of a statement:
map the caller’s choice to a name the code itself holds, an allowlist: A fixed list of values known to be safe, such as the column names a caller may sort by; input that is not on the list is refused instead of cleaned. More in “Dynamic SQL with sp_executesql, safely”, so that no
unchecked input reaches the text. Follow one sort key through accounts_report, from the caller to the finished
ORDER BY:
The drawing shows the whole scene; its 5 steps are listed below.
Every step as text
- A name cannot be bound. A placeholder stands for a value, never for a table or a column, so a sort column has to become part of the text. This is the one place where caller input could touch the statement.
- The code supplies the column. A CASE maps the key to a column the code names itself, ACCOUNT_ID, BALANCE or KIND. Any other key, such as ledger or balance desc, gets ORA-20031 before a single character of text is built.
- Quote it, keep the stored name. ENQUOTE_NAME wraps the column in double quotes. With FALSE it keeps the name as given, and the CASE gives the upper-case name Oracle stores, so "BALANCE" matches the column exactly.
- The direction is a BOOLEAN. p_descending is TRUE or FALSE, and the code turns it into DESC or ASC. The caller never sends a keyword that is pasted into the text.
- Open the finished text. The statement now holds fixed text, one mapped and quoted name and one fixed keyword. Sorted by balance, descending, it lists accounts 4, 2, 1 and 3.
The DBMS_ASSERT: An Oracle-supplied package of functions that check or quote a piece of SQL text, such as SIMPLE_SQL_NAME and ENQUOTE_NAME, and raise an error when the input does not have the expected form. package adds checks and quoting for the names that do reach the text.
SIMPLE_SQL_NAME checks the form of a name and raises ORA-44003 when it is not a simple SQL name;
QUALIFIED_SQL_NAME checks a dotted name (ORA-44004); SQL_OBJECT_NAME checks that an object exists (ORA-44002), and
SCHEMA_NAME that a schema does (ORA-44001). ENQUOTE_NAME wraps a name in double quotes, upper-casing an unquoted
name unless its second argument is FALSE, and ENQUOTE_LITERAL wraps a value in single quotes after checking that
every quote inside it is doubled. NOOP returns its input without any check.
The model above also prints what each function does with the inputs of assert_matrix.sql. Two of its lines show why
the allowlist comes first. "Sort Key" passes SIMPLE_SQL_NAME, because a quoted name may hold any characters, and
ledger passes SQL_OBJECT_NAME, because the table exists, yet neither is a column the report should sort by.
Oracle’s own caution says the same: the package helps validation code but does not replace it. A third line is a quiet
bug: ENQUOTE_NAME('balance', FALSE) returns "balance", a lower-case name that is not the column BALANCE, so the
CASE in the script hands it the upper-case name Oracle stores.
-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- Part 1 calls the DBMS_ASSERT functions on the inputs examples/model/injection_model.py uses, and prints what
-- each returns or raises. Part 2 is a report procedure whose sort column comes from an allowlist, is quoted
-- with ENQUOTE_NAME, and whose direction comes from a BOOLEAN. The expected lines follow Oracle's DBMS_ASSERT
-- reference page.
SET SERVEROUTPUT ON
DECLARE
PROCEDURE probe (p_fn VARCHAR2, p_arg VARCHAR2) IS
v_out VARCHAR2(200);
BEGIN
v_out := CASE p_fn
WHEN 'SIMPLE_SQL_NAME' THEN DBMS_ASSERT.SIMPLE_SQL_NAME(p_arg)
WHEN 'SQL_OBJECT_NAME' THEN DBMS_ASSERT.SQL_OBJECT_NAME(p_arg)
WHEN 'ENQUOTE_NAME' THEN DBMS_ASSERT.ENQUOTE_NAME(p_arg)
WHEN 'ENQUOTE_NAME FALSE' THEN DBMS_ASSERT.ENQUOTE_NAME(p_arg, FALSE)
WHEN 'ENQUOTE_LITERAL' THEN DBMS_ASSERT.ENQUOTE_LITERAL(p_arg)
END;
DBMS_OUTPUT.PUT_LINE(RPAD(p_fn, 20) || RPAD(p_arg, 16) || 'returns ' || v_out);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(RPAD(p_fn, 20) || RPAD(p_arg, 16) || SQLERRM);
END;
BEGIN
probe('SIMPLE_SQL_NAME', 'balance');
probe('SIMPLE_SQL_NAME', 'balance desc');
probe('SIMPLE_SQL_NAME', '"Sort Key"');
probe('SQL_OBJECT_NAME', 'accounts');
probe('SQL_OBJECT_NAME', 'ledger');
probe('SQL_OBJECT_NAME', 'accounts a');
probe('ENQUOTE_NAME', 'balance');
probe('ENQUOTE_NAME FALSE', 'balance');
probe('ENQUOTE_LITERAL', 'Neha D''Souza');
probe('ENQUOTE_LITERAL', 'Neha D''''Souza');
END;
/
-- Expected (outcomes as the reference page states them):
-- SIMPLE_SQL_NAME balance returns balance
-- SIMPLE_SQL_NAME balance desc ORA-44003 (not a simple SQL name)
-- SIMPLE_SQL_NAME "Sort Key" returns "Sort Key"
-- SQL_OBJECT_NAME accounts returns accounts
-- SQL_OBJECT_NAME ledger returns ledger
-- SQL_OBJECT_NAME accounts a ORA-44002 (invalid object name)
-- ENQUOTE_NAME balance returns "BALANCE"
-- ENQUOTE_NAME FALSE balance returns "balance"
-- ENQUOTE_LITERAL Neha D'Souza ORA-06502 (value error: the quote is not paired)
-- ENQUOTE_LITERAL Neha D''Souza returns 'Neha D''Souza'
-- Part 2: the caller names a sort key; the code supplies the column and the direction.
CREATE OR REPLACE PROCEDURE accounts_report (
p_sort_key IN VARCHAR2,
p_descending IN BOOLEAN DEFAULT FALSE,
p_rows OUT SYS_REFCURSOR
) AUTHID DEFINER
IS
v_column VARCHAR2(128);
BEGIN
v_column := CASE LOWER(TRIM(p_sort_key)) -- 1. the allowlist
WHEN 'id' THEN 'ACCOUNT_ID'
WHEN 'balance' THEN 'BALANCE'
WHEN 'kind' THEN 'KIND'
END;
IF v_column IS NULL THEN
RAISE_APPLICATION_ERROR(-20031, 'Unknown sort key: use id, balance or kind');
END IF;
OPEN p_rows FOR
'SELECT account_id, kind, balance FROM accounts ORDER BY '
|| DBMS_ASSERT.ENQUOTE_NAME(v_column, FALSE) -- 2. quoted, keeping the stored upper-case name
|| CASE WHEN p_descending THEN ' DESC' ELSE ' ASC' END; -- 3. direction from a BOOLEAN, never text
END;
/
VARIABLE rc REFCURSOR
BEGIN
accounts_report('balance', TRUE, :rc);
END;
/
PRINT rc
-- Expected: accounts 4, 2, 1 and 3 (41250.50, 18000.00, 2500.00, 760.25)
BEGIN
accounts_report('ledger', FALSE, :rc);
END;
/
-- Expected: ORA-20031: Unknown sort key: use id, balance or kind"""assert_matrix.sql in the lab: SQL*Plus runs it as LAB on a freshly loaded practice schema."""
from oracle_session import Lab
with Lab() as lab:
lab.sqlplus("names/assert_matrix.sql", errors=1)Output
$ sqlplus -s lab@//localhost/FREEPDB1 @names/assert_matrix.sql
SIMPLE_SQL_NAME balance returns balance
SIMPLE_SQL_NAME balance desc ORA-44003: invalid SQL name
SIMPLE_SQL_NAME "Sort Key" returns "Sort Key"
SQL_OBJECT_NAME accounts returns accounts
SQL_OBJECT_NAME ledger returns ledger
SQL_OBJECT_NAME accounts a ORA-44002: invalid object name
ENQUOTE_NAME balance returns "BALANCE"
ENQUOTE_NAME FALSE balance returns "balance"
ENQUOTE_LITERAL Neha D'Souza ORA-06502: PL/SQL: value or conversion error
ENQUOTE_LITERAL Neha D''Souza returns 'Neha D''Souza'
PL/SQL procedure successfully completed.
Procedure created.
PL/SQL procedure successfully completed.
ACCOUNT_ID KIND BALANCE
---------- ---------- ----------
4 savings 41250.5
2 current 18000
1 savings 2500
3 savings 760.25
BEGIN
*
ERROR at line 1:
ORA-20031: Unknown sort key: use id, balance or kind
ORA-06512: at "LAB.ACCOUNTS_REPORT", line 15
ORA-06512: at line 2
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 assert_matrix_session.py
Key idea
Bind every value. Map every name through an allowlist the code holds, then quote it with ENQUOTE_NAME. Use the
other DBMS_ASSERT checks as a second guard, never as the only one.
Checkpoint
Check: Names through an allowlist and DBMS_ASSERT
OPEN FOR returns many rows; DBMS SQL copes with a varying shape
A search with optional filters, by city and by a minimum balance, has 0, 1 or 2 placeholders depending on the call.
EXECUTE IMMEDIATE and OPEN FOR take a USING list written when the code is compiled, so they cannot bind a number
of values that changes from call to call. Oracle’s dynamic SQL chapter draws the line: native dynamic SQL needs the
number and types of the inputs and outputs at compile time, and without them you use the
DBMS_SQL: An Oracle-supplied package that runs dynamic SQL through a cursor number, with separate calls to parse, bind, execute and fetch, for statements whose binds or select list are known only at run time. package.
The OPEN FOR statement: The PL/SQL statement OPEN cursor_variable FOR query, which runs the query, fixes its result set and places the cursor before the first row; the query may also be held in a string, with its values given in a USING clause. is the native way to run a many-row query held in a
string: it opens a cursor variable: A PL/SQL variable that points to the work area of an open query instead of naming one query, declared as SYS_REFCURSOR or of a REF CURSOR type; a function can return it and a caller can receive it, fetch its rows and close it., with its values in USING, and the code fetches
the rows or hands the cursor to a caller, as accounts_report does. DBMS_SQL works through a cursor number: PARSE,
then BIND_VARIABLE by name for each placeholder the text holds, then EXECUTE, and either FETCH_ROWS with
COLUMN_VALUE or DBMS_SQL.TO_REFCURSOR, which turns the cursor into an ordinary ref cursor. DESCRIBE_COLUMNS2 lists
the columns of a query the code has never seen. TO_CURSOR_NUMBER converts the other way, and after either conversion
only the new form may be used. Answer two questions about a statement to see which of the three fits:
Answer the questions above: the table then marks what fits your case and says why the rest does not.
| Tool | Rows it handles | Must be known when you write it | Code and speed |
|---|---|---|---|
| EXECUTE IMMEDIATE Written as: EXECUTE IMMEDIATE … USING | Good one row INTO, many with BULK COLLECT, or DML and DDL | Mixed the number and types of binds and columns | Good one statement, faster than DBMS_SQL |
| OPEN FOR Written as: OPEN rc FOR … USING | Good many rows, fetched in a loop or passed to a caller | Mixed the number and types of binds and columns | Good native code, faster than DBMS_SQL |
| DBMS_SQL Written as: PARSE, BIND_VARIABLE, … | Good any query, DML or block, by cursor number | Good nothing; binds and columns are found at run time | Poor many calls, slower and longer than native code |
How the answers decide
- “What does the statement give back?” One row, or nothing (DML, DDL) and “Do you know the binds and the select list when you write the code?” Yes, a fixed number and types: rules out DBMS_SQL and adds a caveat to OPEN FOR. EXECUTE IMMEDIATE runs it in one statement, with INTO for the row and USING for the values; a cursor only adds code.
- “What does the statement give back?” Many rows to fetch or hand on and “Do you know the binds and the select list when you write the code?” Yes, a fixed number and types: rules out DBMS_SQL and adds a caveat to EXECUTE IMMEDIATE. OPEN FOR fetches the rows or hands the cursor to a caller; EXECUTE IMMEDIATE fits only if BULK COLLECT INTO is enough.
- “Do you know the binds and the select list when you write the code?” No, they vary at run time: rules out EXECUTE IMMEDIATE and OPEN FOR. Native dynamic SQL needs the number and types of binds and columns at compile time; DBMS_SQL finds them at run time.
find_accounts appends only fixed text, one condition for each filter the caller gives, binds exactly those
placeholders by name, and returns the rows with TO_REFCURSOR. With no filter it lists accounts 1, 2, 3 and 4; for
Pune, 1 and 2; for a balance of at least 10000, 2 and 4; for both filters, 2 alone:
-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- When the shape of a query is known only at run time. Part 1: a search with optional filters adds only the
-- filters the caller gives, so the number of placeholders varies; DBMS_SQL binds each by name, and
-- TO_REFCURSOR hands the result back as an ordinary ref cursor. Part 2: DESCRIBE_COLUMNS2 reads the select list
-- of a query the code has never seen.
SET SERVEROUTPUT ON FORMAT WRAPPED
CREATE OR REPLACE FUNCTION find_accounts (
p_city IN VARCHAR2 DEFAULT NULL,
p_min_balance IN NUMBER DEFAULT NULL
) RETURN SYS_REFCURSOR AUTHID DEFINER
IS
v_sql VARCHAR2(400) := 'SELECT a.account_id, c.city, a.balance FROM accounts a '
|| 'JOIN customers c ON c.customer_id = a.customer_id WHERE 1 = 1';
v_cur INTEGER;
v_done INTEGER;
BEGIN
-- Only fixed text is appended; each value gets a placeholder of its own.
IF p_city IS NOT NULL THEN
v_sql := v_sql || ' AND c.city = :city';
END IF;
IF p_min_balance IS NOT NULL THEN
v_sql := v_sql || ' AND a.balance >= :min_bal';
END IF;
v_sql := v_sql || ' ORDER BY a.account_id';
v_cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cur, v_sql, DBMS_SQL.NATIVE);
IF p_city IS NOT NULL THEN -- bind only what this text contains
DBMS_SQL.BIND_VARIABLE(v_cur, ':city', p_city);
END IF;
IF p_min_balance IS NOT NULL THEN
DBMS_SQL.BIND_VARIABLE(v_cur, ':min_bal', p_min_balance);
END IF;
v_done := DBMS_SQL.EXECUTE(v_cur);
RETURN DBMS_SQL.TO_REFCURSOR(v_cur); -- opened, parsed and executed: now a ref cursor
END;
/
DECLARE
v_rc SYS_REFCURSOR;
v_id accounts.account_id%TYPE;
v_city customers.city%TYPE;
v_balance accounts.balance%TYPE;
PROCEDURE show (p_label VARCHAR2, p_rc IN OUT SYS_REFCURSOR) IS
v_list VARCHAR2(100);
BEGIN
LOOP
FETCH p_rc INTO v_id, v_city, v_balance;
EXIT WHEN p_rc%NOTFOUND;
v_list := v_list || CASE WHEN v_list IS NOT NULL THEN ', ' END || v_id;
END LOOP;
CLOSE p_rc;
DBMS_OUTPUT.PUT_LINE(RPAD(p_label, 26) || 'accounts ' || NVL(v_list, 'none'));
END;
BEGIN
v_rc := find_accounts(); show('no filter', v_rc);
v_rc := find_accounts(p_city => 'Pune'); show('city Pune', v_rc);
v_rc := find_accounts(p_min_balance => 10000); show('balance at least 10000', v_rc);
v_rc := find_accounts('Pune', 10000); show('both filters', v_rc);
END;
/
-- Expected:
-- no filter accounts 1, 2, 3, 4
-- city Pune accounts 1, 2
-- balance at least 10000 accounts 2, 4
-- both filters accounts 2
-- Part 2: the select list of a query known only at run time.
DECLARE
v_cur INTEGER := DBMS_SQL.OPEN_CURSOR;
v_cols INTEGER;
v_desc DBMS_SQL.DESC_TAB2;
BEGIN
DBMS_SQL.PARSE(v_cur, 'SELECT sku, item_name, unit_price, on_hand FROM stock', DBMS_SQL.NATIVE);
DBMS_SQL.DESCRIBE_COLUMNS2(v_cur, v_cols, v_desc);
DBMS_OUTPUT.PUT_LINE(v_cols || ' columns');
FOR i IN 1 .. v_cols LOOP
DBMS_OUTPUT.PUT_LINE(' ' || i || ' ' || v_desc(i).col_name);
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_cur);
END;
/
-- Expected:
-- 4 columns
-- 1 SKU
-- 2 ITEM_NAME
-- 3 UNIT_PRICE
-- 4 ON_HAND"""find_accounts_dbms_sql.sql in the lab: SQL*Plus runs it as LAB on a freshly loaded practice schema."""
from oracle_session import Lab
with Lab() as lab:
lab.sqlplus("shape/find_accounts_dbms_sql.sql")Output
$ sqlplus -s lab@//localhost/FREEPDB1 @shape/find_accounts_dbms_sql.sql Function created. no filter accounts 1, 2, 3, 4 city Pune accounts 1, 2 balance at least 10000 accounts 2, 4 both filters accounts 2 PL/SQL procedure successfully completed. 4 columns 1 SKU 2 ITEM_NAME 3 UNIT_PRICE 4 ON_HAND PL/SQL procedure successfully completed.
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 find_accounts_dbms_sql_session.py
Native dynamic SQL is shorter and, by Oracle’s account, runs noticeably faster, so keep DBMS_SQL for binds or select
lists that truly vary, or for a procedure that returns its rows with DBMS_SQL.RETURN_RESULT. Whose tables a dynamic
statement can reach also depends on whose rights its unit runs with: a unit declared AUTHID CURRENT_USER resolves its
names with the caller’s rights, the subject of
definer’s rights, invoker’s rights and roles.
Checkpoint
Check: OPEN FOR and DBMS_SQL for a varying shape
Practise: choose the guard each statement needs
Each question shows a piece of dynamic SQL or a call to it. Decide what reaches the text, what should be bound and which tool fits, then read why each answer is right or wrong:
Practice that adapts to you
Practise this lesson
11 questions about 4 ideas of this lesson. Each answer is explained at once, and what you get right is kept in this browser.
Interview questions
Warm-up (fresher to 2–5 years): why is a bind variable safer than a value concatenated into dynamic SQL?
A concatenated value becomes part of the statement’s text, so the parser reads anything inside it, such as a quote
that ends a literal, as SQL. A bind variable keeps the text fixed, for example WHERE full_name = :n, and sends the
value separately with USING, so the database treats it only as data: no value can add a condition, and a name with
an apostrophe simply matches or does not. Oracle adds that binds also help performance. A bind cannot carry a table
or column name; those need an allowlist.
Two longer questions follow, on a report whose sort column comes from the caller and on a search with optional filters; work out your answer before you open its first step:
Model answers open with any pass: ₹79 for 1 week ($2.99), one payment that never renews.
-
Mid to senior
A report procedure takes p_sort_col from a web page and runs 'SELECT ... FROM accounts ORDER BY ' || p_sort_col. A teammate proposes wrapping p_sort_col in DBMS_ASSERT.SIMPLE_SQL_NAME. Is that enough, and how would you write the procedure?
-
Mid to staff
A search procedure takes four optional filters, city, kind, minimum balance and maximum balance, and concatenates the ones that are not NULL into a WHERE clause. How would you rewrite it so that every value is bound, and which PL/SQL tool fits?
Flashcards · 8 cards
Recall it, then check
Say the answer to yourself before you look: recalling it is what makes it stick.
-
Where do the values and the results of an EXECUTE IMMEDIATE statement go?
Values go in USING, one row comes back with INTO, many rows with BULK COLLECT INTO, and a changed row's values with RETURNING INTO when the text has a RETURNING clause. In a SQL statement the placeholders match the USING list by position, not by name.
-
What happens to a dynamic SELECT with a USING list but no INTO?
Oracle's reference says such a statement never runs, so no row is read. Add INTO for one row or BULK COLLECT INTO for many.
-
Which two EXECUTE IMMEDIATE rules catch people out?
USING does not accept the word NULL, so bind a variable that was never assigned. And DDL commits: the database commits the open transaction before and after every DDL statement, so a ROLLBACK after a dynamic CREATE TABLE undoes nothing.
-
What does Oracle trace every injection technique back to?
One weakness: string input that is not validated and is concatenated into a dynamic statement. The parser then reads whatever the input holds as SQL, so a test input matches every row and an ordinary name with an apostrophe fails to parse (ORA-03049).
-
Why are bind variables the most effective guard?
The database uses their values only as data and never interprets them, and the statement text stays the same on every call. Doubling quotes is easy to forget in one of many places, and refusing every apostrophe turns away real names without making the query safe.
-
What is the trap with a date pasted into dynamic SQL text?
It is converted to characters with the session's NLS date format, which the session itself can change. Bind the value, or convert it with an explicit format model such as TO_CHAR(d, 'YYYY-MM-DD') after the keyword DATE.
-
What do the DBMS_ASSERT checks do, and why is the allowlist still first?
SIMPLE_SQL_NAME checks a name's form, QUALIFIED_SQL_NAME a dotted name, SQL_OBJECT_NAME that an object exists, SCHEMA_NAME that a schema does, and ENQUOTE_NAME quotes a name. But a quoted name passes the form check and an existing table passes the object check without being a column the report should sort by, so Oracle says the package helps validation code and does not replace it.
-
When does native dynamic SQL stop being enough?
When the number and types of the inputs or outputs are not known at compile time, because EXECUTE IMMEDIATE and OPEN FOR take a USING list written when the code is compiled. Then DBMS_SQL parses, binds by name, executes and either fetches or hands back a ref cursor with TO_REFCURSOR; native dynamic SQL is shorter and runs noticeably faster.
Key takeaways
EXECUTE IMMEDIATEruns a statement built as text in one step: values go inUSING, one row comes back withINTO, many withBULK COLLECT INTO, and the changed row’s values withRETURNING INTO.- A value concatenated into the text can change the statement or break it, as the test input and the name Neha D’Souza show, while a bind variable keeps it a value.
- Names cannot be bound, so the caller’s choice is mapped through an allowlist the code holds and quoted with
ENQUOTE_NAME, with the otherDBMS_ASSERTchecks as a second guard. OPEN FORruns a many-row query held in a string, andDBMS_SQLhandles binds or columns known only at run time and hands back a ref cursor withTO_REFCURSOR.- DDL run through
EXECUTE IMMEDIATEcommits the open transaction, and static SQL is the better choice whenever the text is fixed.
Terms in this lesson
- bind variable
- A placeholder such as :id in a statement's text whose value is supplied separately when the statement runs, so the database uses the value only as data and never reads it as SQL.
- DBMS_ASSERT
- An Oracle-supplied package of functions that check or quote a piece of SQL text, such as SIMPLE_SQL_NAME and ENQUOTE_NAME, and raise an error when the input does not have the expected form.
- DBMS_SQL
- An Oracle-supplied package that runs dynamic SQL through a cursor number, with separate calls to parse, bind, execute and fetch, for statements whose binds or select list are known only at run time.
- native dynamic SQL
- PL/SQL's built-in way to run a statement whose text is built at run time: EXECUTE IMMEDIATE for most statements and OPEN FOR for queries, with values passed in a USING clause.
References
- PL/SQL Dynamic SQL (PL/SQL Language Reference) (Oracle)
- When You Need Dynamic SQL (Oracle)
- Native Dynamic SQL (Oracle)
- EXECUTE IMMEDIATE Statement (Oracle)
- SQL Injection (PL/SQL Language Reference) (Oracle)
- ORA-03049 (Database Error Messages) (Oracle)
- DBMS_ASSERT (PL/SQL Packages and Types Reference) (Oracle)
- DBMS_SQL Package (PL/SQL Language Reference) (Oracle)
- DBMS_SQL (PL/SQL Packages and Types Reference) (Oracle)
- Types of SQL Statements (SQL Language Reference) (Oracle)
- SQL Injection Prevention Cheat Sheet (OWASP)
- Input Validation Cheat Sheet (OWASP)
Report a problem with this lesson
Kept only in this browser. Your Learn progress