Using mysql AES encrypt and decrypt from Django

Question

In my Django app, I want to use AES_ENCRYPT and AES_DECRYPT from the mysql database to encrypt and decrypt stuff.
I know that python's Crypto package has AES support but the Crypto AES doesn't produce the same result as mysql AES although I made sure that both are using the ECB mode.
So, now I'm doing this:

sql = "select 1 as id, AES_ENCRYPT(my_field, '16-bytes encryption key') as field_enc from appname_table"
encrypted_fields = MyModel.objects.raw(sql)

This gets me the field values after encryption and it works fine. The issue is that the AES algorithm encryption final result has many unprintable characters, it looks like this:

encrypted_fields[3].field_enc
'\x88\xc5\xe4\xa0c?\xf8\x16|^1JB\x83{\xdf'
print(encrypted_fields[3].field_enc)
���c?�|^1JB�{�

So, now when I try to take this same value to decrypt it, the mysql replies with an error that says

"OperationalError: (1300, "Invalid utf8 character string: '\x88\xC5\xE4\xA0c'")"

I guess this is because I tried to send unprintable characters to the mysql query.

So, how do I approach this?
Please note that I have to use the MySQL functions because I encrypted some database fields using these functions so I need to decrypt them with the same function because Crypto's AES doesn't get the same results. Crypto keeps asking that the text to be encrypted has to be 16 bytes long like the key, while mysql doesn't require that.

I also tried to see if mysql use some kind of padding for the text before encryption but it doesn't say what characters they pad on their webpage:

The str and crypt_str arguments can be any length, and padding is automatically added to str so it is a multiple of a block as required by block-based algorithms such as AES. This padding is automatically removed by the AES_DECRYPT() function

score 0 · Accepted Answer · answered Apr 23 '18 at 13:16

After many googles, I found this blog article about replicating mysql AES Encryption in PHP.
They key turned out to be that the character used for padding is the character that has an ascii code equal to the difference in length between the AES block size and the length of the text.

c = chr(16 - len(text_to_be_encrypted))
def align_str(s, n, char):
    if len(s) < n:
        diff = n - len(s)
        for i in range(diff):
            s += char
    return s
s = align_str(text_to_be_encrypted, 16, c)
e = encryption_suite.encrypt(s)

and it worked. It produced the same output as mysql encryption.

score 0 · Answer 2 · answered Jan 28 '21 at 07:15

In this article about replicating mysql AES Encryption in PHP, i found out that mysql uses ECB mode for AES encryption. Pycrypto library provides ECB mode for AES encryption.According to pycrypto documentation about ECB, the input must be aligned to the right boundary and its size must be in multiples of block_size (i.e. 16 bytes for AES). A function in pycryptodome library 'pad' serves the purpose here but it requires byte-string as input.

The code provided below gives the same results of AES encryption in python language as you get with its mysql counterpart.

from Crypto.Cipher import AES
from Crypto.Util.Padding import pad
def aes_encrypt(data):
    key = b'1234567812345678' # 16 byte key
    obj = AES.new(key, AES.MODE_ECB)
    padded_data = pad(bytes(data,'utf-8'),16)   
    encrypted_data = obj.encrypt(padded_data)
    return encrypted_data

Using mysql AES encrypt and decrypt from Django

2 Answers2