aes_decrypt
function
Applies to: Databricks SQL
Databricks Runtime 10.4 LTS and above
Decrypts a binary produced using AES encryption.
Syntax
aes_decrypt(expr, key [, mode [, padding [, aad]]])
Arguments
expr
: TheBINARY
expression to be decrypted.key
: ABINARY
expression. Must match the key originally used to produce the encrypted value and be 16, 24, or 32 bytes long.mode
: An optionalSTRING
expression describing the encryption mode used to produce the encrypted value.padding
: An optionalSTRING
expression describing how encryption handled padding of the value to key length.aad
: An optionalSTRING
expression providing authenticated additional data (AAD) inGCM
mode. Must match theaad
used to produce the encrypted value. 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'
.'DEFAULT'
: Uses'NONE'
for'GCM'
and'PKCS'
for'ECB'
and'CBC'
mode.
The algorithm depends on the length of the key:
16
: AES-12824
: AES-19232
: AES-256
To tolerate any error conditions resulting from decryption and return NULL
instead use try_aes_decrypt
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
-- 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
> SELECT base64(aes_encrypt('Apache Spark', '0000111122223333', 'CBC', 'PKCS'));
U2FsdGVkX1/ERGxwEOTDpDD4bQvDtQaNe+gXGudCcUk=
> SELECT cast(aes_decrypt(unbase64('OkzJi9oaiKJtTMmOrFjH2QWJZYF1UwT+4cA2008LlHA='), '0000111122223333', 'CBC', 'PKCS') AS STRING);
Apache Spark
> 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
Related functions
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for