Safe way to update mysql table entries using dictionary in python (prevent SQL injection)

Question

I'm writing a helper function for my web application that updates the database based on some information obtained from an external API (not user input). I have the following code, but it is marked as "unsafe" by the Bandit python package.

Ideally I could write a function in such a way that I hardcode the columns to be updated, but I think it should be possible to do this dynamically as well.

Is this a safe way to update a table (no SQL injection possible)?

import mysql.connector as database

def update_message_by_uid(uid: str, update_dict: dict) -> None:

    # Fetches the previous entry from the database using the unique identifier
    message_info_dict = get_message_by_uid(uid)

    # check that all the keys of the update dict are also in the original dict
    assert set(update_dict.keys()) <= set(
        message_info_dict.keys()
    ), "Some of the keys in the dictionary passed are not valid database columns"

    # We update the entry for all entries in the dictionary containing the updates
    statement = 'UPDATE messages SET {}  WHERE uid = %s'.format(", ".join('{}=%s'.format(k) for k in update_dict))


    # Concatenates the values of the dict with the unique identifier to pass it to the execution method as one variable
    data = list(update_dict.values()) + [uid]

    cursor.execute(statement, data)

Answer 1

You should enclose column names in backticks in case the column name is a SQL reserved keyword or contains spaces, punctuation, or international characters. Also make sure that the backtick characters in the column names are replaced with two backticks.

assignments = ", ".join(f"`{k.replace('`', '``')}`=%s" for k in update_dict)
statement = f"UPDATE messages SET {assignments}  WHERE uid = %s"

I prefer using f string instead of format().