How to use implement AES_DECRYPT() of MySQL by Python

Question

I am trying to write a python code which has same functionarities of AES_ENCRYPT and AES_DECRYPT of MySQL. https://dev.mysql.com/doc/refman/5.6/ja/encryption-functions.html

I want to encrypt and decrypt data between MySQL and Python.

For example, I want to decrypt data by python, which is encrypted by AES_ENCRYPT of MySQL.

And I want to decrypt data by AES_DECRYPT of MySQL, which is encrypted by Python vice versa.

I found a example of AES_ENCRYPT in Python. https://www.maykinmedia.nl/blog/2012/nov/15/mysql-aes_encrypt-python/

Does anyone know how to implement the decryption part?

I'm not really sure what exactly you are after. The language of the question suggests that you want to use aes_encrypt() / aes_decrypt() functions provided by MySQL in python. In this case you just pass the parameters to the MySQL query and MySQL takes care of the encryption / decryption. But the linked example mimicks the said aes_encrypt() function within python, which has nothing to do with MySQL. So, which approach do you actually need? — Shadow
– Shadow, Commented Jul 2, 2018 at 11:13
You may edit your question to provide more details about your requirement. — O. Jones
– O. Jones, Commented Jul 2, 2018 at 11:34
Is there any reason for complicating your life? Stick to one or the other approach. Btw, the parameters used for encrypting the data should be used for decrypting as well. AES is a symmetric key encryption. — Shadow
– Shadow, Commented Jul 2, 2018 at 16:59
I have a sql file of MySQL, and it has some encrypted data. I want to read it by Python without MySQL. Yes. I know AES needs a key and I know the key used in MySQL. — Atsushi Sakai
– Atsushi Sakai, Commented Jul 2, 2018 at 23:13

Atsushi Sakai · Accepted Answer · 2018-07-06 12:12:46Z

5

I did it finally.

def mysql_aes_decrypt(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)

    k = mysql_aes_key(key)

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

    return cipher.decrypt(val).decode()

answered Jul 6, 2018 at 12:12

Atsushi Sakai

1,02411 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

pseudomonas Over a year ago

Hi, thanks for this code. I see one issue in the code when I use it. UnicodeDecodeError: 'ascii' codec can't decode byte 0xf3 in position 0: ordinal not in range(128). This error stems from the last line: return cipher.decrypt(val).decode(). If I just keep it as cipher.decrypt(val), there is no error, but the characters are not readable.

pythonian29033 Over a year ago

@Raghuram yup, that's because you haven't decoded it, the issue is with your value, read the link in the question first

Collectives™ on Stack Overflow

How to use implement AES_DECRYPT() of MySQL by Python

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related