What is the proper way to store unicode bytes in MySQL?

Question

I am using mysql-connector and I would like to store some data in MySQL database. I apply an EAS encryption using PyCrypto which results a byte string like the following one:

encrypted_data = b'\xd5\x9e\xea \x8d\xc4\xa6P\x93>\xda\x045\xd6\xfa8'

What is the most efficient way to store encrypted_data in my database?

A thought is to convert the unicode byte to hex and store it as VARCHAR. Though in that case I would need twice as much storage space.

Another thought would be to encrypt it inside MySQL. Though because I also use other encryption and hashing functions in python, I would prefer to do all the encryption stuff in python, so that code is more organized and readable.

Answer 1

Regardless of whether you encrypted the data in Python or MySQL, encrypted data should be stored in a binary column.

Many encryption and compression functions return strings for which the result might contain arbitrary byte values. If you want to store these results, use a column with a VARBINARY or BLOB binary string data type. This will avoid potential problems with trailing space removal or character set conversion that would change data values, such as may occur if you use a nonbinary string data type (CHAR, VARCHAR, TEXT).

Small amounts of encrypted data should be stored as varbinary, this is analogous to varchar. Larger amounts of encrypted data should be stored as a blob which is the binary version of text.

MySQL has encryption and decryption functions, but you're limited to AES. If you're comfortable with encryption in Python, use Python. Just be sure to encrypt it as a binary data, not hex, and to store it in a binary column. That will be the most efficient and safest.