Update to code to address sql injection concerns.

Dealing with precedence. Adding some more tests.
utPLSQL · jgebal · Mar 2, 2022 · Feb 9, 2022 · Feb 22, 2022 · Feb 23, 2022
commit 4eec03e223c942471112ee9e68303b962e0cd25d
diff --git a/source/core/coverage/ut_coverage.pkb b/source/core/coverage/ut_coverage.pkb
@@ -133,7 +133,7 @@ create or replace package body ut_coverage is
     end if;    
 
     if a_coverage_options.exclude_object_expr is not null then
-       l_regex_exc_filters := l_regex_exc_filters||q'[ and not regexp_like(s.name,:a_exclude_obj_expr:,'i')]';   
+       l_regex_exc_filters := l_regex_exc_filters||q'[ and not regexp_like(s.name,:a_exclude_obj_expr,'i')]';   
     else
        l_regex_exc_filters := l_regex_exc_filters||'and :a_exclude_obj_expr is null '; 
     end if; 
@@ -147,7 +147,7 @@ create or replace package body ut_coverage is
     l_result := replace(l_result, '{mappings_cardinality}', l_mappings_cardinality);
     l_result := replace(l_result, '{skipped_objects_cardinality}', ut_utils.scale_cardinality(cardinality(a_skip_objects)));
     l_result := replace(l_result, '{regex_exc_filters}', l_regex_exc_filters);
-    
+
     return l_result;
 
   end;
@@ -159,7 +159,11 @@ create or replace package body ut_coverage is
   begin
     if not is_develop_mode() then
       --skip all the utplsql framework objects and all the unit test packages that could potentially be reported by coverage.
-      l_skip_objects := ut_utils.get_utplsql_objects_list() multiset union all coalesce(a_coverage_options.exclude_objects, ut_object_names());
+      l_skip_objects := coalesce(ut_utils.get_utplsql_objects_list(),ut_object_names());
+      --Regex exclusion override the standard exclusion objects.
+      if a_coverage_options.exclude_schema_expr is null and a_coverage_options.exclude_object_expr is null then
+        l_skip_objects := l_skip_objects multiset union all coalesce(a_coverage_options.exclude_objects, ut_object_names());
+      end if;  
     end if;
 
     l_sql := get_cov_sources_sql(a_coverage_options, l_skip_objects);
@@ -168,12 +172,12 @@ create or replace package body ut_coverage is
 
     if a_coverage_options.file_mappings is not empty then
       open l_cursor for l_sql using a_coverage_options.file_mappings,a_coverage_options.exclude_schema_expr,a_coverage_options.exclude_object_expr,l_skip_objects;
-    elsif a_coverage_options.include_objects is not empty then
-      open l_cursor for l_sql using a_coverage_options.include_objects,a_coverage_options.exclude_schema_expr,a_coverage_options.exclude_object_expr,l_skip_objects;
     elsif a_coverage_options.include_schema_expr is not null or a_coverage_options.include_object_expr is not null then
       open l_cursor for l_sql using a_coverage_options.include_schema_expr,a_coverage_options.include_object_expr,
                                     a_coverage_options.exclude_schema_expr,a_coverage_options.exclude_object_expr,
                                     l_skip_objects;
+    elsif a_coverage_options.include_objects is not empty then
+      open l_cursor for l_sql using a_coverage_options.include_objects,a_coverage_options.exclude_schema_expr,a_coverage_options.exclude_object_expr,l_skip_objects;                                    
     else
       open l_cursor for l_sql using a_coverage_options.schema_names,a_coverage_options.exclude_schema_expr,a_coverage_options.exclude_object_expr,l_skip_objects;
     end if;

diff --git a/test/ut3_tester_helper/coverage_helper.pkb b/test/ut3_tester_helper/coverage_helper.pkb
@@ -158,14 +158,14 @@ create or replace package body coverage_helper is
 
   end;
 
