AES
AES_ENCRYPTβ
Nameβ
AES_ENCRYPT
descriptionβ
Encryption of data using the OpenSSL. This function is consistent with the AES_ENCRYPT
function in MySQL. Using AES_128_ECB algorithm by default, and the padding mode is PKCS7.
Reference: https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_aes-decrypt
Compatibilityβ
- aes_decrypt/aes_encrypt/sm4_decrypt/sm4_encrypt When the initial vector is not provided, block_encryption_mode will not take effect, and AES_128_ECB will be used for encryption and decryption in the end, which is inconsistent with the behavior of MySQL.
- Add aes_decrypt_v2/aes_encrypt_v2/sm4_decrypt_v2/sm4_encrypt_v2 functions to support correct behavior. When no initial vector is provided, block_encryption_mode can take effect, aes-192-ecb and aes-256-ecb will be correctly encrypted and decrypted, and other block encryption modes will report an error. If there is no need to be compatible with old data, the v2 function can be used directly.
Syntaxβ
AES_ENCRYPT(str, key_str[, init_vector])
Argumentsβ
str
: Content to be encryptedkey_str
: Secret keyinit_vector
: Initialization Vector. The default value for the block_encryption_mode system variable is aes ecb mode, which does not require an initialization vector. The alternative permitted block encryption modes CBC, CFB1, CFB8, CFB128, and OFB all require an initialization vector.
Return Typeβ
VARCHAR(*)
Remarksβ
The AES_ENCRYPT function is not used the user secret key directly, but will be further processed. The specific steps are as follows:
- Determine the number of bytes of the SECRET KEY according to the encryption algorithm used. For example, if you using AES_128_ECB, then the number of bytes of SECRET KEY are
128 / 8 = 16
(if using AES_256_ECB, then SECRET KEY length are128 / 8 = 32
); - Then XOR the
i
bit and the16*k+i
bit of the SECRET KEY entered by the user. If the length of the SECRET KEY less than 16 bytes, 0 will be padded; - Finally, use the newly generated key for encryption;
exampleβ
select to_base64(aes_encrypt('text','F3229A0B371ED2D9441B830D21A390C3'));
The results are consistent with those executed in MySQL.
+--------------------------------+
| to_base64(aes_encrypt('text')) |
+--------------------------------+
| wr2JEDVXzL9+2XtRhgIloA== |
+--------------------------------+
1 row in set (0.01 sec)
If you want to change other encryption algorithms, you can:
set block_encryption_mode="AES_256_CBC";
select to_base64(aes_encrypt('text','F3229A0B371ED2D9441B830D21A390C3', '0123456789'));
Here is the result:
+-----------------------------------------------------+
| to_base64(aes_encrypt('text', '***', '0123456789')) |
+-----------------------------------------------------+
| tsmK1HzbpnEdR2//WhO+MA== |
+-----------------------------------------------------+
1 row in set (0.01 sec)
For more information about block_encryption_mode
, see also variables.
keywordsβ
AES_ENCRYPT
AES_DECRYPTβ
Nameβ
AES_DECRYPT
Descriptionβ
Decryption of data using the OpenSSL. This function is consistent with the AES_DECRYPT
function in MySQL. Using AES_128_ECB algorithm by default, and the padding mode is PKCS7.
Syntaxβ
AES_DECRYPT(str,key_str[,init_vector])
Argumentsβ
str
: Content that encryptedkey_str
: Secret keyinit_vector
: Initialization Vector
Return Typeβ
VARCHAR(*)
exampleβ
select aes_decrypt(from_base64('wr2JEDVXzL9+2XtRhgIloA=='),'F3229A0B371ED2D9441B830D21A390C3');
The results are consistent with those executed in MySQL.
+------------------------------------------------------+
| aes_decrypt(from_base64('wr2JEDVXzL9+2XtRhgIloA==')) |
+------------------------------------------------------+
| text |
+------------------------------------------------------+
1 row in set (0.01 sec)
If you want to change other encryption algorithms, you can:
set block_encryption_mode="AES_256_CBC";
select aes_decrypt(from_base64('tsmK1HzbpnEdR2//WhO+MA=='),'F3229A0B371ED2D9441B830D21A390C3', '0123456789');
Here is the result:
+---------------------------------------------------------------------------+
| aes_decrypt(from_base64('tsmK1HzbpnEdR2//WhO+MA=='), '***', '0123456789') |
+---------------------------------------------------------------------------+
| text |
+---------------------------------------------------------------------------+
1 row in set (0.01 sec)
For more information about block_encryption_mode
, see also variables.
keywordsβ
AES_DECRYPT