Skip to main content

Parameters should not be concatenated into dynamic SQL

ZPA rule for PL/SQL and Oracle SQL · ParameterInDynamicSql

A parameter concatenated into the text of a dynamic SQL statement (EXECUTE IMMEDIATE, OPEN … FOR, DBMS_SQL.PARSE) lets whoever calls the subprogram write part of the statement. A value with a quote changes it: x' or '1'='1 turns a lookup of one row into a lookup of all rows (SQL injection).

Pass a value as a bind variable with USING: Oracle never reads it as part of the statement. A name, such as a table or a column, cannot be bound: check it with DBMS_ASSERT (SIMPLE_SQL_NAME, ENQUOTE_NAME), which raises an error for anything that is not a name. DBMS_ASSERT.NOOP checks nothing.

This rule reports a character parameter written directly into the text: in the statement's expression, in a local variable that is assigned once and then run, or as the whole statement. A parameter inside another function or expression is not reported.

Examples​

Noncompliant Code Example
create procedure find_crate(p_code varchar2, p_table varchar2) is
n number;
begin
execute immediate 'select count(*) from crate where code = ''' || p_code || '''' into n; -- Noncompliant
execute immediate 'select count(*) from ' || p_table into n; -- Noncompliant
end;
Compliant Solution
create procedure find_crate(p_code varchar2, p_table varchar2) is
n number;
begin
execute immediate 'select count(*) from crate where code = :c' into n using p_code;
execute immediate 'select count(*) from ' || dbms_assert.simple_sql_name(p_table) into n;
end;