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?

  • 1
    What is your underlying goal? Is it critical that you replicate what MySQL does? Or would you rather do it in a secure way? See discussion below. – nealmcb Jul 16 '11 at 23:34
  • nope it is just to mimic the internal mysql process. – joecks Jul 19 '11 at 16:01

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]

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.

  • 4
    Oh dear - that seems like an awful solution, since it reduces the keyspace. Pretty typical for mysql though.... You want to use a proper key derivation algorithm instead - see Key derivation function - Wikipedia – nealmcb Jul 6 '11 at 15:13
  • 1
    Also note this text from your link: "Please take into consideration that security is definitely not the goal, talking to that system properly is." So unless you simply can't convince folks to do security properly, I'd suggest just rejecting the way MySQL does it, the same way you should reject all the ways that MySQL deals with passwords: Looking for example of well-known app using unsalted hashes - IT Security - Stack Exchange – nealmcb Jul 16 '11 at 23:33

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.

  • Yep that was my concern in first place, that I would like to use an arbitrary key and I tried much longer keys, yielding valid encrypted data, which is only decrypted by that long key (I also tried to truncated it). – joecks Jun 28 '11 at 15:35
  • Actually I found a solution, but I can not post it now... maybe tomorrow :'( – joecks Jun 28 '11 at 15:49
  • @M'vy, no, hashing the key material before passing it to AES_ENCRYPT() does not reduce security. Your statement "you will get less security" is not accurate. The reason is that a good crypto hash function (e.g., SHA1, SHA256) is collision-resistant: while collisions exist, it is computationally infeasible to find them, so the hash function is basically indistinguishable from a surjection for a computationally-limited attacker. (Minor complication: MD5's collision-resistance is broken, so this proof doesn't apply; but I still don't know of any weakness in MD5-hashing the key material.) – D.W. Jul 7 '11 at 4:39
  • Well, hashes function are not meant to build password, so collisions does not really interest us. And usually hashes algorithm does not prove that you can always obtain all hashes value ($2^{n} -1$ for n length hashes length). Indeed less word is really a subjective as I said. Should be surrounded by big double quotes. Well if we have such proof or surjectivity, I'd be interested to see this. So "less" just mean that if you use hashes for password the cardinal of codomain is less or equal to the $2^{n} -1$ of all possible values.But indeed, it can be equal. Thanks for this good comment. – M'vy Jul 7 '11 at 7:06

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


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:

    $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) );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.