SQL syntax error with python strings

Question

I'm having the following error:

  File "sqldata/basetables/places.py", line 24, in update
    sqldata.objs.c.execute("UPDATE places SET name='%s', matrix='%s', bans='%s', pop='%s' WHERE name='%s'" % (place.name,place.matrix,place.bans,place.pop,name))
sqlite3.OperationalError: near "bob": syntax error

Where I have:

>>> place.name
'room'
>>> place.matrix
[['bob', False, False], [False, False, False], [False, False, False]]
>>> place.bans
[]
>>> place.pop
[]
>>> name
'room'

For some reason the single quotes around bob seem to be causing the error, since if I replace 'bob' with True it works, but the single quotes around room do not cause any errors. What is the best approach to correct this? I can't replace 'bob' with bob, since I want a string there, not an object.

I tried the suggestions found here and here but the string remains the same. I also tried replacing 'bob' for ''bob'', u'''bob''', "bob", "'bob'", "\'bob\'". It all gives syntax error.

Removing the single quotes from the SQL query gives:

  File "sqldata/basetables/places.py", line 24, in update
    sqldata.objs.c.execute("UPDATE places SET name=%s, matrix=%s, bans=%s, pop=%s WHERE name=%s" % (place.name,place.matrix,place.bans,place.pop,name))
sqlite3.OperationalError: near ",": syntax error

score 3 · Accepted Answer · answered Jun 04 '13 at 13:56

Log your SQL string before sending it to execute. You'll see it comes out something like this given the values for matrix in your post:

UPDATE places SET name='room', matrix='[['bob', False, False], [False, False, False], [False, False, False]]'

Now, clearly, '[['bob' is not a valid string: you open a single quote, then close it again after the open brackets, but then continue the string regardless. You can't fix this by changing the value you're passing in: you need to change the SQL string.

One (extremely bad) solution would be to use double quotes in the SQL string instead of single ones: then you would get:

"""UPDATE places SET name="room", matrix="[['bob', False, False]..."""

which is at least valid.

But you should not do this. You should use the templating that is provided in the Python DB API:

"UPDATE places SET name=?, matrix=?, bans=?, pop=? WHERE name=?", (place.name,place.matrix,place.bans,place.pop,name)

This gets the db driver itself to do the interpolation, and protects you against SQL injection. See http://bobby-tables.com/ for why this is important.

Thanks. Just a note: doing it the "correct" way gave `sqlite3.InterfaceError: Error binding parameter 1 - probably unsupported type.` Which was fixed by using `unicode(place.matrix)` to convert it to Unicode. — Alex, Jun 04 '13 at 14:45

SQL syntax error with python strings

1 Answers1