MySQL AES_ENCRYPT key length

Question

AES_ENCRYPT uses a 128 bit long key to encrypt the data, but how MySQL handle longer or shorter keys? I found out that PyCrypto for instances recomend to transform the key by using MD5, SHA-1, SHA-2, etc. hashes and then using the the resulting key for encryption. How does it work with MySQL?

Answer 1

MySQL 5.5 does not handle other key sizes. As stated on http://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html:

AES_ENCRYPT() and AES_DECRYPT() enable encryption and decryption of data using the official AES (Advanced Encryption Standard) algorithm, previously known as “Rijndael.” Encoding with a 128-bit key length is used, but you can extend it up to 256 bits by modifying the source. We chose 128 bits because it is much faster and it is secure enough for most purposes.

I never tried to pass bigger key to MySQL, but I guess it would yield an error or truncate to 128 bits.

I'm not sure if MD5 use it's full codomain because I can't find any proof of that MD5 is a surjection. So I assume that you will get less security by using MD5 hashes since you reduce the set of possible values for your keys. This is still very subjective.

As far as smaller keys are concerned, I guess that 0x1 == 0x00...01 so it is still a valid key.

By the way, I do not understand why you just don't generate key to the given size. If you have some constraints (already existing key you want to reuse, ...) let us know.

Answer 2

Sorry but I found that later from RubyForum

"The algorithm just creates a 16 byte buffer set to all zero, then loops through all the characters of the string you provide and does an assignment with bitwise XOR between the two values. If we iterate until we hit the end of the 16 byte buffer, we just start over from the beginning doing ^=. For strings shorter than 16 characters, we stop at the end of the string."

  bzero((char*) rkey,AES_KEY_LENGTH/8);      /* Set initial key  */

  for (ptr= rkey, sptr= key; sptr < key_end; ptr++,sptr++)
  {
    if (ptr == rkey_end)
      ptr= rkey;  /*  Just loop over tmp_key until we used all key */
    *ptr^= (uint8) *sptr;
  }

which looks like that in Ruby

def mysql_key2(key)
   final_key = "\0" * 16
   key.length.times do |i|
     final_key[i%16] ^= key[i]
   end
   final_key
end

and I applied that to python:

finalKey = b'\0'*16
key = b'mySecretKey'
for i, c in enumerate(key) :
  finalKey[i%16] ^= key[i]

It's probably wise not to use a repetitive password such as "MySQL=insecure! MySQL=insecure! " as it would zero out the resulting key.

Answer 3

By default these functions implement AES with a 128-bit key length. As of MySQL 5.7.4, key lengths of 196 or 256 bits can be used, as described later. The key length is a trade off between performance and security. encryption-functions

Answer 4

I had to do this in PHP. You need to do the XOR as described by @joecks and remove padding for any encrypted data shorter than 16 bytes as described here: http://forums.devnetwork.net/viewtopic.php?f=34&t=97082

Working code:

<?php
    $key = 'SomeSecretKeyThatCanBeLongerThan16Bytes';
    $encodedString = [fetched from DB];

    $finalKey = array_fill(0, 16, 0);
    foreach (unpack('C*', $key) as $i => $char)
    {
        $finalKey[($i-1)%16] = $finalKey[($i-1)%16] ^ $char;
    }

    $dec = mcrypt_decrypt(MCRYPT_RIJNDAEL_128, implode(array_map("chr", $finalKey)), $encodedString, MCRYPT_MODE_ECB, '');
    return rtrim($dec, ((ord(substr($dec, strlen($dec) - 1, 1)) >= 0 and ord(substr($dec, strlen($dec) - 1, 1 ) ) <= 16 ) ? chr(ord(substr($dec, strlen($dec ) - 1, 1))): null) );