Create a trigger on compile

Question

To prevent SQL injection, I want to validate all Varchar parameters of procedures, user functions and packages.

Is it possible to create a trigger when compiling or creating a new stored procedure or a user function to force all developpers to add this validation ? To validate a parameter I need to call a user function that validate and sanitize each parameter of type Varchar.

We have also a code review process, but we think that it is better to have also an automated process. — Bilel Chaouadi, Oct 17 '19 at 21:11

score 1 · Accepted Answer · answered Oct 16 '19 at 19:01

Maybe a starting point:

CREATE OR REPLACE TRIGGER ddl_trigger
BEFORE CREATE OR ALTER
ON SCHEMA

DECLARE
 sql_text ora_name_list_t;
 v_sysevent VARCHAR2(32767);
 v_sql_text VARCHAR(32767);
 i        PLS_INTEGER;


BEGIN

 i := sql_txt(sql_text);

  select ora_sysevent,
         sql_text(1)
    into v_sysevent,
         v_sql_text
    from dual;

 dbms_output.put_line('Event: ' || v_sysevent);
 dbms_output.put_line('DDL: ' || v_sql_text);

END ddl_trigger;
/

v_sql_text contains the text of the ddl being executed. Perhaps you could scan through v_sql_text to verify that the validation routines you require are being called.

Create a trigger on compile

1 Answers1