Just wanted to give a complete solution for mannequins based on the very didactic answer of @Codo.
EDIT: To be precise in general cases, I found the following: - "PKCS # 5 padding is a subset of the PKCS # 7 padding for 8-byte block sizes." Therefore, strictly PKCS5 cannot be applied to AES; they mean PKCS7, but use their names interchangeably.
About PKCS5 and PKCS7
/ * MySQL uses a non-standard folding key. * Thus, in order to achieve the same result in MySQL and Oracle (or .NET or Java), use only keys with a length of 16 bytes (32 hexadecimal characters) = 128 bits AES, MySQL AES_encrypt by default. * * This means that MySQL allows any key length from 16 to 32 bytes for 128-bit AES encryption, but it is not allowed by the AES standard to use a non-16 byte key, so do not use it, since you cannot use the standard AES- decryption on another platform for keys with more than 16 bytes, and it would be required to program the folding of the MySQL key in this other platform, with XOR materials, etc. (he is already there, but why do weird, non-standard things thay may change when choosing MySQL, etc.). Moreover, I think they say that the algorithm chosen by MySQL for those cases is really poorly chosen at the security level ... * /
- ### ORACLE:
- First, the key is hashed with md5 to make it a 128-bit key (16 bytes, 32 hexadecimal characters):
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 encryption settings:
encryption_type:= DBMS_CRYPTO.ENCRYPT_AES128 + DBMS_CRYPTO.CHAIN_ECB + DBMS_CRYPTO.PAD_PKCS5;
- Strictly speaking, this is really PKCS7.
/ * And I choose ECB in order to be faster if it is applied, and @Codo said that it is correct, but as a standard (Oracle) AES128 will only accept 16 bytes, CBC also works, since I believe that they do not apply to a key with 16 bytes. Can anyone confirm this? * /
- Then the data is encrypted using AES:
encrypted_result := DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW('test-data', 'AL32UTF8'), encryption_type, raw_key);
- The result is binary (varbinary, blob).
- You can use RAWTOHEX () if you want to represent it in hexadecimal characters.
In case you use a directly hashed hash phrase with a hexadecimal character in the hexadecimal representation of characters or 32 hexadecimal random characters:
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 )
- Decoding ORACLE:
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:
- The MySQL MD5 function returns a string of 32 hexadecimal characters (= 16 bytes = 128 bits).
“It is marked as a binary string, but it is not the 16-bit binary data that you would expect.”
- NOTE. Please note that the return type of functions MD5, SHA1, etc. changed in some versions from 5.3.x. See the MySQL 5.7 manual.
- To fix this, this string should be converted back from hexadecimal to binary data using unHex ():
SELECT hex(aes_encrypt('test-data', unhex(MD5('test_key')));
PS: I would recommend reading the improved explanation in MySQL 5.7 Manual, which now allows a lot more configuration. MySQL AES_ENCRYPT improved explanation from manual v5.7