try_aes_decrypt function

Applies to: check marked yes Databricks Runtime 13.1 and above

Decrypts a binary produced using AES encryption and returns NULL if that fails for any reason.

Syntax 

try_aes_decrypt(expr, key [, mode [, padding]])

Arguments

  • expr: The BINARY expression to be decrypted.

  • key: A BINARY expression. Must match the key originally used to produce the encrypted value and be 16, 24, or 32 bytes long.

  • mode: An optional STRING expression describing the encryption mode used to produce the encrypted value.

  • padding: An optional STRING expression describing how encryption handled padding of the value to key length.

Returns

A BINARY.

mode must be one of (case-insensitive):

  • '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'.

  • 'DEFAULT': Uses 'NONE' for 'GCM' and 'PKCS' for 'ECB' mode.

The algorithm depends on the length of the key:

  • 16: AES-128

  • 24: AES-192

  • 32: AES-256

To raise errors instead of NULL in case of error conditions use try_aes_decrypt.

Examples 

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

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

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

> SELECT cast(try_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==

-- try_aes_decrypt tolerates an error where aes_decrypt does not.
> SELECT cast(aes_decrypt(x'1234567890abcdef1234567890abcdef', '1234567890abcdef', 'GCM') AS STRING);
  Error: INVALID_PARAMETER_VALUE.AES_KEY

> SELECT cast(try_aes_decrypt(x'1234567890abcdef1234567890abcdef', '1234567890abcdef', 'GCM') AS STRING);
  NULL