AES_DECRYPT
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_DECRYPT(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 iskey
, thenkey[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
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.
Example
select aes_decrypt(from_base64('wr2JEDVXzL9+2XtRhgIloA=='),'F3229A0B371ED2D9441B830D21A390C3');
+------------------------------------------------------+
| aes_decrypt(from_base64('wr2JEDVXzL9+2XtRhgIloA==')) |
+------------------------------------------------------+
| text |
+------------------------------------------------------+
1 row in set (0.01 sec)
If want to use other encryption algorithms, you can:
set block_encryption_mode="AES_256_CBC";
select AES_DECRYPT(FROM_BASE64('tsmK1HzbpnEdR2//WhO+MA=='),'F3229A0B371ED2D9441B830D21A390C3', '0123456789');
+---------------------------------------------------------------------------+
| aes_decrypt(from_base64('tsmK1HzbpnEdR2//WhO+MA=='), '***', '0123456789') |
+---------------------------------------------------------------------------+
| text |
+---------------------------------------------------------------------------+
1 row in set (0.01 sec)
Keywords
AES_DECRYPT, AES, DECRYPT