如何解密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.

推荐答案

这里是一个示例，说明如何使用带有PKCS填充的AES-CBC进行解密.我不清楚您的示例中是否有有效的密钥/密文，因为我无法使用该密钥对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屋！