AEAD Encryption Functions in Standard SQL

The following sections describe the AEAD encryption functions that BigQuery supports. For a description of how the AEAD encryption functions work, see AEAD encryption concepts.

KEYS.NEW_KEYSET

KEYS.NEW_KEYSET(key_type)

Description

Returns a serialized keyset containing a new key based on key_type. The returned keyset is a serialized BYTES representation of google.crypto.tink.Keyset that contains a primary cryptographic key and no additional keys. You can use the keyset with the AEAD.ENCRYPT, AEAD.DECRYPT_BYTES, and AEAD.DECRYPT_STRING functions for encryption and decryption, as well as with the KEYS group of key- and keyset-related functions.

key_type is a STRING literal representation of the type of key to create. key_type cannot be NULL. The only supported value for key_type is currently AEAD_AES_GCM_256. KEYS.NEW_KEYSET(AEAD_AES_GCM_256) creates a 256-bit key with the pseudo-random number generator provided by OpenSSL. The key will use AES-GCM for encryption and decryption operations.

Return Data Type

BYTES

Example

The following query creates a keyset for each row in CustomerIds, which can subsequently be used to encrypt data. Each keyset contains a single encryption key with randomly-generated key data. Each row in the output contains a customer_id and an 'AEAD_AES_GCM_256' key in BYTES.

SELECT customer_id, KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset
FROM (
  SELECT 1 AS customer_id UNION ALL
  SELECT 2 UNION ALL
  SELECT 3
) AS CustomerIds;

KEYS.ADD_KEY_FROM_RAW_BYTES

KEYS.ADD_KEY_FROM_RAW_BYTES(keyset, key_type, raw_key_bytes)

Description

Returns a serialized keyset as BYTES with the addition of a key to keyset based on key_type and raw_key_bytes.

The primary cryptographic key remains the same as in keyset. The expected length of raw_key_bytes depends on the value of key_type. The following are supported key_types:

• 'AES_CBC_PKCS': Creates a key for AES decryption using cipher block chaining and PKCS padding. raw_key_bytes is expected to be a raw key BYTES value of length 16, 24, or 32; these lengths have sizes of 128, 192, and 256 bits, respectively. BigQuery AEAD functions do not support keys of these types for encryption; instead, prefer 'AEAD_AES_GCM_256' or 'AES_GCM' keys.
• 'AES_GCM': Creates a key for AES decryption or encryption using Galois/Counter Mode. raw_key_bytes must be a raw key BYTES value of length 16 or 32; these lengths have sizes of 128 and 256 bits, respectively. When keys of this type are inputs to AEAD.ENCRYPT, the output ciphertext does not have a Tink-specific prefix indicating which key was used as input.

Return Data Type

BYTES

Example

The following query creates a table of customer IDs along with raw key bytes, called CustomerRawKeys, and a table of unique IDs, called CustomerIds. It creates a new 'AEAD_AES_GCM_256' keyset for each customer_id; then it adds a new key to each keyset, using the raw_key_bytes value corresponding to that customer_id. The output is a table where each row contains a customer_id and a keyset in BYTES, which contains the raw key added using KEYS.ADD_KEY_FROM_RAW_BYTES.

WITH CustomerRawKeys AS (
  SELECT 1 AS customer_id, b'0123456789012345' AS raw_key_bytes UNION ALL
  SELECT 2, b'9876543210543210' UNION ALL
  SELECT 3, b'0123012301230123'
), CustomerIds AS (
  SELECT 1 AS customer_id UNION ALL
  SELECT 2 UNION ALL
  SELECT 3
)
SELECT
  ci.customer_id,
  KEYS.ADD_KEY_FROM_RAW_BYTES(
    KEYS.NEW_KEYSET('AEAD_AES_GCM_256'),
    'AES_CBC_PKCS',
    (SELECT raw_key_bytes FROM CustomerRawKeys AS crk
     WHERE crk.customer_id = ci.customer_id)
  ) AS keyset
