Description

AES decryption function. This function behaves like the AES_DECRYPT function in MySQL. By default, it uses the AES_128_ECB algorithm with PKCS7 padding mode. The underlying decryption is done using the OpenSSL library.

Syntax

VARCHAR AES_ENCRYPT(VARCHAR str, VARCHAR key_str[, VARCHAR init_vector][, VARCHAR encryption_mode])

Returns the decrypted result, where:

• str is the text to be decrypted;
• key_str is the key. Note that this key is not a hexadecimal encoding, but a string representation of the encoded key. For example, for 128-bit key encryption, key_str should be 16-length. If the key is not long enough, use zero padding to make it up. If it is longer than that, the final key is found using a cyclic xor method. For example, if the 128-bit key used by the algorithm finally is key, then key[i] = key_str[i] ^ key_str[i+128] ^ key_str[i+256] ^ ...
• init_vector is the initial vector to be used in the algorithm, this is only valid for some algorithms, if not specified then Doris will use the built-in value;
• encryption_mode is the encryption algorithm, optionally available in variables。

danger

Until 3.0.2, function with two arguments will ignore session variable block_encryption_mode and always use AES_128_ECB to do decryption. So it’s not recommended to use it.

Since 3.0.3, it works as expected.

Example

mysql> set block_encryption_mode='';
Query OK, 0 rows affected (0.10 sec)
mysql> select aes_decrypt(from_base64('wr2JEDVXzL9+2XtRhgIloA=='),'F3229A0B371ED2D9441B830D21A390C3');
+--------------------------------------------------------------------------------+
| aes_decrypt(from_base64('wr2JEDVXzL9+2XtRhgIloA=='), '***', '', 'AES_128_ECB') |
+--------------------------------------------------------------------------------+
| text                                                                           |
+--------------------------------------------------------------------------------+
1 row in set (0.14 sec)
mysql> set block_encryption_mode="AES_256_CBC";
Query OK, 0 rows affected (0.10 sec)
mysql> select aes_decrypt(from_base64('3dym0E7/+1zbrLIaBVNHSw=='),'F3229A0B371ED2D9441B830D21A390C3'); -- since 3.0.3
+--------------------------------------------------------------------------------+
| aes_decrypt(from_base64('3dym0E7/+1zbrLIaBVNHSw=='), '***', '', 'AES_256_CBC') |
+--------------------------------------------------------------------------------+
| text                                                                           |
+--------------------------------------------------------------------------------+
1 row in set (0.12 sec)
mysql> select AES_DECRYPT(FROM_BASE64('tsmK1HzbpnEdR2//WhO+MA=='),'F3229A0B371ED2D9441B830D21A390C3', '0123456789');
+------------------------------------------------------------------------------------------+
| aes_decrypt(from_base64('tsmK1HzbpnEdR2//WhO+MA=='), '***', '0123456789', 'AES_256_CBC') |
+------------------------------------------------------------------------------------------+
| text                                                                                     |
+------------------------------------------------------------------------------------------+
1 row in set (0.12 sec)

Keywords

AES_DECRYPT, AES, DECRYPT