MySQL Bugs: #109236: Mysql AES encrypt from 8.0.25 to 8.0.30

Bug #109236	Mysql AES encrypt from 8.0.25 to 8.0.30
Submitted:	29 Nov 2022 13:28	Modified:	4 Dec 2022 16:47
Reporter:	lokesh singhal	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.30	OS:	Linux ( 18.04.6 LTS)
Assigned to:		CPU Architecture:	x86

Description:
Hi Team,

We have upgrade MySQL from 8.0.25 to 8.0.30.

After upgrade we have stated facing issue with AES_encypt function.

Error :

AES key size should be 32 bytes length or secure KDF methods hkdf or pbkdf2_hmac should be used, please provide exact AES key size or use KDF methods for better security.

Please suggest on this.

How to repeat:
CREATE DEFINER=`root`@`localhost` FUNCTION `UDF_encrypt`(_value varchar(255)) RETURNS varbinary(255)
    DETERMINISTIC
begin

declare key_value varchar(100);
declare init_vactor varchar(32);
set key_value=sha2('esfdfdfdfdnce0d0111@asdaasas',256);
set init_vactor=md5(key_value);
return  aes_encrypt(_value, key_value,init_vactor);
end

it is working fine for sometimes but sometimes not.

Hi Mr. singhai,

Thank you for your bug report.

However, it is not a bug.

It is clearly documented change in the behaviour, as explained in our Release Notes:

MySQL Server’s AES_ENCRYPT() and AES_DECRYPT() functions now support the use of a key derivation function (KDF) to create a cryptographically strong secret key from information such as a password or a passphrase that you pass to the function. The derived key is used to encrypt and decrypt the data, and it remains in the MySQL Server instance and is not accessible to users. Using a KDF is highly recommended, as it provides better security than specifying your own premade key or deriving it by a simpler method when you use the function. The functions support HKDF (available from OpenSSL 1.1.0), for which you can specify an optional salt and context-specific information to include in the keying material, and PBKDF2 (available from OpenSSL 1.0.2), for which you can specify an optional salt and set the number of iterations used to produce the key.

Not a bug.

can you please suggest how to use it?

Do I need to decrypt my older data and encrypt again by changing encryption key.  
SHA2('My secret passphrase',256) to SHA2('My secret passphrase',512);

I have figure out  solution by modifying my function to decrypt my already encrypt .

Also update my encryption function decrypt new data with hkdf.

To Decrypt old and new data :

CREATE DEFINER=`root`@`localhost` FUNCTION `UDF_decrypt2`(_value varbinary(256)) RETURNS varchar(255) CHARSET utf8mb4
    DETERMINISTIC
begin
declare key_value varchar(100);
declare init_vactor varchar(32);
set key_value=sha2('testkey',256);
set init_vactor=md5(key_value);
return  
ifnull(convert(aes_decrypt(_value, key_value,init_vactor,'hkdf'),char),
convert(aes_decrypt(_value, key_value,init_vactor),char));
end

To Encrypt:

CREATE DEFINER=`root`@`localhost` FUNCTION `UDF_encrypt2`(_value varchar(255)) RETURNS varbinary(256)
    DETERMINISTIC
begin
declare key_value varchar(100);
declare init_vactor varchar(32);
set key_value=sha2('testkey',256);
set init_vactor=md5(key_value);
return  aes_encrypt(_value, key_value,init_vactor,'hkdf');
end