FROM CustomerIds AS ci;

The output keysets each contain two things: the primary cryptographic key created using KEYS.NEW_KEYSET('AEAD_AES_GCM_256'), and the raw key added using KEYS.ADD_KEY_FROM_RAW_BYTES. If a keyset in the output is used with AEAD.ENCRYPT, BigQuery uses the primary cryptographic key created using KEYS.NEW_KEYSET('AEAD_AES_GCM_256') to encrypt the input plaintext. If the keyset is used with AEAD.DECRYPT_STRING or AEAD.DECRYPT_BYTES, BigQuery returns the resulting plaintext if either key succeeds in decrypting the ciphertext.

AEAD.DECRYPT_BYTES

AEAD.DECRYPT_BYTES(keyset, ciphertext, additional_data)

Description

Uses the matching key from keyset to decrypt ciphertext and verifies the integrity of the data using additional_data. Returns an error if decryption or verification fails.

keyset is a serialized BYTES value returned by one of the KEYS functions. keyset must contain the key that was used to encrypt ciphertext, and the key must be in an 'ENABLED' state, or else the function returns an error. AEAD.DECRYPT_BYTES identifies the matching key in keyset by finding the key with the key ID that matches the one encrypted in ciphertext.

ciphertext is a BYTES value that is the result of a call to AEAD.ENCRYPT where the input plaintext was of type BYTES.

additional_data is a STRING or BYTES value that ensures the authenticity and integrity of the encrypted data. This function casts any STRING value to BYTES. This must be the same as the additional_data provided to AEAD.ENCRYPT to encrypt ciphertext, ignoring its type, or else the function returns an error.

Return Data Type

BYTES

Example

This example creates a table of unique IDs with associated plaintext values and keysets. Then it uses these keysets to encrypt the plaintext values as BYTES and store them in a new table. Finally, it uses AEAD.DECRYPT_BYTES to decrypt the encrypted values and display them as plaintext.

The following statement creates a table CustomerKeysets containing a column of unique IDs, a column of AEAD_AES_GCM_256 keysets, and a column of favorite animals.

CREATE TABLE aead.CustomerKeysets AS
SELECT
  1 AS customer_id,
  KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset,
  'jaguar' AS favorite_animal
UNION ALL
SELECT
  2 AS customer_id,
  KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset,
  'zebra' AS favorite_animal
UNION ALL
SELECT
  3 AS customer_id,
  KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset,
  'nautilus' AS favorite_animal;

The following statement creates a table EncryptedCustomerData containing a column of unique IDs and a column of ciphertext. The statement encrypts the plaintext favorite_animal using the keyset value from CustomerKeysets corresponding to each unique ID.

CREATE TABLE aead.EncryptedCustomerData AS
SELECT
  customer_id,
  AEAD.ENCRYPT(keyset, favorite_animal, CAST(customer_id AS STRING))
   AS encrypted_animal
FROM
  aead.CustomerKeysets AS ck;

The following query uses the keysets in the CustomerKeysets table to decrypt data in the EncryptedCustomerData table.

SELECT
  ecd.customer_id,
  AEAD.DECRYPT_STRING(
    (SELECT ck.keyset
     FROM aead.CustomerKeysets AS ck
     WHERE ecd.customer_id = ck.customer_id),
    ecd.encrypted_animal,
    CAST(ecd.customer_id AS STRING)
  ) AS favorite_animal
FROM aead.EncryptedCustomerData AS ecd;

AEAD.DECRYPT_STRING

AEAD.DECRYPT_STRING(keyset, ciphertext, additional_data)

Description

Like AEAD.DECRYPT_BYTES, but ciphertext is the BYTES output of AEAD.ENCRYPT where the plaintext input to AEAD.ENCRYPT is of type STRING, rather than of type BYTES.

Return Data Type

