Snowflake动态SQL中数组作为绑定变量传入array_contains报错解决

错误根因 #

Snowflake动态SQL执行时，对直接绑定的ARRAY类型入参不会做自动类型适配，传入array_contains函数时，引擎会将绑定值识别为未明确类型的VARIANT值，无法匹配函数第二个参数必须为ARRAY类型的要求，最终抛出绑定变量解析失败、类型不匹配类错误。

可落地实现方案 #

方案1：显式类型转换（通用推荐，无SQL注入风险） #

核心逻辑是保留绑定变量传参的方式，在动态SQL内部使用TO_ARRAY()函数对绑定的数组参数做显式类型声明，让引擎可以正确识别入参类型，全程不做字符串拼接，完全规避注入风险。
示例存储过程代码：

CREATE OR REPLACE PROCEDURE query_with_array_filter(filter_arr ARRAY)
RETURNS TABLE(user_id NUMBER, user_name VARCHAR)
LANGUAGE SQL
AS
DECLARE
    dynamic_query VARCHAR;
    query_result RESULTSET;
BEGIN
    dynamic_query := '
        SELECT user_id, user_name
        FROM user_info
        -- 核心：用TO_ARRAY包裹绑定的数组参数，匹配函数入参类型要求
        WHERE ARRAY_CONTAINS(user_name::VARIANT, TO_ARRAY(:filter_arr))
    ';
    query_result := (EXECUTE IMMEDIATE dynamic_query USING filter_arr);
    RETURN TABLE(query_result);
END;

注意：调用array_contains时，第一个待匹配的字段需要显式转为VARIANT类型，否则会出现元素类型和数组内元素类型不匹配的报错。

方案2：固定长度数组场景适配 #

如果业务场景中传入的数组长度固定、元素类型统一，可以将数组元素拆分为独立绑定变量，通过ARRAY_CONSTRUCT在动态SQL内重构数组，适合对查询性能有极致要求的场景：

CREATE OR REPLACE PROCEDURE query_with_fixed_len_array(filter_arr ARRAY)
RETURNS TABLE(user_id NUMBER, user_name VARCHAR)
LANGUAGE SQL
AS
DECLARE
    dynamic_query VARCHAR;
    query_result RESULTSET;
    elem1 VARCHAR := GET(filter_arr, 0)::VARCHAR;
    elem2 VARCHAR := GET(filter_arr, 1)::VARCHAR;
    elem3 VARCHAR := GET(filter_arr, 2)::VARCHAR;
BEGIN
    dynamic_query := '
        SELECT user_id, user_name
        FROM user_info
        WHERE ARRAY_CONTAINS(user_name::VARIANT, ARRAY_CONSTRUCT(:1, :2, :3))
    ';
    -- 按顺序绑定拆分后的元素值
    query_result := (EXECUTE IMMEDIATE dynamic_query USING elem1, elem2, elem3);
    RETURN TABLE(query_result);
END;

常见避坑点 #

• 禁止直接把数组参数值拼接到动态SQL字符串中，不仅存在SQL注入风险，数组元素包含单引号、特殊字符时还会直接触发SQL语法错误
• 不要省略TO_ARRAY()直接绑定裸数组到array_contains，该写法仅能在非动态SQL的静态逻辑中生效，动态SQL执行上下文下无法自动识别类型
• 如果数组内存储的是NUMBER类型值，array_contains的第一个待匹配字段同样需要转为VARIANT类型，否则会触发类型校验失败

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