G-9501
Never use parameter in string expression of dynamic SQL. Use asserted local variable instead.
Reason
The use of static SQL eliminates the risk of SQL injection. However, if you write dynamic SQL you are responsible to ensure that the SQL cannot be injected with malicious SQL statements.
This check looks for unasserted parameters used in execute immediate statements and open for statements. All parameters used in these statements must be asserted with one of the subprograms provided by dbms_assert.
Example
Non-Compliant Example
create or replace package body pkg is
function f (in_table_name in varchar2) return boolean as
co_templ constant varchar2(4000 byte) := 'DROP TABLE #in_table_name# PURGE';
l_table_name varchar2(128 byte);
l_sql varchar2(4000 byte);
begin
l_table_name := in_table_name;
l_sql := replace(co_templ, '#in_table_name#', l_table_name);
execute immediate l_sql;
return true;
end f;
end pkg;
/Issues
| Line | Column | Message |
|---|---|---|
| 7 | 25 | SQL injection possible via parameter in_table_name. |
The input parameter in_table_name is copied to the local variable l_table_name and then used without an assert to build the l_sql variable. Hence, the execute immediate statement is considered vulnerable to SQL injection, e.g. by passing DEPT CASCADE CONSTRAINTS.
Compliant Solution - ★★★★★
create or replace package body pkg is
function f (in_table_name in varchar2) return boolean as
co_templ constant varchar2(4000 byte) := 'DROP TABLE #in_table_name# PURGE';
l_table_name varchar2(128 byte);
l_sql varchar2(4000 byte);
begin
l_table_name := sys.dbms_assert.enquote_name(in_table_name);
l_sql := replace(co_templ, '#in_table_name#', l_table_name);
execute immediate l_sql;
return true;
end f;
end pkg;
/SQL injection is not possible, because the input parameter in_table_name is checked/modified with sys.dbms_assert.enquote_name.
Parameters
Use parameters to customize the rule to your needs.
| Parameter | Description | Default Value |
|---|---|---|
| AssertPackages | Comma-separated list of PL/SQL packages that contain functions to assert a parameter regarding SQL injection. | dbms_assert, ut_utils |
| SchemaNames | Comma-separated list of database schemas owning the database objects of an application. | dbl_owner |
References
- similar to plsql:S1523
The scope of plsql:S1523 is the dynamic SQL statements. It cannot detect SQL injection vulnerabilities. It suggest to review every dynamically executed SQL statement.