STRING

AEAD.ENCRYPT

AEAD.ENCRYPT(keyset, plaintext, additional_data)

Description

Encrypts plaintext using the algorithm and primary cryptographic key in the keyset. Incorporates additional_data into the returned ciphertext. Returns NULL if any input is NULL.

keyset is a serialized BYTES value returned by one of the KEYS functions.

plaintext is the STRING or BYTES value to be encrypted.

additional_data is a STRING or BYTES value to incorporate into the returned ciphertext. plaintext and additional_data must be of the same type. AEAD.ENCRYPT(keyset, string1, string2) is equivalent to AEAD.ENCRYPT(keyset, CAST(string1 AS BYTES), CAST(string2 AS BYTES)).

The output is ciphertext BYTES. The ciphertext will contain a Tink-specific prefix indicating the key used to perform the encryption, except when that key is an 'AES_GCM' key.

Return Data Type

BYTES

Example

The following query uses the keysets for each customer_id in the CustomerKeysets table to encrypt the value of the plaintext favorite_animal in the PlaintextCustomerData table corresponding to that customer_id. The output contains a column of customer_id values and a column of corresponding ciphertext output as BYTES.

WITH CustomerKeysets AS (
  SELECT 1 AS customer_id, KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset UNION ALL
  SELECT 2, KEYS.NEW_KEYSET('AEAD_AES_GCM_256') UNION ALL
  SELECT 3, KEYS.NEW_KEYSET('AEAD_AES_GCM_256')
), PlaintextCustomerData AS (
  SELECT 1 AS customer_id, 'elephant' AS favorite_animal UNION ALL
  SELECT 2, 'walrus' UNION ALL
  SELECT 3, 'leopard'
)
SELECT
  pcd.customer_id,
  AEAD.ENCRYPT(
    (SELECT keyset
     FROM CustomerKeysets AS ck
     WHERE ck.customer_id = pcd.customer_id),
    pcd.favorite_animal,
    CAST(pcd.customer_id AS STRING)
  ) AS encrypted_animal
FROM PlaintextCustomerData AS pcd;

KEYS.KEYSET_FROM_JSON

KEYS.KEYSET_FROM_JSON(json_keyset)

Description

Returns the input json_keyset STRING as serialized BYTES, which is a valid input for other KEYS and AEAD functions. The JSON STRING must be compatible with the definition of the google.crypto.tink.Keyset protocol buffer message: the JSON keyset should be a JSON object containing objects and name-value pairs corresponding to those in the "keyset" message in the google.crypto.tink.Keyset definition. You can convert the output serialized BYTES representation back to a JSON STRING using KEYS.KEYSET_TO_JSON.

Return Data Type

BYTES

Example

KEYS.KEYSET_FROM_JSON takes JSON-formatted STRING values like the following:

{
  "key":[
      {
        "keyData":{
          "keyMaterialType":"SYMMETRIC",
          "typeUrl":"type.googleapis.com/google.crypto.tink.AesGcmKey",
          "value":"GiD80Z8kL6AP3iSNHhqseZGAIvq7TVQzClT7FQy8YwK3OQ=="
        },
        "keyId":3101427138,
        "outputPrefixType":"TINK",
        "status":"ENABLED"
      }
    ],
  "primaryKeyId":3101427138
}

The following query creates a new keyset from a JSON-formatted STRING json_keyset:

SELECT KEYS.KEYSET_FROM_JSON(json_keyset);

This returns the json_keyset serialized as BYTES, like the following:

\x08\x9d\x8e\x85\x82\x09\x12d\x0aX\x0a0
type.googleapis.com/google.crypto.tink.AesGcmKey\x12\"\x1a qX\xe4IG\x87\x1f\xde
\xe3)+e\x98\x0a\x1c}\xfe\x88<\x12\xeb\xc1t\xb8\x83\x1a\xcd\xa8\x97\x84g\x18\x01
\x10\x01\x18\x9d\x8e\x85\x82\x09 \x01