-  procedure create_regex_dummy_cov is
+  procedure create_regex_dummy_for_schema(p_schema in varchar2) is
     pragma autonomous_transaction;
   begin
-    execute immediate q'[create or replace package ut3_develop.regex_dummy_cov is
+    execute immediate q'[create or replace package ]'||p_schema||q'[.regex_dummy_cov is
       procedure do_stuff(i_input in number);
     end;]';
 
-    execute immediate q'[create or replace package body ut3_develop.regex_dummy_cov is
+    execute immediate q'[create or replace package body ]'||p_schema||q'[.regex_dummy_cov is
       procedure do_stuff(i_input in number) is
       begin
         if i_input = 2 then dbms_output.put_line('should not get here'); elsif i_input = 1 then dbms_output.put_line('should get here');
@@ -175,39 +175,38 @@ create or replace package body coverage_helper is
       end;
     end;]';
 
-    execute immediate q'[create or replace package ut3_develop.test_regex_dummy_cov is
+    execute immediate q'[create or replace package ]'||p_schema||q'[.test_regex_dummy_cov is
       --%suite(dummy coverage test)
       --%suitepath(coverage_testing)
 
       --%test
       procedure test_do_stuff;
 
+      --%test
+      procedure zero_coverage;
     end;]';
 
-    execute immediate q'[create or replace package body ut3_develop.test_regex_dummy_cov is
+    execute immediate q'[create or replace package body ]'||p_schema||q'[.test_regex_dummy_cov is
       procedure test_do_stuff is
       begin
         regex_dummy_cov.do_stuff(1);
         ut.expect(1).to_equal(1);
       end;
+      procedure zero_coverage is
+      begin
+        null;
+      end;
     end;]';
   end;
-
-  procedure drop_regex_dummy_cov is
-    pragma autonomous_transaction;
-  begin
-    begin execute immediate q'[drop package ut3_develop.regex_dummy_cov]'; exception when others then null; end;
-    begin execute immediate q'[drop package ut3_develop.test_regex_dummy_cov]'; exception when others then null; end;
-  end; 
 
-  procedure create_regex_dummy_for_schema(p_schema in varchar2) is
+  procedure create_regex_dummy_obj is
     pragma autonomous_transaction;
   begin
-    execute immediate q'[create or replace package ]'||p_schema||q'[.regex_dummy_cov_schema is
+    execute immediate q'[create or replace package ut3_develop.regex123_dummy_cov is
       procedure do_stuff(i_input in number);
     end;]';
 
-    execute immediate q'[create or replace package body ]'||p_schema||q'[.regex_dummy_cov_schema is
+    execute immediate q'[create or replace package body ut3_develop.regex123_dummy_cov is
       procedure do_stuff(i_input in number) is
       begin
         if i_input = 2 then dbms_output.put_line('should not get here'); elsif i_input = 1 then dbms_output.put_line('should get here');
@@ -217,7 +216,7 @@ create or replace package body coverage_helper is
       end;
     end;]';
 
-    execute immediate q'[create or replace package ]'||p_schema||q'[.test_regex_dummy_cov_schema is
+    execute immediate q'[create or replace package ut3_develop.test_regex123_dummy_cov is
       --%suite(dummy coverage test)
       --%suitepath(coverage_testing)
 
@@ -228,10 +227,10 @@ create or replace package body coverage_helper is
       procedure zero_coverage;
     end;]';
 
-    execute immediate q'[create or replace package body ]'||p_schema||q'[.test_regex_dummy_cov_schema is
+    execute immediate q'[create or replace package body ut3_develop.test_regex123_dummy_cov is
       procedure test_do_stuff is
       begin
-        regex_dummy_cov_schema.do_stuff(1);
+        regex123_dummy_cov.do_stuff(1);
         ut.expect(1).to_equal(1);
       end;
       procedure zero_coverage is
@@ -240,20 +239,23 @@ create or replace package body coverage_helper is
       end;
     end;]';
   end;
