G-6030: Always verify parameters that will be used in dynamic SQL with DBMS_ASSERT
Major
Maintainability, Testability
Reason
Parameters used with dynamic sql are subject to SQL injection. The DBMS_ASSERT package provides an interface to validate properties of parameters.
Example (bad)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23 | procedure schedule_refresh_data_job (p_user varchar2) is
l_job_action varchar2(4000);
l_scope logger_logs.scope%type := gc_scope_prefix || 'Create refresh job';
begin
logger.log('START',l_scope);
l_job_action := 'begin hr.hr_utils.refresh_read_only_data_from_source; end;';
dbms_scheduler.create_job
(job_name => gk_on_demand_job_name
,job_type => 'PLSQL_BLOCK'
,job_action => l_job_action
,start_date => sysdate
,enabled => true
,auto_drop => true
,job_class => 'DEFAULT_JOB_CLASS'
,comments => 'Refresh data from Source to Destination by ' || p_user || ' created on ' || to_char(sysdate ,'mm/dd/yyyy hh:mi:ss am')
);
logger.log('END ',l_scope);
exception
when others then
logger.log_error('Unable to execute: '|| sqlerrm,l_scope);
raise_application_error(-20001,'Unable to create refresh job :'||sqlerrm);
end schedule_refresh_data_job;
|
Example (good)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23 | procedure schedule_refresh_data_job (p_user varchar2) is
l_job_action varchar2(4000);
l_scope logger_logs.scope%type := gc_scope_prefix || 'Create refresh job';
begin
logger.log('START',l_scope);
l_job_action := 'begin hr.hr_utils.refresh_read_only_data_from_source; end;';
dbms_scheduler.create_job
(job_name => gk_on_demand_job_name
,job_type => 'PLSQL_BLOCK'
,job_action => l_job_action
,start_date => sysdate
,enabled => true
,auto_drop => true
,job_class => 'DEFAULT_JOB_CLASS'
,comments => 'Refresh data from Source to Destination by ' || dbms_assert.simple_sql_name(p_user) || ' created on ' || to_char(sysdate ,'YYYY-MM-DD hh24:mi:ss')
);
logger.log('END ',l_scope);
exception
when others then
logger.log_error('Unable to execute: '|| sqlerrm,l_scope);
raise_application_error(-20001,'Unable to create refresh job :'||sqlerrm);
end schedule_refresh_data_job;
|