Snowflake error on parsing JSON as single quotes are not escaped-CodePudding

I am trying to log error when working with some data in Snowflake data warehouse.

The error I am getting on specific data file is as follows and I need to get it into a table called DATA_LOAD_LOG:

INSERT INTO DWH_OPS.DATA_LOAD_LOG select 'DATA LOAD PROCEDURE', ?, ?, (datediff('milliseconds', ?, ?)/1000), current_schema(), current_database(), current_user(), current_role(), ?, parse_json('[{"Error":"Can't parse 'n/a' as date with format 'YYYY-MM-DD'","Survey Name":"data.csv","Stage Name":"@DWH_OPS.AZURE_BLOB","Execution Duration (in seconds)":5.405}]')::VARIANT

The error message is:

"Can't parse 'n/a' as date with format 'YYYY-MM-DD'"

the catch part is returning a different syntax error as there single quote before t and 'YYYY-MM-DD'. so the log is not added to the table.

here is the catch part script:

try {
  // Try code
} catch (err) {
  var obj = {};
  var res = [];
  obj["Error"] = "Exists";
  obj["Error Message"] = err.message;
  obj["Status"] = err.status;
  obj["Error Code"] = err.code;
  res.push(obj);
  var insert_into_data_log =
    "INSERT INTO DWH_OPS.DATA_LOAD_LOG select 'DATA LOAD PROCEDURE', current_timestamp(), "  
    "current_timestamp(), 0, "  
    "current_schema(), current_database(), current_user(), current_role(), "  
    "'SCRIPT ERROR', parse_json('"  
    JSON.stringify(res)  
    "')::VARIANT";
  var data_log_stmt = snowflake
    .createStatement({ sqlText: insert_into_data_log })
    .execute();

  return err;
}

I tried to replace with space or to escape it using:

var errorMsg = err.message; obj['Error Message'] = errorMsg.replace("'","").replace("."," ").replace("\n"," ");

but it didn't work at all and it is throwing the same error.

CodePudding user response：

You can use this method to escape the single quotes:

obj["Error Message"] = obj["Error Message"].replace(/'/g, "\\''");

Here is a simple script to test:

create table DATA_LOAD_LOG ( t timestamp, v variant );

create or replace procedure test_sp()
returns string  
language javascript
as
$$
var obj = {};
var res = [];
obj["Error"] = "Exists";
obj["Error Message"] = "Can't parse 'n/a' as date with format 'YYYY-MM-DD'";

obj["Error Message"] = obj["Error Message"].replace(/'/g, "\\''");
obj["Error Code"] = 200;
res.push(obj);

var insert_into_data_log =
"INSERT INTO DATA_LOAD_LOG select current_timestamp(), "  
"parse_json('"   JSON.stringify(res)   "')::VARIANT";
var data_log_stmt = snowflake.createStatement({ sqlText: insert_into_data_log }).execute();
 
return "Ok";

$$;

call test_sp();

CodePudding user response：

If you're going to insert strings and dates directly by constructing the SQL rather than using bind variables, you have to escape more than just single quotes. You also have to escape backslashes and line breaks at a minimum. Here's a helper function you can use to do that.

function escapeString(str){
    str = str.replace(/'/g, "''");
    str = str.replace(/\\/g, "\\\\");
    str = str.replace(/(\r\n|\n|\r)/gm," ");
    return str;
}

Used in your SP:

try {
  // Try code
} catch (err) {
  var obj = {};
  var res = [];
  obj["Error"] = "Exists";
  obj["Error Message"] = escapeString(err.message);
  obj["Status"] = escapeString(err.status);
  obj["Error Code"] = err.code;
  res.push(obj);
  var insert_into_data_log =
    "INSERT INTO DWH_OPS.DATA_LOAD_LOG select 'DATA LOAD PROCEDURE', current_timestamp(), "  
    "current_timestamp(), 0, "  
    "current_schema(), current_database(), current_user(), current_role(), "  
    "'SCRIPT ERROR', parse_json('"  
    JSON.stringify(res)  
    "')::VARIANT";
  var data_log_stmt = snowflake
    .createStatement({ sqlText: insert_into_data_log })
    .execute();

  return err;


function escapeString(str){
    str = str.replace(/'/g, "''");
    str = str.replace(/\\/g, "\\\\");
    str = str.replace(/(\r\n|\n|\r)/gm," ");
    return str;
}

}