KEYS.KEYSET_TO_JSON

KEYS.KEYSET_TO_JSON(keyset)

Description

Returns a JSON STRING representation of the input keyset. The returned JSON STRING is compatible with the definition of the google.crypto.tink.Keyset protocol buffer message. You can convert the JSON STRING representation back to BYTES using KEYS.KEYSET_FROM_JSON.

Return Data Type

STRING

Example

The following query returns a new 'AEAD_AES_GCM_256' keyset as a JSON-formatted STRING.

SELECT KEYS.KEYSET_TO_JSON(KEYS.NEW_KEYSET('AEAD_AES_GCM_256'));

The result is a STRING like the following.

{
  "key":[
      {
        "keyData":{
          "keyMaterialType":"SYMMETRIC",
          "typeUrl":"type.googleapis.com/google.crypto.tink.AesGcmKey",
          "value":"GiD80Z8kL6AP3iSNHhqseZGAIvq7TVQzClT7FQy8YwK3OQ=="
        },
        "keyId":3101427138,
        "outputPrefixType":"TINK",
        "status":"ENABLED"
      }
    ],
  "primaryKeyId":3101427138
}

KEYS.ROTATE_KEYSET

KEYS.ROTATE_KEYSET(keyset, key_type)

Description

Adds a new key to keyset based on key_type. This new key becomes the primary cryptographic key of the new keyset. Returns the new keyset serialized as BYTES.

The old primary cryptographic key from the input keyset remains an additional key in the returned keyset.

Return Data Type

BYTES

Example

The following statement creates a table containing a column of unique customer_id values and 'AEAD_AES_GCM_256' keysets. Then, it creates a new primary cryptographic key within each keyset in the source table using KEYS.ROTATE_KEYSET. Each row in the output contains a customer_id and an 'AEAD_AES_GCM_256' keyset in BYTES.

WITH ExistingKeysets AS (
SELECT 1 AS customer_id, KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset
    UNION ALL
  SELECT 2, KEYS.NEW_KEYSET('AEAD_AES_GCM_256') UNION ALL
  SELECT 3, KEYS.NEW_KEYSET('AEAD_AES_GCM_256')
)
SELECT customer_id, KEYS.ROTATE_KEYSET(keyset, 'AEAD_AES_GCM_256') AS keyset
FROM ExistingKeysets;

KEYS.KEYSET_LENGTH

KEYS.KEYSET_LENGTH(keyset)

Description

Returns the number of keys in the provided keyset.

Return Data Type

INT64

Example

This example references a JSON-formatted STRING called json_keyset that contains two keys:

{
   "primaryKeyId":1354994251,
   "key":[
      {
         "keyData":{
            "keyMaterialType":"SYMMETRIC",
            "typeUrl":"type.googleapis.com/google.crypto.tink.AesGcmKey",
            "value":"GiD9sxQRgFj4aYN78vaIlxInjZkG/uvyWSY9a8GN+ELV2Q=="
         },
         "keyId":1354994251,
         "outputPrefixType":"TINK",
         "status":"ENABLED"
      }
   ],
   "key":[
      {
         "keyData":{
            "keyMaterialType":"SYMMETRIC",
            "typeUrl":"type.googleapis.com/google.crypto.tink.AesGcmKey",
            "value":"PRn76sxQRgFj4aYN00vaIlxInjZkG/uvyWSY9a2bLRm"
         },
         "keyId":852264701,
         "outputPrefixType":"TINK",
         "status":"DISABLED"
      }
   ]
}

The following query converts json_keyset to a keyset and then returns the number of keys in the keyset:

SELECT KEYS.KEYSET_LENGTH(KEYS.KEYSET_FROM_JSON(json_keyset)) as key_count;

+-----------+
| key_count |
+-----------+
| 2         |
+-----------+