Python cursor.execute() with MySQL UPDATE causes syntax error

Question

Following this example I am attempting to rewrite code that works with code that protects against SQL injection:

Code that works:

table = "led_status"
field = "test_led"
value = "FALSE"

cursor.execute(("UPDATE %s SET %s = %s") % (table, field, value))

code that does not work:

table = "led_status"
field = "test_led"
value = "FALSE"

cursor.execute(("UPDATE %s SET %s = %s", table, field, value))

Nor does this code work:

table = "led_status"
field = "test_led"
value = "FALSE"

sql_update_command = "UPDATE %s SET %s = %s"
cursor.execute(sql_update_command, (table, field, value))

The first example works, the others do not and each of them throw this syntax error:

mysql.connector.errors.ProgrammingError: 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 ''led_status' SET 'test_led' = 'FALSE'' at line 1

I am not sure what I am doing wrong so any pointers would be greatly appreciated.

score 2 · Answer 1 · edited Nov 18 '20 at 21:39

2

The optimal way for doing it according to the documentation is with:

from psycopg2 import sql

cur.execute(
sql.SQL("insert into {} values (%s, %s)")
    .format(sql.Identifier('my_table')),
[10, 20])

Source

edited Nov 18 '20 at 21:39

marc_s

732,580
175
1,330
1,459

answered Jan 04 '19 at 20:06

Bruno Carballo

1,156
8
15

Richard · Accepted Answer · 2019-01-04T19:53:13.683

1

Thanks for the tips. Eventually I figured out my error after reading this post. Apparently you can't parameterize a table name !

This is how I fixed the code:

table = "led_status"
field = "test_led"
value = "FALSE"

sql_update = "UPDATE " + table + " SET " + column + " = %s"
cursor.execute(sql_update, (value,))

edited Jan 04 '19 at 19:53

answered Jan 03 '19 at 03:38

Richard

313
1
4
14

You don't have a prepared statement. The last example was updated as the tuples were surrounding the entire statement, and I did not see it at first glance. – Stephan Jan 03 '19 at 15:33
To pass the table name you sould use `sql.Identifier` and `format()` – Bruno Carballo Jan 04 '19 at 20:08

Stephan · Answer 3 · 2020-08-09T08:37:25.553

0

Your SQL is incorrect in all 3 examples. The following code should work:

table = "led_status"
field = "test_led"
value = False

cursor.execute("UPDATE `%s` SET `%s` = %s", (table, field, value))

Notice the backticks (`) around table and column names, however, values should be represented as the columns respective object type. Single and double quotes should be used to represent string values.

In your case, FALSE should most likely not be stored as a string, but a boolean, or tinyint within your database schema.

edited Aug 09 '20 at 08:37

answered Jan 03 '19 at 01:18

Stephan

332
5
13

Actually, in my case, I want FALSE to be stored as a literal string. I'll try the other changes! – Richard Jan 03 '19 at 01:24
hummm...still didn't work, now I am getting: `AttributeError: 'tuple' object has no attribute 'encode'` – Richard Jan 03 '19 at 01:26

score 0 · Answer 4 · answered Jan 03 '19 at 01:46

I like to use psycopg2 for instances like this where you are trying to feed a column name as an input and do not want it escaped

from psycopg2.extensions import AsIs

cmd = """
      UPDATE %(table)s SET %(column)s = %(val)s
      """

kwargs = {
    'table': AsIs('led_status'),
    'column': AsIs('test_led'),
    'val': False
}

cursor.execute(cmd, kwargs)

score 0 · Answer 5 · answered Jan 04 '19 at 20:24

Since your question is about SQL injection, I'll go a little further than giving you a one liner. Your problem is that you cannot parameterize a table name. When dealing with SQL injection you have to distinguish the difference between dynamic SQL and user input values. Escaping values to protect against SQL injection should be handled by the driver's quote/escaping mechanism. You are on your own validating dynamic SQL fragments. Dynamic SQL fragments include things like variable WHERE condition expressions, variable table names or variable SELECT field lists.

The correct syntax for your example is:

cursor.execute("UPDATE {} SET {} = %s".format(table, field), value)

or

cursor.execute("UPDATE %s SET %s = %%s" % (table, field), value)

However, if the table does come from the user you must validate it against some predefined list before you get to this statement. You cannot trust the database to do the right thing with table names or field names. The following data structure would provide something to validate off of for example.

valid_fields = {
   'table_1': ['field_1', 'field_2'],
   'table_2': ['field_a', 'field_b'],
   'table_3': ['field_x', 'field_y']
}

You can also use special catalog tables your database provides (like pg_catalog) to get these dynamically, but you should still check for special field/table names (OID for example).

Thanks for the extra information. From a programming view I want to protect against injection however the values for table, column, etc all come hardcoded from various other functions and passed as a variable to a single database function to write an update that words program wide, there is no user interaction or input at all. However I did try both of your examples and both of them failed with: `1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version` — Richard, Jan 06 '19 at 18:27

Python cursor.execute() with MySQL UPDATE causes syntax error

5 Answers5