I need to store very important information, and for greater security, I decided to use the built-in MySQL function "AES_ENCRYPT", but while saving data in the table, I get the following error:
mysql.connector.errors.DataError: 1366 (22007):
Incorrect string value: '\xFE\xC5\x0A\xA3}\xCE...' for column `database`.`table`.`token` at row 7
my code:
key = hashlib.sha256(str(guild.owner_id).encode('utf-8')).hexdigest()
update = f'''UPDATE `table` SET token = AES_ENCRYPT('{token}', '{key}'), WHERE id = {guild.id}'''
# guild.id and guild.owner_id there are int values
cursor.execute(update)
connection.commit()
How to fix it?
From the MySQL Reference Manual 5.7:
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 avoids 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).
If you use column types VARCHAR or TEXT, this may occur. So you should change column token from VARCHAR to VARBINARY or use HEX(AES_ENCRYPT(token, key)) to store as string