Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – I can't save the data encrypted with the AES_ENCRYPT function

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: 'xFExC5x0AxA3}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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    Using prepared statements separates the data from the (sql) code and as such, the data no longer requires to conform the the connection character set. This also makes it immune from SQL injection:

    key = hashlib.sha256(str(guild.owner_id).encode('utf-8')).hexdigest()
update = "UPDATE `table` SET token = AES_ENCRYPT(%s, %s), WHERE id = %s"
cursor.execute(update, (token, key, guild.id))
connection.commit()

    %s refers to an arbitrary marker and its not quoted. The prepared statement markers need to be data and cannot be part of the SQL syntax (like table identifiers).

    Could also let the database perform the key generation:

    update = "UPDATE `table` SET token = AES_ENCRYPT(%s, SHA2(CAST(id AS CHAR),256)), WHERE id = %s"
cursor.execute(update, (token, guild.id))

    Also keys are normally secret values. I’m not sure the guild.id quantifies as secret enough to provide any value around the encrypted value.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search