Snowflake解析JSON单引号未转义 写入DATA_LOAD_LOG日志表报错

问题根因 #

当前错误本质是动态拼接SQL时，JSON字符串内的单引号未正确转义，导致SQL语句的字符串边界被打破。你之前的替换方案无效是因为replace("'","")默认只替换第一个匹配的单引号，且直接删除字符也会导致错误日志信息失真。

解决方案1（优先推荐：使用Snowflake绑定变量，无需手动转义） #

Snowflake的createStatement原生支持参数绑定，直接把JSON字符串作为变量传入即可完全规避转义问题，同时可以避免SQL注入风险：

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);
  // 单独生成错误JSON字符串作为绑定变量
  var errorJson = JSON.stringify(res);
  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(?)::VARIANT
  `;
  // 执行时传入绑定参数
  var data_log_stmt = snowflake
    .createStatement({ 
      sqlText: insert_into_data_log,
      binds: [errorJson]
    })
    .execute();

  return err;
}

解决方案2（临时兼容方案：手动全量转义单引号） #

如果需要保留动态拼接SQL的写法，按照Snowflake的SQL语法规则，字符串内的单引号需要用两个连续单引号转义，使用正则全局替换所有单引号即可：

try {
  // Try code
} catch (err) {
  var obj = {};
  var res = [];
  obj["Error"] = "Exists";
  // 全局替换错误信息中的单引号为双单引号
  var errorMsg = err.message.replace(/'/g, "''");
  obj["Error Message"] = errorMsg;
  obj["Status"] = err.status;
  obj["Error Code"] = err.code;
  res.push(obj);
  // 对最终生成的JSON字符串再做一次全局单引号转义，避免遗漏
  var jsonStr = JSON.stringify(res).replace(/'/g, "''");
  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('" +
    jsonStr +
    "')::VARIANT";
  var data_log_stmt = snowflake
    .createStatement({ sqlText: insert_into_data_log })
    .execute();

  return err;
}

内容的提问来源于stack exchange，提问作者alim1990