AES Encryption in Oracle and MySQL are giving different results

Question

I am in need to compare data between an Oracle database and a MySQL database.

In Oracle, the data is first encrypted with the AES-128 algorithm, and then hashed. Which means it is not possible to recover the data and decrypt it.

The same data is available in MySQL, and in plain text. So to compare the data, I tried encrypting and then hashing the MySQL data while following the same steps done in Oracle.

After lots of tries, I finally found out that the aes_encrypt in MySQL returns different results than the one in Oracle.

-- ORACLE:
-- First the key is hashed with md5 to make it a 128bit key:
raw_key := DBMS_CRYPTO.Hash (UTL_I18N.STRING_TO_RAW ('test_key', 'AL32UTF8'), DBMS_CRYPTO.HASH_MD5);

-- Initialize the encrypted result
encryption_type:= DBMS_CRYPTO.ENCRYPT_AES128 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;

-- Then the data is being encrypted with AES:
encrypted_result := DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW('test-data', 'AL32UTF8'), encryption_type, raw_key);

The result for the oracle code will be: 8FCA326C25C8908446D28884394F2E22

-- MySQL
-- While doing the same with MySQL, I have tried the following:
SELECT hex(aes_encrypt('test-data', MD5('test_key'));

The result for the MySQL code will be: DC7ACAC07F04BBE0ECEC6B6934CF79FE

Am I missing something? Or are the encryption methods between different languages not the same?

UPDATE: According to the comments below, I believe I should mention the fact that the result of DBMS_CRYPTO.Hash in Oracle is the same as the result returned by the MD5 function in MySQL.

Also using CBC or CBE in Oracle gives the same result, since the IV isn't being passed to the function, thus the default value of the IV is used which is NULL

BOUNTY: If someone can verify my last comment, and the fact that if using same padding on both sides, will yield same results gets the bounty:

@rossum The default padding in MySQL is PKCS7, mmm... Oh.. In Oracle it's using PKCS5, can't believe I didn't notice that. Thanks. (Btw Oracle doesn't have the PAD_PKCS7 option, not in 11g at least)

Answer 1

MySQL's MD5 function returns a string of 32 hexadecimal characters. It's marked as a binary string but it isn't the 16 byte binary data one would expect.

So to fix it, this string must be converted back to the binary data:

SELECT hex(aes_encrypt('test-data', unhex(MD5('test_key'))));

The result is:

8FCA326C25C8908446D28884394F2E22

It's again a string of 32 hexadecimal characters. But otherwise it's the same result as with Oracle.

And BTW:

• MySQL uses PKCS7 padding.
• PKCS5 padding and PKCS7 padding are one and the same. So the Oracle padding option is correct.
• MySQL uses ECB block cipher mode. So you'll have to adapt the code accordingly. (It doesn't make any difference for the first 16 bytes.)
• MySQL uses no initialization vector (the same as your Oracle code).
• MySQL uses a non-standard folding a keys. So to achieve the same result in MySQL and Oracle (or .NET or Java), only use keys that are 16 byte long.

Answer 2

Just would like to give the complete solution for dummies based on @Codo's very didactic answer.

EDIT: For being exact in general cases, I found this: - "PKCS#5 padding is a subset of PKCS#7 padding for 8 byte block sizes". So strictly PKCS5 can't be applied to AES; they mean PKCS7 but use their names interchangeably.

About PKCS5 and PKCS7

/* MySQL uses a non-standard folding a key. * So to achieve the same result in MySQL and Oracle (or .NET or Java), only use keys that are 16 bytes long (32 hexadecimal symbols) = 128 bits AES encryption, the MySQL AES_encrypt default one. * * This means MySQL admits any key length between 16 and 32 bytes for 128 bits AES encryption, but it's not allowed by the standard AES to use a non-16 bytes key, so do not use it as you won't be able to use the standard AES decrypt in other platform for keys with more than 16 bytes, and would be obligued to program the MySQL folding of the key in that other platform, with the XOR stuff, etc. (it's already out there but why doing weird non-standard things thay may change when MySQL decide, etc.). Moreover, I think they say the algorithm chosen by MySQL for those cases is a really bad choose on a security level... */

-- ### ORACLE:

-- First the key is hashed with md5 to make it a 128 bit key (16 bytes, 32 hex symbols):

raw_key := DBMS_CRYPTO.Hash (UTL_I18N.STRING_TO_RAW ('test_key', 'AL32UTF8'), DBMS_CRYPTO.HASH_MD5); 

-- MySQL uses AL32UTF8, at least by default

-- Configure the encryption parameters:

encryption_type:= DBMS_CRYPTO.ENCRYPT_AES128 + DBMS_CRYPTO.CHAIN_ECB + DBMS_CRYPTO.PAD_PKCS5; 

-- Strictly speaking, it's really PKCS7.

/* And I choose ECB for being faster if applied and @Codo said it's the correct one, but as standard (Oracle) AES128 will only accept 16 bytes keys, CBC also works, as I believe they are not applied to a 16 bytes key. Could someone confirm this? */

-- Then the data is encrypted with AES:

encrypted_result := DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW('test-data', 'AL32UTF8'), encryption_type, raw_key);

-- The result is binary (varbinary, blob).

-- One can use RAWTOHEX() for if you want to represent it in hex characters.

In case you use directly the 16 bytes hashed passphrase in hex characters representation or 32 hex random chars:

raw_key := HEXTORAW(32_hex_key)
encryption_type := 6 + 768 + 4096 -- (same as above in numbers; see Oracle Docum.) 
raw_data := UTL_I18N.STRING_TO_RAW('test-data', 'AL32UTF8')

encrypted_result := DBMS_CRYPTO.ENCRYPT( raw_data, encryption_type, raw_key )

-- ORACLE Decryption:

decrypted_result := UTL_I18N.RAW_TO_CHAR( CRYPTO.DECRYPT( raw_data, encryption_type, raw_key ), 'AL32UTF8' )

-- In SQL:

SELECT 
  UTL_I18N.RAW_TO_CHAR( 
    DBMS_CRYPTO.DECRYPT( 
    UTL_I18N.STRING_TO_RAW('test-data', 'AL32UTF8'), 
    6 + 768 + 4096, 
    HEXTORAW(32_hex_key) 
  ) , 'AL32UTF8') as "decrypted" 
FROM DUAL;

-- ### MySQL decryption:

-- MySQL's MD5 function returns a string of 32 hexadecimal characters (=16 bytes=128 bits).

-- It's marked as a binary string but it isn't the 16 bytes binary data one would expect.

-- NOTE: Note that the kind of return of MD5, SHA1, etc functions changed in some versions since 5.3.x. See MySQL 5.7 manual.

-- So to fix it, this string must be converted back from hex to binary data with unHex():

SELECT hex(aes_encrypt('test-data', unhex(MD5('test_key')));

P.S.: I would recommend to read the improved explanation in MySQL 5.7 Manual, which moreover now allows a lot more configuration. MySQL AES_ENCRYPT improved explanation from v5.7 manual