title: AES Encryption in Oracle and MySQL are giving different results
body: <p>I am in need to compare data between an Oracle database and a MySQL database.</p>
  
  <p>In Oracle, the data is first encrypted with the <code>AES-128</code> algorithm, and then hashed. Which means it is not possible to recover the data and decrypt it.</p>
  
  <p>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.</p>
  
  <p>After lots of tries, I finally found out that the <code>aes_encrypt</code> in MySQL returns different results than the one in Oracle.</p>
  
  <pre><code>-- 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);
  </code></pre>
  
  <p>The result for the oracle code will be: <code>8FCA326C25C8908446D28884394F2E22</code></p>
  
  <pre><code>-- MySQL
  -- While doing the same with MySQL, I have tried the following:
  SELECT hex(aes_encrypt('test-data', MD5('test_key'));
  </code></pre>
  
  <p>The result for the MySQL code will be: <code>DC7ACAC07F04BBE0ECEC6B6934CF79FE</code></p>
  
  <p>Am I missing something? Or are the encryption methods between different languages not the same?</p>
  
  <p><strong>UPDATE:</strong>
  According to the comments below, I believe I should mention the fact that the result of <code>DBMS_CRYPTO.Hash</code> in Oracle is the same as the result returned by the <code>MD5</code> function in MySQL.</p>
  
  <p>Also using <code>CBC</code> or <code>CBE</code> 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 <code>NULL</code></p>
  
  <p><strong>BOUNTY:</strong>
  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:</p>
  
  <blockquote>
    <p>@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)</p>
  </blockquote>