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; |