MySQL 8.2 AES_ENCRYPT IV is not affecting the results

Question

I using XAMPP with MySQL 8.2 and I have statements like this:

UPDATE Table SET `column` = TO_BASE64(AES_ENCRYPT('plaintext','key',`IV`,'hkdf') ) WHERE  id = 1;
UPDATE Table SET `column` = TO_BASE64(AES_ENCRYPT('plaintext','key',`IV`,'hkdf') ) WHERE  id = 2;
UPDATE Table SET `column` = TO_BASE64(AES_ENCRYPT('plaintext','key',`IV`,'hkdf') ) WHERE  id = 3;

The IV is a unique data that already defined in the table. I am getting the same value for these statement (like these 3 statements all output: 123), found out that maybe the IV is not affecting the result.

And I tried to handmade IV but its getting the same result too.

UPDATE Table SET `column` = TO_BASE64(AES_ENCRYPT('plaintext','key','abc','hkdf') ) WHERE  id = 1;
UPDATE Table SET `column` = TO_BASE64(AES_ENCRYPT('plaintext','key','acb','hkdf') ) WHERE  id = 2;
UPDATE Table SET `column` = TO_BASE64(AES_ENCRYPT('plaintext','key','bca','hkdf') ) WHERE  id = 3;

But if I add the salt parameter after 'hkdf', the result will be affected by the salt.

UPDATE Table SET `column` = TO_BASE64(AES_ENCRYPT('plaintext','key',`IV`,'hkdf',`IV`) ) WHERE  id = 1;
UPDATE Table SET `column` = TO_BASE64(AES_ENCRYPT('plaintext','key',`IV`,'hkdf',`IV`) ) WHERE  id = 2;
UPDATE Table SET `column` = TO_BASE64(AES_ENCRYPT('plaintext','key',`IV`,'hkdf',`IV`) ) WHERE  id = 3;

Want to know why my IV parameter is not affecting the result? Or how can I make it affective? UPDATE Table SET `column` = TO_BASE64(AES_ENCRYPT('plaintext','key',`IV`,'hkdf') ) WHERE id = 1; Thank you!

Answer 1

Since you didn't mention which block-encryption-mode you're using, I assume it is the default, which is ECB (Electronic Codebook).

In contrast to other block encryption modes such as CBC or GCM, ECB does not require an initialization vector and therefore the passed value with the content of IV column will be ignored:

SELECT @@block_encryption_mode;
+-------------------------+
| @@block_encryption_mode |
| ----------------------- |
| aes-128-ecb             |
+-------------------------+

SET @a:=AES_ENCRYPT("mytext", @key, @iv);
SHOW WARNINGS;
+---------+------+---------------------+
| Level   | Code | Message             |
| ------- | ---- | ------------------- |
| Warning | 1618 | <IV> option ignored |
+---------+------+---------------------+