Bug #66685 parameter subsitution in UPDATE statement causes syntax error

• Submitted: 4 Sep 2012 14:40
• Modified: 6 Sep 2012 6:09
• Reporter: an p
• Status: Not a Bug
• Category: Connector / Python
• Severity: S3 (Non-critical)
• Version: 1.0.5
• OS: MacOS
• CPU Architecture: Any
• Tags: MySQL, python, update statement

[4 Sep 2012 14:40] an p

Description:
Have this in Python3.2.3

When using parameter substitution in an UPDATE statement a syntax error (ERROR 1064) is thrown

How to repeat:
self.c is a MySQLCursor of a connection

self.c.execute('''UPDATE clients_users SET %s = %s WHERE user_name = %s AND signature = %s''', (key, value, userName, signature))

Suggested fix:
Not sure, but what I observed is:

when putting a breakpoint in cursor.py at line 407 the variable stmt (which I suppose contains the statement to be executed) has value:

b"UPDATE clients_users SET 'user_name' = 'anp' WHERE user_name = 'anpp' AND signature = '92019e6121e3405b830fb85bd34c75b8'"

the field name that has to be updated (user_name in this case) has single quotes added which causes a syntax error

[6 Sep 2012 6:09] Geert Vanderkelen

You can not pass identifiers as parameters. Only variables should be passed as parameters to execute() or executemany().
Consequently, this is not a bug.

If you want to make the field names dynamic, you can do something like this:

  update = (
    "UPDATE clients_users SET %s = %%s "
    "WHERE user_name = %%s AND signature = %%s"
    ) % (key,)
  self.c.execute(update, (value, userName, signature))