1

I'm trying to store in BigQuery encrypted data using AES GCM.

Data is encrypted using Python's Cryptodome library.

This is the code

from Cryptodome.Cipher import AES
from Cryptodome.Random import get_random_bytes

key = get_random_bytes(32)


def encrypt(txt):
   nonce = get_random_bytes(16)
   cipher = AES.new(key, AES.MODE_GCM, nonce)
   cipher, tag = cipher.encrypt_and_digest(txt)
   return nonce, cipher, tag


def decrypt(nonce, ciphertext, tag):
   cipher = AES.new(key, AES.MODE_GCM, nonce)
   return cipher.decrypt_and_verify(ciphertext, tag)


if __name__ == '__main__':
   # Bigquery insert key
   # row_to_insert = [[email, key]]
   # errors = client.insert_rows(table_keys, row_to_insert)

   e = encrypt('that is a message'.encode('utf-8'))

   # BigQuery insert encrypt message
   # row_to_insert = [[email, b''.join(e)]]
   # errors = client.insert_rows(table_data, row_to_insert)
   print(decrypt(e[0], e[1], e[2]))

An output example of key and ciphertext will be:

key --> b'\x91\xfa\x02\xa9+\x1d\xcf_\xcd\n\xe3ci\x9dCq\x8dw\x94\xbb\xfd\x040\xad\xaer!8_\xb0\xe4\xb3'
ciphertext -> b'\x1a\xa8F\x17 \xfa\xfbf\x19*A\xc80\xd96e\xcf'
Nonce -> b'\xe1s\x9er\xb4{\xe6\xfd[\xcdw(\xd4\x00\xf3\x1b'
tag -> b'f\t\x1b\xcd\x8b\x1au\xfc\xba\x87\xa2\x85\xca\xa7\n\xe8'

In python, the data are well decrypted but not in bigquery that return:

Failed to decrypt ciphertext using key of length 32. IV and ciphertext (in hexadecimal) are 'c103fcf32913be2de8883dfe' and '771b52e409157a5cb148769dfb789c33bbbada74424f0aa657c216e2748dda40f22fbb45eccbef1f776b11fd22dbd4c28d86...'. OpenSSL error is: BAD_DECRYPT

This is the query:

SELECT BQ.AES_DECRYPT('GCM',message, key) FROM `origen.data` join `origen.keys` on `origen.data`.email  = 'email@gmail.com'

Any idea about what I'm doing wrong?

AdrianCR
  • 23
  • 1
  • 4
  • BigQuery's encryption functions are documented here: https://cloud.google.com/bigquery/docs/reference/standard-sql/aead_encryption_functions. The one that you're using is a one-off function that was added prior to these being available and is subject to removal in the future. – Elliott Brossard Oct 04 '19 at 13:31
  • Can you give an example key and ciphertext (which aren't used in practice) from the Python code that you are using? – Elliott Brossard Oct 04 '19 at 14:43
  • @ElliottBrossard I have added to the post an example of an output of my code. – AdrianCR Oct 04 '19 at 16:36
  • Actually, i am making all the encrypt process with the functions that are in this link but I dont want to make a job.query to encrypt my data so I decided to move this part out of BigQuery and insert directly data encrypted but now I cant decrypt them. – AdrianCR Oct 04 '19 at 16:43
  • Are there any online sites that you've successfully been able to use to perform the decryption with this key and ciphertext? – Elliott Brossard Oct 04 '19 at 18:06

0 Answers0