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;