如何在Snowflake中使用DECRYPT解密外部加密的AES 256字段？

问题根源 #

你的两次尝试失败都源于对Snowflake DECRYPT() 函数的参数要求和外部加密格式的不匹配：

1. 第一个错误是因为你将非HEX编码的加密字符串当作HEX格式转换，to_binary()无法解析非HEX字符串。
2. 第二个错误是因为DECRYPT()的第一个参数必须是BINARY类型，直接传入VARCHAR加密字符串不符合函数参数要求。

Snowflake的DECRYPT()仅支持与自身加密逻辑兼容的解密操作，核心要求是：加密数据需为二进制类型，且解密时必须匹配外部加密的算法、模式、填充方式、编码格式等细节。

必要前置信息 #

必须从加密团队确认以下关键信息，否则无法正确解密：

• 加密算法（如AES-128、AES-256）
• 加密模式（如ECB、CBC、GCM）
• 填充方式（Snowflake默认使用PKCS#7，需确认外部加密是否一致）
• 加密后数据的存储编码（Base64、HEX、原始二进制）
• 密钥的格式：是二进制的Base64/HEX编码，还是需要从字符串通过哈希生成
• 是否使用了初始化向量（IV），以及IV的获取方式（是否随加密数据存储、固定值或其他）

通用解密示例（以常见AES-256-CBC场景为例） #

假设外部加密使用AES-256-CBC，加密数据以Base64存储，密钥从字符串SHA-256哈希生成，且IV为已知的Base64字符串：

-- 生成符合AES-256要求的二进制密钥（从字符串哈希）
SET aes_key = SHA2('AES Key provided by the Encryption Team', 256);
-- 将已知的IV字符串转成二进制
SET iv_val = TO_BINARY('your_iv_from_encryption_team', 'BASE64');

-- 解密表中answer字段
SELECT TO_VARCHAR(
    DECRYPT(
        TO_BINARY(answer, 'BASE64'), -- 把存储的Base64加密数据转成二进制
        $aes_key,
        $iv_val
    ),
    'UTF-8'
) AS decrypted_answer
FROM your_snowflake_table;

其他场景适配 #

• 如果加密数据是HEX编码：将TO_BINARY(answer, 'BASE64')替换为TO_BINARY(answer, 'HEX')
• 如果无需IV（如ECB模式）：去掉DECRYPT()的第三个IV参数
• 如果密钥本身是Base64编码的二进制：将SHA2(...)替换为TO_BINARY('your_base64_key', 'BASE64')

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