Variable to replace %s for sql is not getting honored

I have a table with single empty row inserted. Later I want to update column with some value into the row. I am trying to compose a function which will work for all the columns as shown

def update_column(field, value):
    print(field, value)
    sql = '''UPDATE `table name` SET %s = %s WHERE `ID` = 1'''
    params = (field,value)
    execute_sql(sql, params)

upon execution update_column(‘fieldName’, 10), getting following error. Any help is appreciated.

Failed to execute command UPDATE TABLE2 SET %s = %s WHERE ID = 1 in database due to: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”TEST’ = 10 WHERE ID = 1′ at line 1

Not sure if %s for the field name is the acceptable use case or not. If anyone familiar with such cases, please let me know if I am missing anything here.

Answer

You can’t use query parameters for column names (or table names, sql keywords, expressions, lists, etc.).

A query parameter is filled in as if it were a quoted string, after escaping any literal quote characters in that string.* That’s why it ends up as 'TEST' = 10 and not TEST = 10.

For column names, you have to do that yourself, without the parameter treatment.

It’s up to you to make sure this is safe. If you use the back-ticks, then the only thing you have to do is make sure your field variable does not contain literal back-tick characters.

field.replace('`', '')
sql = '''UPDATE `table name` SET `{field}` = %s WHERE `ID` = 1'''.format(field=field)

---

* This is true for Python, which interpolates parameter values into the string before it submits the SQL query to the server. That’s not a real query parameter; a real parameter leaves the placeholder until after the query is parsed. Python doesn’t support that kind of parameter.