如何在Snowflake存储过程中无需动态SQL实现多列重复值检测？

我有一个Snowflake存储过程SP1，调用它能准确返回重复数据——根据输入的表名和列名（第二个参数支持多列，比如field1, field2）返回重复行的统计。但SP1用了动态SQL拼接，存在SQL注入风险，属于不良实践。

我已经实现了用绑定变量的SP2，但它仅支持传入单列，尝试用数组也没成功。想知道不使用动态SQL拼接的话，怎么编写支持多列传入的存储过程？

SP1（存在SQL注入风险） #

CREATE OR REPLACE PROCEDURE TWOCHECKDUPE(tablename varchar, fieldcheck varchar)
  RETURNS TABLE ()
  LANGUAGE SQL
AS
DECLARE
    rs RESULTSET;
    stmt VARCHAR;
BEGIN
    stmt := 'SELECT count(1), '||:fieldcheck||' from '||:tablename||' group by '||:fieldcheck||' having count(1) > 1';
    rs := (EXECUTE IMMEDIATE :stmt);

    RETURN TABLE(rs);
END;

call TWOCHECKDUPE('...tablename...', 'field1, field2');

SP2（仅支持单列） #

CREATE OR REPLACE PROCEDURE ONECHECKDUPE(tablename varchar, fieldcheck varchar)
  RETURNS TABLE ()
  LANGUAGE SQL
AS
DECLARE
  rs RESULTSET;
  query VARCHAR DEFAULT 'SELECT count(1), IDENTIFIER(?) FROM IDENTIFIER(?) GROUP BY IDENTIFIER(?) HAVING COUNT(1) > 1';
BEGIN
  rs := (EXECUTE IMMEDIATE :query USING (fieldcheck, tablename, fieldcheck));
  RETURN TABLE(rs);
END;

call ONECHECKDUPE('...tablename...', 'field1');

解决方案：安全构造多列查询，避免SQL注入 #

Snowflake的IDENTIFIER()无法直接解析逗号分隔的多列字符串，但可以通过拆分列名、结合内置安全函数来构造无注入风险的动态SQL，同时支持多列传入。

方案1：支持逗号分隔的列名字符串参数 #

CREATE OR REPLACE PROCEDURE CHECK_DUPLICATES(tablename VARCHAR, fieldcheck VARCHAR)
RETURNS TABLE ()
LANGUAGE SQL
AS
DECLARE
    rs RESULTSET;
    -- 拆分列名并包装为安全的IDENTIFIER格式
    safe_fields VARCHAR := ARRAY_TO_STRING(
        ARRAY_TRANSFORM(SPLIT(TRIM(fieldcheck), ','), x => 'IDENTIFIER(''' || TRIM(x) || ''')'),
        ', '
    );
    -- 构造安全查询，用QUOTE_IDENTIFIER处理表名
    query VARCHAR := 'SELECT COUNT(1), ' || safe_fields || ' FROM IDENTIFIER(?) GROUP BY ' || safe_fields || ' HAVING COUNT(1) > 1';
BEGIN
    rs := (EXECUTE IMMEDIATE :query USING (QUOTE_IDENTIFIER(tablename)));
    RETURN TABLE(rs);
END;

调用方式：

CALL CHECK_DUPLICATES('your_schema.your_table', 'field1, field2, field3');

方案2：支持数组类型的列名参数 #

如果更倾向于用数组传递列名，可使用以下版本：

CREATE OR REPLACE PROCEDURE CHECK_DUPLICATES_ARRAY(tablename VARCHAR, fieldcheck ARRAY)
RETURNS TABLE ()
LANGUAGE SQL
AS
DECLARE
    rs RESULTSET;
    safe_fields VARCHAR := ARRAY_TO_STRING(
        ARRAY_TRANSFORM(fieldcheck, x => 'IDENTIFIER(''' || TRIM(x) || ''')'),
        ', '
    );
    query VARCHAR := 'SELECT COUNT(1), ' || safe_fields || ' FROM IDENTIFIER(?) GROUP BY ' || safe_fields || ' HAVING COUNT(1) > 1';
BEGIN
    rs := (EXECUTE IMMEDIATE :query USING (QUOTE_IDENTIFIER(tablename)));
    RETURN TABLE(rs);
END;

调用方式：

CALL CHECK_DUPLICATES_ARRAY('your_schema.your_table', ARRAY_CONSTRUCT('field1', 'field2'));

安全说明 #

• TRIM()处理列名前后空格，避免格式错误
• ARRAY_TRANSFORM()将每个列名包装为IDENTIFIER('列名')，确保列名被正确解析
• QUOTE_IDENTIFIER()对表名进行转义，防止包含特殊字符或恶意注入的表名被执行
• 全程避免直接拼接用户输入的原始字符串，所有动态部分都经过Snowflake内置安全函数处理

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