雪花存储过程：处理多行字符串和单引号

Question

我在雪花存储过程中使用反勾来构建下面的插入查询，它在激发sql时工作得很好

CREATE OR REPLACE PROCEDURE SP_TEST_CALLPROC()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS $$
var q = `insert into abc 
         select col1, col2, 
         to_date(col3,'YYYYMMDD') as col3 from def;`
var insrtSql = snowflake.createStatement({sqlText: q});
var result = insrtSql.execute();
result.next()
$$
;

我还想将此查询记录到日志表中，由于TO_DATE函数'yyyymmdd'中的单引号，该查询失败了：

CREATE OR REPLACE PROCEDURE SP_LOAD_LOG_POC(PROCESS_ID FLOAT,
                            PROCESS_NAME STRING,
                            BATCH_ID FLOAT,
                            LOG_TXT STRING)
    RETURNS STRING
    LANGUAGE JAVASCRIPT
    EXECUTE AS OWNER
    AS $$
 
    var query = `INSERT INTO INGESTION_LOG
                  VALUES
                  (${PROCESS_ID},'${PROCESS_NAME}',${BATCH_ID},current_timestamp(),'${LOG_TXT}');`
    var stmt = snowflake.createStatement( { sqlText: query});
    var res = stmt.execute();
 $$
;
CREATE OR REPLACE PROCEDURE SP_TEST_CALLPROC()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS $$
function log(process_id,process_name,batch_id,log_txt){
        snowflake.createStatement( { sqlText: `call SP_LOAD_LOG_POC(:1,:2,:3,:4)`, binds:[process_id,process_name,batch_id,log_txt] } ).execute();
        }

var q = `insert into abc 
         select col1, col2, 
         to_date(col3,'YYYYMMDD') as col3 from def;`
log(1,'test_procedure',12345,`Firing insert query : ${q}`);
var insrtSql = snowflake.createStatement({sqlText: q});
var result = insrtSql.execute();
result.next()
$$
;

CALL SP_TEST_CALLPROC()

Execution error in store procedure SP_TEST_CALLPROC: 
Execution error in store procedure SP_LOAD_LOG_POC: SQL compilation error: syntax error line 5 at position 23 unexpected 'YYYYMMDD'.
At Statement.execute, line 10 position 19 
At Statement.execute, line 4 position 169

尝试使用javascript函数replaceAll（）将单引号替换为2个单引号('->'')，但雪花存储过程中不支持此函数。有人能建议如何在插入日志条目时消除此错误吗

Answer 1

如果必须将SQL作为字符串传递给另一个函数、UDF或SP，除了单个刻度之外，还有许多特殊字符需要考虑。下面是我编写的两个JavaScript帮助器函数，用于确保插入时不同数据类型的安全性。

由于wrapInsertValue函数为文本类型调用escapeInsertString，所以您可以只调用该类型。wrapInsertValue函数的目的是读取列的类型并相应地处理它。注意，escapeInsertString函数也将压缩空白。您可以注释掉s=s.replace(/\s+/g,“”）行；如果您不想这样做。

function wrapInsertValue(value, dataType){
    if (value == 'null'){
        return 'NULL';
    }
    switch (dataType){
        case "TEXT":
            return "'" + escapeInsertString(value) + "'";
        case "OBJECT":
            return "'" + escapeInsertString(value) + "'";
        case "TIMESTAMP_TZ":
            return "'" + value + "'";
        case "TIMESTAMP":
            return "'" + value + "'";
        default: return value;
    }
}

function escapeInsertString(value) {
    var s = value.replace(/\\/g, "\\\\");
    s = s.replace(/'/g, "''" );
    s = s.replace(/"/g, '\\"');
    s = s.replace(/\s+/g, " ");
//  s = s.replace(/[^\x00-\x7F]/g, "");
    return s;
}