如何在BigQuery控制台解密AES256加密的ssn列？

核心问题分析 #

你的两个错误根源都是密钥格式处理错误：

• 第一个查询直接将Base64密钥作为字节字符串传入，导致密钥长度变成404字节（Base64字符串的字符数），而AES256要求密钥必须是32字节（256位）。
• 第二个查询错误地用FROM_HEX()处理Base64格式的密钥，FROM_HEX()仅用于十六进制字符串解码，自然会报错。

正确解密查询 #

首先需要将Base64格式的密钥解码为原始32字节密钥，同时将Base64格式的加密列转换为字节密文，再进行解密。

基本查询（假设加密时使用空IV，或IV已包含在密文中） #

SELECT
  AEAD.DECRYPT_STRING(
    KEYS.ADD_KEY_FROM_RAW_BYTES(b'', 'AES_CBC_PKCS', FROM_BASE64('sdfdfsASgfdsHsaDSFSjsdasfsfblaBlaBLAsdfdfsASgfdsHsaDSFSjsdasfsfblaBlaBLAsdfdfsASgfdsHsaDSFSjsdasfsfblaBlaBLAsdfdfsASgfdsHsaDSFSjsdasfsfblaBlaBLA=')),
    FROM_BASE64(ssn),
    '' -- 这里替换为加密时使用的IV（Base64解码后的字节或空字符串，根据NiFi配置）
  ) AS plaintext_ssn
FROM `vf-grp-maml-dev-lab-01.Test_Dataset.test_table`;

关键说明 #

1. 密钥处理：用FROM_BASE64()解码Base64密钥字符串，得到AES256要求的32字节原始密钥。
2. 密文处理：用FROM_BASE64(ssn)将存储的Base64加密列转换为字节类型的密文，而不是直接CAST(ssn AS BYTES)（后者会把字符串转成UTF-8字节，不是解码Base64）。
3. IV问题：
   • AES-CBC模式必须使用初始化向量（IV），如果NiFi加密时生成了随机IV，通常会将IV附加在密文开头（比如前16字节，因为CBC的IV长度等于块大小16字节）。这种情况下需要拆分IV和密文：

     SELECT
       AEAD.DECRYPT_STRING(
         KEYS.ADD_KEY_FROM_RAW_BYTES(b'', 'AES_CBC_PKCS', FROM_BASE64('sdfdfsASgfdsHsaDSFSjsdasfsfblaBlaBLAsdfdfsASgfdsHsaDSFSjsdasfsfblaBlaBLAsdfdfsASgfdsHsaDSFSjsdasfsfblaBlaBLAsdfdfsASgfdsHsaDSFSjsdasfsfblaBlaBLA=')),
         SUBSTR(FROM_BASE64(ssn), 17), -- 从第17字节开始取密文（前16字节是IV）
         SUBSTR(FROM_BASE64(ssn), 1, 16) -- 取前16字节作为IV
       ) AS plaintext_ssn
     FROM `vf-grp-maml-dev-lab-01.Test_Dataset.test_table`;
     

   • 如果NiFi配置了固定IV，需要将该IV解码为字节后填入第三个参数（比如IV是Base64格式，就用FROM_BASE64('<固定IV的Base64字符串>')）。

验证密钥正确性 #

先单独验证密钥解码后的长度是否为32字节：

SELECT BYTE_LENGTH(FROM_BASE64('sdfdfsASgfdsHsaDSFSjsdasfsfblaBlaBLAsdfdfsASgfdsHsaDSFSjsdasfsfblaBlaBLAsdfdfsASgfdsHsaDSFSjsdasfsfblaBlaBLAsdfdfsASgfdsHsaDSFSjsdasfsfblaBlaBLA=')) AS key_length;

如果结果不是32，说明你提供的Base64密钥不是AES256的密钥，需要确认Vault生成的密钥是否正确。

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