如何解密 BigQuery 中的列? [英] How can I decrypt columns in BigQuery?

问题描述

我想使用 BigQuery 函数解密 BigQuery 中的一些加密列.

I have some encrypted columns in BigQuery that I want to decrypt using BigQuery functions.

用于加密它们的机制是 AES 256.使用的向量以 UTF8 编码.生成的数据以 Base64 加密.

The mechanic used in encrypting them is AES 256. The vector used is encoded in UTF8. The resulting data is encrypted in Base64.

我想要做的是使用函数解密在 BigQuery 中传递给我的字段，而无需经历在其他地方解密它们然后将它们集成回 BigQuery 的额外步骤的麻烦.请注意，我在 BigQuery 中可以访问的加密列是字符串类型.

What I want to do is decrypt the fields passed to me in BigQuery using functions without going through the trouble of doing extra steps of decrypting them elsewhere and then integrating them back in BigQuery. Note that the encrypted columns which I have access in BigQuery to are of type string.

我阅读了一些可以在 BigQuery 中解密的函数，例如 AEAD.DECRYPT_BYTES 和 AEAD.DECRYPT_STRING，我想知道它们是否对我有帮助.

I read about some functions that can decrypt in BigQuery like AEAD.DECRYPT_BYTES and AEAD.DECRYPT_STRING, and I wonder if they could be of help in my case.

有没有办法做到这一点?如果是这样，你能带我完成整个过程吗?

Are there any way to do that ? If so, can you walk me through the process ?

P.S:这些不是实际的键值和向量值，只是一个相似的例子.

P.S: Those are not the actual key and vector values, just a similar-looking example.

推荐答案

以下是如何使用 AES-CBC 和 PKCS 填充进行解密的示例.我不清楚您的示例中是否有有效的密钥/密文，因为我无法使用该密钥解密 vector:

Here is an example of how to decrypt using AES-CBC with PKCS padding. I'm not clear on whether you have a valid key/ciphertext in your example, since I'm not able to decrypt vector using that key:

SELECT
  AEAD.DECRYPT_STRING(
    KEYS.ADD_KEY_FROM_RAW_BYTES(
      b'', 'AES_CBC_PKCS', FROM_BASE64('dfrBArd8b6YZFDGTYrZtQa==')),
    FROM_BASE64('/FCM1XMvr+rHwZx'), '');

这是一个有效的独立示例:

Here is a self-contained example that does work:

WITH EncryptedInput AS (
  SELECT FROM_HEX('deed2a88e73dccaa30a9e6e296f62be27db30db16f76d3f42c85d31db3f46376') AS ciphertext,
    b'1234567890123456' AS key UNION ALL
  SELECT FROM_HEX('deed2a88e73dccaa30a9e6e296f62be2ea3f4c2ac2c8863306fd9ff87e10497b61d86111fafd0d0fe0046d7e199044ec'),
    b'1234567890123456' UNION ALL
  SELECT FROM_HEX('0102030405060708090a0b0c0d0e0f1073d8712936ea9899952e97284288c1cd7b7cbfff0a53ae87a19454f7d84082a07a25fc01031b5e08c6b7ce6520989b82'),
    b'98765432101234567890123456789012' UNION ALL
  SELECT NULL, b'1234567890123456' UNION ALL
  SELECT FROM_HEX('deed2a88e73dccaa30a9e6e296f62be27db30db16f76d3f42c85d31db3f46376'),
    NULL
)
SELECT AEAD.DECRYPT_STRING(KEYS.ADD_KEY_FROM_RAW_BYTES(b'', 'AES_CBC_PKCS', key), ciphertext, '') AS plaintext
FROM EncryptedInput;

这篇关于如何解密 BigQuery 中的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！