How to capture a invalid SQL in a error handling procedure

Question

we have a common error handling procedure which we use in other procedures to capture the error into a common error table.

The statement that errors out when inserting into the table is as follows, it has a missing single quote. (This has a single quote missing in the beginning. target err' )

This statement is the v_err_stmt value in the INSERT statement.

insert into temp_census (ERROR_DATE, sis_table , census_table ) 
                    values(current_timestamp, target err','target error test')

The error message is SQL compilation error: syntax error line 2 at position 53 unexpected 'err'. parse error line 2 at position 78 near ''.

The procedure is as follows.

CREATE OR REPLACE PROCEDURE SNOW_ERROR_CAPTURE(p_batch_id number, p_job_name varchar2,p_error_desc varchar2, p_err_stmt varchar2)
returns varchar(20000)
language sql
as
$$
declare
   v_err_stmt varchar2;
   v_error_desc varchar2;
   v_job_name varchar2;
   v_batch_id number;
   count number;
    begin

  if (p_batch_id IS NULL) then
  v_batch_id :=0;
  else
   v_batch_id :=p_batch_id;
  end if;
 
   if (p_job_name IS NULL) then
  v_job_name :='UNknown';
  else
   v_job_name :=p_job_name;
  end if;
 
    if (p_error_desc IS NULL) then
  v_error_desc :='UNknown';
  else
   v_error_desc :=p_error_desc;
  end if;
  
    if (p_err_stmt IS NULL) then
  v_err_stmt :='UNknown';
  else
   v_err_stmt :=p_err_stmt;
  end if;
  
     let _sql varchar := 'insert into BATCH_ERROR_LOG
                (ERROR_DATE , BATCH_ID, JOB_NAME, DESCRIPTION, ERROR_STMT ) 
                values
                (current_timestamp ,' || v_batch_id || ',''' || v_job_name || ''',''' || v_error_desc|| ''',''' || v_err_stmt || ''')';
    execute immediate _sql;
    --return _sql;
exception
  when statement_error then
    return object_construct('Error type', 'STATEMENT_ERROR',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate
                            );
   when other then
    return object_construct('Error type', 'Other error',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
end;

$$;

user_43992 · Answer

Given you are string concatenating in sql space. What ever token you arr using need to be made safe. So in you v_err_msg you need to escape those single quotes, with replace(v_err_msg, '\'', '\\'')

select $$insert into temp_census (ERROR_DATE, sis_table , census_table )
                    values(current_timestamp, target err','target error test')$$ as v_err_msg
    ,'SELECT ''' || replace(v_err_msg, '\'', '\\'') || ''';' as output;

gives the output

SELECT 'insert into temp_census (ERROR_DATE, sis_table , census_table )                     values(current_timestamp, target err\',\'target error test\')';

which is valid and safe.

But we also need to escape any escape token present also, and that has to be done first. thus you need to use

select $$word 'quoted' and with slash \ $$ as v_err_msg
    ,'SELECT ''' || replace(replace(v_err_msg, '\', '\\'), '\'', '\\'') || ''';' as output;

which gives:

SELECT 'word \'qouted\' and with slash \ ';

which when runs gives us back the input:

'WORD 'QUOTED' AND WITH SLASH \ '
word 'quoted' and with slash \

How to capture a invalid SQL in a error handling procedure

1 Answers