aes_encrypt function

  • Article

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 10.4 LTS and above

Encrypts a binary using AES encryption.

Syntax

aes_encrypt(expr, key [, mode [, padding[, iv[, aad]]]])

Arguments

  • expr: The BINARY expression to be encrypted.
  • key: A BINARY expression. The key to be used to encrypt expr. It must be 16, 24, or 32 bytes long.
  • mode: An optional STRING expression describing the encryption mode.
  • padding: An optional STRING expression describing how encryption handles padding of the value to key length.
  • iv: An optional STRING expression providing an initialization vector (IV) for GCM or CBC modes. Applies to Databricks SQL and Databricks Runtime 13.3 LTS and above.
  • aad: An optional STRING expression providing authenticated additional data (AAD) in GCM mode. Applies to Databricks SQL and Databricks Runtime 13.3 LTS and above.

Returns

A BINARY.

mode must be one of (case-insensitive):

  • 'CBC': Use Cipher-Block Chaining (CBC) mode. Applies to Databricks SQL, Databricks Runtime 13.3 LTS and above.
  • 'ECB': Use Electronic CodeBook (ECB) mode.
  • 'GCM': Use Galois/Counter Mode (GCM). This is the default.

padding must be one of (case-insensitive):

  • 'NONE': Uses no padding. Valid only for 'GCM'.
  • 'PKCS': Uses Public Key Cryptography Standards (PKCS) padding. Valid only for 'ECB' and 'CBC'. PKCS padding adds between 1 and key-length number of bytes to pad expr to a multiple of key length. The value of each pad byte is the number of bytes being padded.
  • 'DEFAULT': Uses 'NONE' for 'GCM' and 'PKCS' for 'ECB', and 'CBC' mode.

iv, when specified, must be 12-bytes long for GCM and 16 bytes for CBC.

The algorithm depends on the length of the key:

  • 16: AES-128
  • 24: AES-192
  • 32: AES-256

Examples

> SELECT base64(aes_encrypt('Spark', 'abcdefghijklmnop'));
  4A5jOAh9FNGwoMeuJukfllrLdHEZxA2DyuSQAWz77dfn

> SELECT cast(aes_decrypt(unbase64('4A5jOAh9FNGwoMeuJukfllrLdHEZxA2DyuSQAWz77dfn'),
                          'abcdefghijklmnop') AS STRING);
  Spark

> SELECT base64(aes_encrypt('Spark SQL', '1234567890abcdef', 'ECB', 'PKCS'));
  3lmwu+Mw0H3fi5NDvcu9lg==

> SELECT cast(aes_decrypt(unbase64('3lmwu+Mw0H3fi5NDvcu9lg=='),
                          '1234567890abcdef', 'ECB', 'PKCS') AS STRING);
  Spark SQL

> SELECT base64(aes_encrypt('Spark SQL', '1234567890abcdef', 'GCM'));
  2sXi+jZd/ws+qFC1Tnzvvde5lz+8Haryz9HHBiyrVohXUG7LHA==

> SELECT cast(aes_decrypt(unbase64('2sXi+jZd/ws+qFC1Tnzvvde5lz+8Haryz9HHBiyrVohXUG7LHA=='),
                          '1234567890abcdef', 'GCM') AS STRING);
  Spark SQL

> SELECT base64(aes_encrypt('Spark SQL', '1234567890abcdef', 'GCM', 'DEFAULT', '123456789012', 'Some AAD'));
  MTIzNDU2Nzg5MDEyMdXvR41sJqwZ6hnTU8FRTTtXbL8yeChIZA==

> SELECT cast(aes_decrypt(unbase64('MTIzNDU2Nzg5MDEyMdXvR41sJqwZ6hnTU8FRTTtXbL8yeChIZA=='),
                          '1234567890abcdef', 'GCM', 'DEFAULT', 'Some AAD') AS STRING);
  Spark SQL