-  
-  procedure create_regex_dummy_cov_schema is
+
+  procedure create_regex_dummy_cov is
   begin
     create_regex_dummy_for_schema('ut3_develop');
     create_regex_dummy_for_schema('ut3_tester_helper');
+    create_regex_dummy_obj;
   end;
 
-  procedure drop_regex_dummy_cov_schema is
+  procedure drop_regex_dummy_cov is
     pragma autonomous_transaction;
   begin
-    begin execute immediate q'[drop package ut3_develop.regex_dummy_cov_schema]'; exception when others then null; end;
-    begin execute immediate q'[drop package ut3_develop.test_regex_dummy_cov_schema]'; exception when others then null; end;
-    begin execute immediate q'[drop package ut3_tester_helper.regex_dummy_cov_schema]'; exception when others then null; end;
-    begin execute immediate q'[drop package ut3_tester_helper.test_regex_dummy_cov_schema]'; exception when others then null; end;    
+    begin execute immediate q'[drop package ut3_develop.regex_dummy_cov]'; exception when others then null; end;
+    begin execute immediate q'[drop package ut3_develop.test_regex_dummy_cov]'; exception when others then null; end;
+    begin execute immediate q'[drop package ut3_tester_helper.regex_dummy_cov]'; exception when others then null; end;
+    begin execute immediate q'[drop package ut3_tester_helper.test_regex_dummy_cov]'; exception when others then null; end;
+    begin execute immediate q'[drop package ut3_develop.regex123_dummy_cov]'; exception when others then null; end;
+    begin execute immediate q'[drop package ut3_develop.test_regex123_dummy_cov]'; exception when others then null; end;        
   end;
 
 

diff --git a/test/ut3_tester_helper/coverage_helper.pks b/test/ut3_tester_helper/coverage_helper.pks
@@ -17,9 +17,6 @@ create or replace package coverage_helper is
   procedure create_regex_dummy_cov;
   procedure drop_regex_dummy_cov;
 
-  procedure create_regex_dummy_cov_schema;
-  procedure drop_regex_dummy_cov_schema;
-
   procedure create_cov_with_dbms_stats;
   procedure drop_cov_with_dbms_stats;
 

diff --git a/test/ut3_user/reporters/test_coverage/test_extended_coverage.pkb b/test/ut3_user/reporters/test_coverage/test_extended_coverage.pkb
@@ -107,74 +107,111 @@ create or replace package body test_extended_coverage is
   end;
 
   procedure coverage_regex_include_schema is
-    l_expected_ut3   clob;
-    l_expected_help  clob;  
-    l_actual_ut3     clob;
-    l_actual_help    clob;   
-    l_actual_both    clob;      
-
+    l_expected   clob;
+    l_not_expected  clob;  
+    l_actual     clob;
   begin
     --Arrange
-    l_expected_ut3 := '%<file path="package body ut3_develop.test_regex_dummy_cov_schema">' ||
+    l_expected := '%<file path="package body ut3_develop.regex_dummy_cov">' ||
       '%<lineToCover lineNumber="4" covered="true"/>%';
-    l_expected_help := '%<file path="package body ut3_tester_helper.test_regex_dummy_cov_schema">' ||
+    l_not_expected := '%<file path="package body ut3_tester_helper.regex_dummy_cov">' ||
       '%<lineToCover lineNumber="4" covered="true"/>%';            
     --Act
-    l_actual_ut3 :=
+    l_actual :=
       ut3_tester_helper.coverage_helper.run_tests_as_job(
         q'[
             ut3_develop.ut.run(
-              a_paths => ut3_develop.ut_varchar2_list('ut3_develop.test_regex_dummy_cov_schema', 'ut3_tester_helper.test_regex_dummy_cov_schema'),
+              a_paths => ut3_develop.ut_varchar2_list('ut3_develop.test_regex_dummy_cov', 'ut3_tester_helper.test_regex_dummy_cov'),
               a_reporter=> ut3_develop.ut_coverage_sonar_reporter( ),
-              a_include_schema_expr => '^ut3_develop'
+              a_include_schema_expr => '^ut3_develop',
+              a_include_objects => ut3_develop.ut_varchar2_list( 'ut3_tester_helper.regex_dummy_cov' )
             )
           ]'
