Why do AES-256-CBC encryption results differ between MariaDB and SCV Crypto Manager?

I am encountering a discrepancy between the AES-256-CBC encryption results generated by MariaDB and SCV Crypto Manager. Below are the details of my setup and the steps I followed:

Encryption Parameters Key: A 256-bit key generated using SHA2() in MariaDB. IV (Initialization Vector): A 16-byte vector, converted from Base64 to binary. Data: Plaintext input that I want to encrypt.

SET block_encryption_mode = 'aes-256-cbc';


SET @key = SHA2('JImgFdqERWjqyDaAwxIER0KbThoWV7GEiwSr0d6J2IQ=', 256); 
SET @iv = UNHEX('557169695444544577516D6C4B30344A'); 


SET @plain_data = 'KTC';
SET @encrypted_data = UNHEX('116A82BE96082F6188F448B2FA49717B'); 

SELECT 
    HEX(AES_ENCRYPT(@plain_data, @key, @iv)) AS encrypted_hex, -- ENC
    AES_DECRYPT(@encrypted_data, @key, @iv) AS decrypted_text, -- DEC
    (@key) AS encryption_key_hex,                          -- KEY
    (@iv) AS initialization_vector_hex;                    -- IV
Output:

+----------------------------------+----------------+------------------------------------------------------------------+---------------------------+
| encrypted_hex                    | decrypted_text | encryption_key_hex                                               | initialization_vector_hex |
+----------------------------------+----------------+------------------------------------------------------------------+---------------------------+
| 116A82BE96082F6188F448B2FA49717B | KTC            | db31652dbe04f6187e79cfb9c9bffb8e560a5a8eeec687e4c37a12815c4affbd | UqiiTDTEwQmlK04J          |
+----------------------------------+----------------+------------------------------------------------------------------+---------------------------+

And I got this result below using SCV CryptoManager

Input : 11 6A 82 BE 96 08 2F 61 88 F4 48 B2 FA 49 71 7B
Modes : CBC
Key : db 31 65 2d be 04 f6 18 7e 79 cf b9 c9 bf fb 8e 56 0a 5a 8e ee c6 87 e4 c3 7a 12 81 5c 4a ff bd
IV : 55 71 69 69 54 44 54 45 77 51 6D 6C 4B 30 34 4A

AES mode CBC deciphering :

6A C6 0B FB D5 72 10 6D A4 EC B2 08 0D C1 32 F0

Which is decoded to ascii is jÆûÕrm¤ì²Á2ð

So I am literally dying to know Why are the encryption results different between MariaDB and SCV Crypto Manager, even though the key, IV, and data are the same?

Solution

It is highly recommended to use always a key with the required key length:

128 bits (16 bytes) for CBC/ECB/CTR-128
192 bits (24 bytes) for CBC/ECB/CTR-192
256 bits (32 byzes) for CBC/ECB/CTR-256

Unfortunately MariaDB (but also MySQL) doesn't complain about invalid key lengths but does the following:

If the key length is shorter than the required key length, it is filled up with zeros (0x0):

select @@block_encryption_mode;
+-------------------------+
| @@block_encryption_mode |
+-------------------------+
| aes-128-cbc             |
+-------------------------+

set @iv=0x3DAFBA429D9EB430B422DA802C9FAC41,
    @key1=0x06A9214036B8A15B512E03D534120000,
    @key2=0x06A9214036B8A15B512E03D53412;

select aes_encrypt("Test", @key1, @iv) = aes_encrypt("Test", @key2, @iv);
+-------------------------------------------------------------------+
| aes_encrypt("Test", @key1, @iv) = aes_encrypt("Test", @key2, @iv) |
+-------------------------------------------------------------------+
|                                                                 1 |
+-------------------------------------------------------------------+

If the key length is higher than the required key length, MariaDB xores the key with the redundant bytes, while other implementations truncate the key which might be the reason for different results.

#due xor operation with redundant bytes keys will be the same
set @key1=REPEAT("A",32), @key2=REPEAT("B",32);
select aes_encrypt("Test", @key1, @iv) = aes_encrypt("Test", @key2, @iv);
+-------------------------------------------------------------------+
| aes_encrypt("Test", @key1, @iv) = aes_encrypt("Test", @key2, @iv) |
+-------------------------------------------------------------------+
|                                                                 1 |
+-------------------------------------------------------------------+