oracle__skills/db/sql-dev/sql-injection-avoidance.md

SQL Injection Avoidance in Oracle

Overview

SQL injection is a code injection technique in which an attacker inserts malicious SQL fragments into a query by manipulating user-supplied input. In Oracle environments, injection can occur in both static application code and dynamic PL/SQL. The consequences range from unauthorized data access and data manipulation to privilege escalation and full database compromise.

Oracle provides a layered set of defenses: bind variables (the single most important protection), the DBMS_ASSERT package for whitelist validation, careful dynamic SQL construction, and application-level input validation. This guide covers each layer with concrete examples of vulnerable versus safe patterns.

---

The Root Cause: String Concatenation

SQL injection becomes possible when user-supplied values are concatenated directly into a SQL string rather than passed as bind variables. Oracle then parses the resulting string as SQL, making it impossible to distinguish intended structure from injected input.

Vulnerable Pattern

-- DANGEROUS: user input is concatenated directly into the query string
CREATE OR REPLACE PROCEDURE get_employee_unsafe (
  p_name IN VARCHAR2
) AS
  v_sql VARCHAR2(500);
  v_result employees%ROWTYPE;
BEGIN
  -- If p_name = ' OR 1=1 --', this returns ALL rows
  -- If p_name = 'Smith'' UNION SELECT username,password,null,null FROM dba_users--'
  -- this leaks the DBA user table
  v_sql := 'SELECT * FROM employees WHERE last_name = ''' || p_name || '''';
  EXECUTE IMMEDIATE v_sql INTO v_result;
END;
/

When an attacker passes ' OR '1'='1, the resulting SQL becomes:

SELECT * FROM employees WHERE last_name = '' OR '1'='1'

This returns every row in the table.

When an attacker passes ' UNION SELECT username, password, NULL, NULL FROM dba_users--, the resulting SQL leaks DBA credentials.

---

Bind Variables: The Primary Defense

Bind variables (also called bind parameters or parameterized queries) separate the SQL structure from its data values. The query is parsed once with placeholders; actual values are substituted at execution time. The database engine never re-parses the statement with injected content — the injected string is treated as a literal data value, not SQL syntax.

Safe Static SQL with Bind Variables

-- SAFE: bind variable prevents injection; also improves performance via plan reuse
CREATE OR REPLACE PROCEDURE get_employee_safe (
  p_name IN VARCHAR2
) AS
  v_result employees%ROWTYPE;
BEGIN
  SELECT *
  INTO   v_result
  FROM   employees
  WHERE  last_name = p_name;   -- p_name is a bind variable here

  DBMS_OUTPUT.PUT_LINE(v_result.first_name || ' ' || v_result.last_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employee found.');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Multiple employees found.');
END;
/

Safe Dynamic SQL with Bind Variables via EXECUTE IMMEDIATE

-- SAFE: EXECUTE IMMEDIATE with USING clause binds data separately from SQL structure
CREATE OR REPLACE PROCEDURE get_employee_dynamic_safe (
  p_name IN VARCHAR2
) AS
  v_sql       VARCHAR2(500);
  v_first     employees.first_name%TYPE;
  v_last      employees.last_name%TYPE;
BEGIN
  v_sql := 'SELECT first_name, last_name FROM employees WHERE last_name = :1';
  EXECUTE IMMEDIATE v_sql INTO v_first, v_last USING p_name;

  DBMS_OUTPUT.PUT_LINE(v_first || ' ' || v_last);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employee found.');
END;
/

Multiple Bind Variables

CREATE OR REPLACE PROCEDURE search_employees (
  p_dept_id  IN employees.department_id%TYPE,
  p_min_sal  IN employees.salary%TYPE,
  p_max_sal  IN employees.salary%TYPE
) AS
  TYPE emp_cursor IS REF CURSOR;
  v_cur    emp_cursor;
  v_sql    VARCHAR2(500);
  v_emp    employees%ROWTYPE;
BEGIN
  v_sql := 'SELECT * FROM employees '
        || 'WHERE department_id = :dept '
        || 'AND   salary BETWEEN :min_sal AND :max_sal '
        || 'ORDER BY last_name';

  OPEN v_cur FOR v_sql USING p_dept_id, p_min_sal, p_max_sal;
  LOOP
    FETCH v_cur INTO v_emp;
    EXIT WHEN v_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp.last_name || ': ' || v_emp.salary);
  END LOOP;
  CLOSE v_cur;
END;
/

---

DBMS_ASSERT: Whitelist Validation for Dynamic Structure

Bind variables protect data values but cannot protect dynamic SQL structure elements: table names, column names, schema names, and SQL keywords. These must be validated against a whitelist. Oracle's DBMS_ASSERT package provides built-in validators.

DBMS_ASSERT Functions

Function	What it validates
DBMS_ASSERT.SQL_OBJECT_NAME(str)	String is a valid, existing schema object name
DBMS_ASSERT.SIMPLE_SQL_NAME(str)	String matches the pattern of a simple SQL identifier (no quotes, spaces, or special chars)
DBMS_ASSERT.QUALIFIED_SQL_NAME(str)	String is a valid qualified name (e.g., schema.table)
DBMS_ASSERT.SCHEMA_NAME(str)	String is an existing schema name
DBMS_ASSERT.ENQUOTE_NAME(str)	Returns the string as a properly quoted identifier
DBMS_ASSERT.ENQUOTE_LITERAL(str)	Returns the string as a properly quoted SQL string literal (escapes single quotes)
DBMS_ASSERT.NOOP(str)	Returns the string unchanged — used as a documentation marker only, no validation

Validation functions raise specific errors on failure: SCHEMA_NAME raises ORA-44001, SQL_OBJECT_NAME raises ORA-44002, SIMPLE_SQL_NAME raises ORA-44003, and QUALIFIED_SQL_NAME raises ORA-44004.

Dynamic Table Name — Vulnerable vs. Safe

-- DANGEROUS: an attacker can pass 'employees WHERE 1=1 UNION SELECT ...'
CREATE OR REPLACE PROCEDURE count_rows_unsafe (
  p_table_name IN VARCHAR2
) AS
  v_count NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || p_table_name
    INTO v_count;
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;
/

-- SAFE: DBMS_ASSERT.SQL_OBJECT_NAME raises an error if the table doesn't exist
-- and rejects any string containing SQL syntax characters
CREATE OR REPLACE PROCEDURE count_rows_safe (
  p_table_name IN VARCHAR2
) AS
  v_count      NUMBER;
  v_safe_name  VARCHAR2(128);
BEGIN
  v_safe_name := DBMS_ASSERT.SQL_OBJECT_NAME(p_table_name);
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_safe_name
    INTO v_count;
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE IN (-44001, -44002, -44003, -44004) THEN
      RAISE_APPLICATION_ERROR(-20001, 'Invalid table name provided.');
    ELSE
      RAISE;
    END IF;
END;
/

Dynamic Column Name with SIMPLE_SQL_NAME

-- SAFE: validates that the column name is a simple identifier
CREATE OR REPLACE PROCEDURE get_column_value (
  p_table_name  IN VARCHAR2,
  p_column_name IN VARCHAR2,
  p_row_id      IN NUMBER
) AS
  v_sql         VARCHAR2(500);
  v_value       VARCHAR2(4000);
  v_safe_table  VARCHAR2(128);
  v_safe_col    VARCHAR2(128);
BEGIN
  v_safe_table := DBMS_ASSERT.SQL_OBJECT_NAME(p_table_name);
  v_safe_col   := DBMS_ASSERT.SIMPLE_SQL_NAME(p_column_name);

  v_sql := 'SELECT TO_CHAR(' || v_safe_col || ') FROM ' || v_safe_table
        || ' WHERE id = :1';

  EXECUTE IMMEDIATE v_sql INTO v_value USING p_row_id;
  DBMS_OUTPUT.PUT_LINE(v_value);
END;
/

ENQUOTE_LITERAL for String Values in Dynamic SQL

When you genuinely cannot use a bind variable (e.g., inside a DDL statement), use DBMS_ASSERT.ENQUOTE_LITERAL to safely quote a string value:

-- Properly escapes embedded single quotes and wraps in quotes
DECLARE
  v_input    VARCHAR2(100) := 'O''Brien';   -- contains a single quote
  v_safe_lit VARCHAR2(200);
  v_sql      VARCHAR2(500);
BEGIN
  v_safe_lit := DBMS_ASSERT.ENQUOTE_LITERAL(v_input);
  -- v_safe_lit is now: 'O''Brien'  (properly escaped)

  v_sql := 'INSERT INTO audit_log(name) VALUES(' || v_safe_lit || ')';
  EXECUTE IMMEDIATE v_sql;
END;
/

---

Dangerous Patterns and Their Safe Replacements

Pattern 1: Dynamic WHERE clause construction from a list

-- DANGEROUS: building IN list from concatenated input
-- Input: '1,2,3' becomes WHERE id IN (1,2,3) -- safe-looking but fragile
-- Input: '1,2) OR (1=1' becomes WHERE id IN (1,2) OR (1=1) -- injection!
v_sql := 'SELECT * FROM orders WHERE customer_id IN (' || p_id_list || ')';

-- SAFE: use a collection and TABLE() operator
CREATE OR REPLACE TYPE number_list AS TABLE OF NUMBER;
/

CREATE OR REPLACE PROCEDURE get_orders_by_ids (
  p_ids IN number_list
) AS
  TYPE order_cur IS REF CURSOR;
  v_cur order_cur;
BEGIN
  OPEN v_cur FOR
    SELECT * FROM orders
    WHERE  customer_id IN (SELECT COLUMN_VALUE FROM TABLE(p_ids));
  -- process cursor...
  CLOSE v_cur;
END;
/

Pattern 2: Dynamic ORDER BY column

-- DANGEROUS: ORDER BY from user input
v_sql := 'SELECT * FROM employees ORDER BY ' || p_sort_col;

-- SAFE: whitelist against known column names
CREATE OR REPLACE PROCEDURE get_employees_sorted (
  p_sort_col IN VARCHAR2
) AS
  v_safe_col VARCHAR2(30);
  v_sql      VARCHAR2(500);
BEGIN
  -- Explicit whitelist; reject anything not in the list
  v_safe_col := CASE p_sort_col
                  WHEN 'last_name'   THEN 'last_name'
                  WHEN 'salary'      THEN 'salary'
                  WHEN 'hire_date'   THEN 'hire_date'
                  WHEN 'employee_id' THEN 'employee_id'
                  ELSE NULL
                END;

  IF v_safe_col IS NULL THEN
    RAISE_APPLICATION_ERROR(-20002, 'Invalid sort column: ' || p_sort_col);
  END IF;

  v_sql := 'SELECT * FROM employees ORDER BY ' || v_safe_col;
  EXECUTE IMMEDIATE v_sql;
END;
/

Pattern 3: Login / authentication query

-- DANGEROUS: classic login bypass
-- Input username: admin' --
-- Results in: WHERE username = 'admin' --' AND password = '...'
-- The password check is commented out!
v_sql := 'SELECT COUNT(*) FROM users WHERE username = ''' || p_user
      || ''' AND password = ''' || p_pass || '''';

-- SAFE: use bind variables; the injected quote is treated as data
SELECT COUNT(*)
INTO   v_count
FROM   users
WHERE  username = p_user
AND    password = STANDARD_HASH(p_pass, 'SHA256');  -- also: don't store plaintext passwords

Pattern 4: DBMS_SQL with dynamic bind variables (truly dynamic column count)

-- SAFE: DBMS_SQL allows dynamic bind variable count at runtime
-- Use when the number of bind variables isn't known until runtime
CREATE OR REPLACE PROCEDURE flexible_search (
  p_conditions IN SYS.ODCIVARCHAR2LIST,  -- array of 'column_name=value' pairs
  p_values     IN SYS.ODCIVARCHAR2LIST
) AS
  v_cursor  INTEGER;
  v_sql     VARCHAR2(4000) := 'SELECT employee_id, last_name FROM employees WHERE 1=1';
  v_rows    INTEGER;
BEGIN
  -- Build WHERE clause with bind placeholders (never concatenate values)
  FOR i IN 1..p_conditions.COUNT LOOP
    -- Validate column name against whitelist before appending
    v_sql := v_sql || ' AND '
          || DBMS_ASSERT.SIMPLE_SQL_NAME(p_conditions(i))
          || ' = :b' || i;
  END LOOP;

  v_cursor := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);

  FOR i IN 1..p_values.COUNT LOOP
    DBMS_SQL.BIND_VARIABLE(v_cursor, ':b' || i, p_values(i));
  END LOOP;

  v_rows := DBMS_SQL.EXECUTE(v_cursor);
  DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;
/

---

Input Validation Layers

Defense in depth means applying validation at multiple layers. Bind variables handle data values; these techniques handle everything else.

Application Tier Validation

• Validate data types before sending to the database (ensure numeric inputs are actually numeric).
• Enforce length limits; a surname field should never accept 4,000 characters.
• Use an allowlist (permitted characters) rather than a blocklist (forbidden characters). Blocklists always miss edge cases.
• Reject null bytes (chr(0)), escape sequences, and Unicode normalization tricks.

PL/SQL Tier Validation

-- Utility: validate that input is a positive integer
CREATE OR REPLACE FUNCTION is_positive_integer (p_input IN VARCHAR2)
RETURN BOOLEAN AS
  v_num NUMBER;
BEGIN
  v_num := TO_NUMBER(p_input);
  RETURN v_num = TRUNC(v_num) AND v_num > 0;
EXCEPTION
  WHEN VALUE_ERROR THEN
    RETURN FALSE;
END;
/

-- Utility: strip non-alphanumeric characters (use with caution — prefer whitelisting)
CREATE OR REPLACE FUNCTION sanitize_alphanumeric (p_input IN VARCHAR2)
RETURN VARCHAR2 AS
BEGIN
  RETURN REGEXP_REPLACE(p_input, '[^A-Za-z0-9 _-]', '');
END;
/

Least Privilege at the Database Level

Even if injection occurs, a least-privilege schema limits the blast radius:

-- Application schema should have only the minimum required privileges
GRANT SELECT, INSERT, UPDATE ON hr.employees TO app_user;
-- Never GRANT DBA or GRANT ANY PRIVILEGE to application accounts

-- Use a dedicated low-privilege schema for application connections
-- Never connect as SYS or SYSTEM from application code

---

Best Practices Summary

• Always use bind variables for data values in both static and dynamic SQL. This is the single most effective defense.
• Validate dynamic identifiers (table/column names) with DBMS_ASSERT functions or explicit whitelists before interpolating into SQL strings.
• Never build SQL from unvalidated input of any kind, even from internal sources — insider threat and compromised middleware are real.
• Apply least privilege to database accounts used by applications. An account with only SELECT on specific tables cannot drop tables even if injected SQL runs.
• Log and alert on DBMS_ASSERT exceptions. Repeated assertion failures are a strong signal of active probing.
• Audit sensitive queries using Fine-Grained Auditing (FGA) so injection attempts leave a trace.
• Use connection pooling with application-specific schemas, not DBA-level credentials.

---

Common Mistakes

Mistake	Why It's Dangerous	Fix
Concatenating numeric input directly	Numbers can still carry injection: 1 UNION SELECT...	Validate type with TO_NUMBER(), then use bind variable
Trusting escaping/encoding	Encoding tricks (URL encoding, multi-byte characters) can bypass simple escaping	Use bind variables; escaping is not a substitute
Using DBMS_ASSERT.NOOP thinking it validates	NOOP does nothing — it is purely a documentation marker	Use SIMPLE_SQL_NAME or SQL_OBJECT_NAME
Whitelisting only in the UI layer	UI can be bypassed; direct API/DB access skips UI entirely	Always validate at the PL/SQL/database layer
Dynamic DDL from user input	CREATE TABLE, DROP TABLE, GRANT in dynamic SQL is very dangerous	Never allow user input to influence DDL structure
Storing credentials in source code	Credentials in code become injection entry points if code is exposed	Use Oracle Wallet or secret management tools

---

Oracle-Specific Notes

• Oracle's Fine-Grained Auditing (FGA) via DBMS_FGA.ADD_POLICY can alert when sensitive columns are accessed, providing detection capability alongside prevention.
• Virtual Private Database (VPD) adds predicates to queries automatically based on application context, limiting data exposure even if a query is injected.
• Oracle Label Security and Database Vault provide additional controls for environments with strict data segregation requirements.
• The CURSOR_SHARING = FORCE parameter can force bind variable use system-wide at the cost of some optimizer accuracy — it is a last resort, not a substitute for proper bind variable use in application code.
• Oracle SQL Firewall (21c+) can whitelist exactly which SQL statements are permitted from each database account, blocking any SQL that doesn't match the learned baseline.

---

Oracle Version Notes (19c vs 26ai)

• Baseline guidance in this file is valid for Oracle Database 19c unless a newer minimum version is explicitly called out.
• Features marked as 21c, 23c, or 23ai should be treated as Oracle Database 26ai-capable features; keep 19c-compatible alternatives for mixed-version estates.
• For dual-support environments, test syntax and package behavior in both 19c and 26ai because defaults and deprecations can differ by release update.

Sources

• Oracle Database 19c SQL Language Reference (SQLRF)
• DBMS_ASSERT — Oracle Database 19c PL/SQL Packages and Types Reference
• DBMS_SQL — Oracle Database 19c PL/SQL Packages and Types Reference
• Oracle Database Security Guide 19c