-        );
-    /*
-    l_actual_help :=
+        );          
+    --Assert
+    ut.expect(l_actual).to_be_like(l_expected);
+    ut.expect(l_actual).not_to_be_like(l_not_expected);
+  end;
+
+  procedure coverage_regex_include_object is
+    l_expected   clob;
+    l_not_expected  clob;  
+    l_actual     clob;
+  begin
+    --Arrange
+    l_expected := '%<file path="package body ut3_develop.regex123_dummy_cov">' ||
+      '%<lineToCover lineNumber="4" covered="true"/>%';
+    l_not_expected := '%<file path="package body ut3_tester_helper.regex_dummy_cov">' ||
+      '%<lineToCover lineNumber="4" covered="true"/>%';            
+    --Act
+    l_actual :=
       ut3_tester_helper.coverage_helper.run_tests_as_job(
         q'[
             ut3_develop.ut.run(
-              a_paths => ut3_develop.ut_varchar2_list('ut3_develop.test_regex_dummy_cov_schema', 'ut3_tester_helper.test_regex_dummy_cov_schema'),
+              a_paths => ut3_develop.ut_varchar2_list('ut3_develop.test_regex_dummy_cov', 'ut3_develop.test_regex123_dummy_cov'),
               a_reporter=> ut3_develop.ut_coverage_sonar_reporter( ),
-              a_include_schema_expr => '^ut3_tester_helper'
+              a_include_object_expr => 'regex123',
+              a_include_objects => ut3_develop.ut_varchar2_list( 'ut3_develop.regex_dummy_cov' )       
             )
           ]'
-        );  
+        );          
+    --Assert
+    ut.expect(l_actual).to_be_like(l_expected);
+    ut.expect(l_actual).not_to_be_like(l_not_expected);  
+  end;
 
-    l_actual_both :=
+  procedure coverage_regex_exclude_schema is
+    l_expected   clob;
+    l_not_expected  clob;  
+    l_actual     clob;
+  begin
+    --Arrange
+    l_expected := '%<file path="package body ut3_develop.regex_dummy_cov">' ||
+      '%<lineToCover lineNumber="4" covered="true"/>%';
+    l_not_expected := '%<file path="package body ut3_tester_helper.regex_dummy_cov">' ||
+      '%<lineToCover lineNumber="4" covered="true"/>%';            
+    --Act
+    l_actual :=
       ut3_tester_helper.coverage_helper.run_tests_as_job(
         q'[
             ut3_develop.ut.run(
-              a_paths => ut3_develop.ut_varchar2_list('ut3_develop.test_regex_dummy_cov_schema', 'ut3_tester_helper.test_regex_dummy_cov_schema'),
+              a_paths => ut3_develop.ut_varchar2_list('ut3_develop.test_regex_dummy_cov', 'ut3_tester_helper.test_regex_dummy_cov'),
               a_reporter=> ut3_develop.ut_coverage_sonar_reporter( ),
-              a_include_schema_expr => '^ut3_tester_helper||^ut3_tester_helper'
+              a_exclude_schema_expr => '^ut3_tester',
+              a_exclude_objects => ut3_develop.ut_varchar2_list( 'ut3_develop.regex_dummy_cov' )
             )
           ]'
-        ); 
-    */              
+        );          
     --Assert
