MySQL AES_ENCRYPT in Python

nov 15, 2012 - Joeri - encryption - aes - mysql - python - Development - Software

MySQL features a function called AES_ENCRYPT(str, keystr) which basically performs - you guessed it - AES encryption over your value str using your key keystr. For example:

SELECT AES_ENCRYPT("my_secret_data", "my_key");

Mimicking MySQL's AES_ENCRYPT to Python is not as straightforward as it might seem. MySQL has its own tweaks on the parameters and AES mode of its encryption function.

I'm not going in on whether you should or should not use AES_ENCRYPT, or mimic its behavior in Python. Just let me say, if you can avoid it, avoid it :)

AES

Let's start with getting the PyCrypto library which features AES encryption utilities for Python.

Transforming the key

The first thing you need to know is that MySQL's AES_ENCRYPT uses AES mode ECB. This means 2 things: ECB mode does not use an initialization vector (IV), and the strings must be padded to the block size of the cipher.

Furthermore, on the MySQL website: "We chose 128 bits because it is much faster and it is secure enough for most purposes". This means the final key must be 16 bytes.

MySQL creates such a final key from any length key by starting with 16-byte block of null characters. They break our key up in 16-byte blocks and XORs them together.

def mysql_aes_key(key):
    final_key = bytearray(16) # Start with 16-byte block of null characters.
    for i, c in enumerate(key): # Iterate over all characters in our key.
        final_key[i%16] ^= ord(key[i]) # XOR the characters together.
    return bytes(final_key) # Returns the resulting byte string.

Transforming the value

From the PyCrypt documentation, the value needs to be padded to be a multiple of the block size, which is 16. The standard way is to pad the key with the byte value equal to the number of bytes left over. So, if our value is 31 characters, then we would pad with a byte value of 1.

def mysql_aes_val(val):
    pad_value = 16 - (len(val) % 16)
    return '%s%s' % (val, chr(pad_value)*pad_value)

Finally

Mixing it together, it's actually not that much code.

from Crypto.Cipher import AES
from Crypto import Random

def mysql_aes_encrypt(val, key):

    def mysql_aes_key(key):
        final_key = bytearray(16)
        for i, c in enumerate(key):
            final_key[i%16] ^= ord(key[i])
        return bytes(final_key)

    def mysql_aes_val(val):
        pad_value = 16 - (len(val) % 16)
        return '%s%s' % (val, chr(pad_value)*pad_value)

    k = mysql_aes_key(key)
    v = mysql_aes_val(val)

    cipher = AES.new(k, AES.MODE_ECB)

    return cipher.encrypt(v)

Have fun.



Ukraine x 2, India x 1, Netherlands x 4, Canada x 1

If you found this post useful, funny or whatever, let us know without writing a full comment!

comments powered by Disqus

Latest Tweets