How to Prevent SQL Injection with DBMS_ASSERT in PL/SQL

Dynamic SQL built by concatenating user input is the classic way to let an attacker rewrite your query. The first defense is bind variables, which keep input as a value. But table and column names cannot be bound, so code that builds names from input needs a second defense: DBMS_ASSERT, a package of functions that check names and quote values before they go into SQL text.

Code for This Guide

The main examples are in the examples/dynamic-bulk folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Injection, Binding, and Checking Names

The input DXB' or '1'='1 is concatenated into a query for one airport's routes, then passed as a bind variable, then a name containing extra text is checked with DBMS_ASSERT.

Example:

declare
  v_input  varchar2(100) := 'DXB'' or ''1''=''1';     -- a malicious value from a form
  v_count  number;
begin
  -- concatenated: the input becomes part of the SQL text
  execute immediate 'select count(*) from routes where origin = ''' || v_input || ''''
    into v_count;
  dbms_output.put_line('concatenated: ' || v_count || ' routes');
  -- bound: the input is only ever a value
  execute immediate 'select count(*) from routes where origin = :o'
    into v_count using v_input;
  dbms_output.put_line('bound: ' || v_count || ' routes');
  -- names can't be bound: check them
  execute immediate 'select count(*) from ' || dbms_assert.simple_sql_name('routes; drop')
    into v_count;
end;
/

Output:

concatenated: 50 routes
bound: 0 routes

declare
*
ERROR at line 1:
ORA-44003: invalid SQL name
ORA-06512: at "SYS.DBMS_ASSERT", line 192
ORA-06512: at line 14

Concatenated, the input turns the query into one that matches every route, returning 50. Bound, it is just a value that matches nothing. And SIMPLE_SQL_NAME rejects 'routes; drop' with ORA-44003 before it reaches the SQL text.

The DBMS_ASSERT Functions

Example:

select dbms_assert.enquote_literal('Dubai')           as literal,
       dbms_assert.enquote_name('flights')            as quoted_name,
       dbms_assert.sql_object_name('NIMBUS.ROUTES')   as existing_object,
       dbms_assert.schema_name('NIMBUS')              as existing_schema
from   dual;

-- an object that does not exist
select dbms_assert.sql_object_name('NO_SUCH_TABLE') as missing from dual;

-- ENQUOTE_LITERAL refuses a value with an unpaired quote inside
select dbms_assert.enquote_literal('O''Hare') as with_quote from dual;

Output:

LITERAL    QUOTED_NAME    EXISTING_OBJECT    EXISTING_SCHEMA
__________ ______________ __________________ __________________
'Dubai'    "FLIGHTS"      NIMBUS.ROUTES      NIMBUS

Error starting at line : 8
In command -
select dbms_assert.sql_object_name('NO_SUCH_TABLE') as missing from dual
Error at Command Line : 8 Column : 8
Error report -
SQL Error: ORA-44002: invalid object name
ORA-06512: at "SYS.DBMS_ASSERT", line 452
ORA-06512: at "SYS.DBMS_ASSERT", line 447

Error starting at line : 11
In command -
select dbms_assert.enquote_literal('O''Hare') as with_quote from dual
Error at Command Line : 11 Column : 8
Error report -
SQL Error: ORA-06502: PL/SQL: value or conversion error
ORA-06512: at "SYS.DBMS_ASSERT", line 473
ORA-06512: at "SYS.DBMS_ASSERT", line 563

A name that is not an existing object raises ORA-44002. ENQUOTE_LITERAL refuses a value with an unpaired single quote such as O'Hare; values belong in bind variables anyway, and literals only when there is truly no alternative.

Rules for Safe Dynamic SQL

• Pass every value as a bind variable; never concatenate input into SQL text.
• Validate every name that comes from outside with SIMPLE_SQL_NAME or SQL_OBJECT_NAME, or better, check it against a fixed list or the data dictionary.
• Prefer static SQL whenever the statement does not really change.

Related Guides

• Oracle Dynamic SQL Example to Insert a Record Using DBMS_SQL
• How to Return Implicit Results from PL/SQL

Conclusion

Bind variables keep input as values and stop SQL injection for data; DBMS_ASSERT protects the parts that cannot be bound, by validating names and quoting literals before they go into dynamic SQL. Use both whenever SQL text is built at run time.