-    ut.expect(l_actual_ut3).to_be_like(l_expected_ut3);
-    ut.expect(l_actual_ut3).not_to_be_like(l_expected_help);
-    --ut.expect(l_actual_help).to_be_like(l_expected_help);
-    --ut.expect(l_actual_help).not_to_be_like(l_expected_ut3);
-    --ut.expect(l_actual_both).to_be_like(l_expected_ut3);
-    --ut.expect(l_actual_both).to_be_like(l_expected_help);    
-  end;
-
-  procedure coverage_regex_include_object is
-  begin
-    null;
-  end;
-
-  procedure coverage_regex_exclude_schema is
-  begin
-    null;
+    ut.expect(l_actual).to_be_like(l_expected);
+    ut.expect(l_actual).not_to_be_like(l_not_expected);
   end;
 
   procedure coverage_regex_exclude_object is
+    l_expected   clob;
+    l_not_expected  clob;  
+    l_actual     clob;
   begin
-    null;
+    --Arrange
+    l_expected := '%<file path="package body ut3_develop.regex_dummy_cov">' ||
+      '%<lineToCover lineNumber="4" covered="true"/>%';
+    l_not_expected := '%<file path="package body ut3_tester_helper.regex123_dummy_cov">' ||
+      '%<lineToCover lineNumber="4" covered="true"/>%';            
+    --Act
+    l_actual :=
+      ut3_tester_helper.coverage_helper.run_tests_as_job(
+        q'[
+            ut3_develop.ut.run(
+              a_paths => ut3_develop.ut_varchar2_list('ut3_develop.test_regex_dummy_cov', 'ut3_develop.test_regex123_dummy_cov'),
+              a_reporter=> ut3_develop.ut_coverage_sonar_reporter( ),
+              a_exclude_object_expr => 'regex123',
+              a_exclude_objects => ut3_develop.ut_varchar2_list( 'ut3_develop.regex_dummy_cov' )       
+            )
+          ]'
+        );          
+    --Assert
+    ut.expect(l_actual).to_be_like(l_expected);
+    ut.expect(l_actual).not_to_be_like(l_not_expected);  
   end;
 
 end;

diff --git a/test/ut3_user/reporters/test_coverage/test_extended_coverage.pks b/test/ut3_user/reporters/test_coverage/test_extended_coverage.pks
@@ -18,18 +18,24 @@ create or replace package test_extended_coverage is
   --%test(Extended coverage does not fail the test run then tested code calls DBMS_STATS)
   procedure coverage_with_dbms_stats;
 
-  --%beforetest(ut3_tester_helper.coverage_helper.create_regex_dummy_cov_schema)
-  --%aftertest(ut3_tester_helper.coverage_helper.drop_regex_dummy_cov_schema)
+  --%beforetest(ut3_tester_helper.coverage_helper.create_regex_dummy_cov)
+  --%aftertest(ut3_tester_helper.coverage_helper.drop_regex_dummy_cov)
   --%test(Collect coverage for objects with schema regex include)  
   procedure coverage_regex_include_schema;
 
-  --%test(Collect coverage for objects with schema regex include) 
+  --%beforetest(ut3_tester_helper.coverage_helper.create_regex_dummy_cov) 
+  --%aftertest(ut3_tester_helper.coverage_helper.drop_regex_dummy_cov)
+  --%test(Collect coverage for objects with object regex include) 
   procedure coverage_regex_include_object;
 
-  --%test(Collect coverage for objects with schema regex include) 
+  --%beforetest(ut3_tester_helper.coverage_helper.create_regex_dummy_cov) 
+  --%aftertest(ut3_tester_helper.coverage_helper.drop_regex_dummy_cov)
+  --%test(Collect coverage for objects with schema regex exclude) 
   procedure coverage_regex_exclude_schema;  
 
-  --%test(Collect coverage for objects with schema regex include) 
+  --%beforetest(ut3_tester_helper.coverage_helper.create_regex_dummy_cov) 
+  --%aftertest(ut3_tester_helper.coverage_helper.drop_regex_dummy_cov)
+  --%test(Collect coverage for objects with object regex exclude) 
   procedure coverage_regex_exclude_object;  
 
 end;