How to fix syntax error with UPDATE in SQLite3 Python module

Question

I'm using python module sqlite3 as per the below code:

# Enter the randomised data into the dictionary:
for square in cube:
    cur.execute("UPDATE cubes SET ? = ? WHERE id = ?", (square, cube[square], session["current_cube_id"]))
con.commit()

Which results in the following error:

cur.execute("UPDATE cubes SET ? = ? WHERE id = ?", (square, cube[square], session["current_cube_id"]))
sqlite3.OperationalError: near "?": syntax error

I don't seem to have a problem with INSERT or SELECT queries, so I assume there is a specific syntax required to UPDATE. From the documentation, tutorials and other examples I can find this seems to be correct - can anyone please assist with what might be the syntax error?

JL Peyret · Accepted Answer · 2021-04-22 15:53:51Z

1

You can't define table, column names, or SQL keywords, using bind variables (the SET ? =) in UPDATE cubes SET ? = ? WHERE id = ?

I am not sure why you feel you need to have a dynamic column name, rather than UPDATE cubes SET mycol = ? WHERE id = ? but you need to specify your column name differently.

You'd have the exact same problem with insert or delete if your target column names, in an insert, or your where condition column names in a delete, were getting specified with ? placeholders. I assume you did not do this so you did not get the error.

Be very careful if you decide to build your query string dynamically as in

myquery = f"UPDATE cubes SET {my_user_supplied_column_name} = ? WHERE id = ?"

cur.execute(myquery, (cube[square], session["current_cube_id"]))

That opens you to a large class of extremely serious vulnerabilities, the SQL Injections because the user may enter anything they want in my_user_supplied_column_name. Best to be very careful as it also has a reputational risk: a savvy prospective employer might for example reject your application if they saw this type of construct, unguarded, in your code because it is an extremely grave, frequent and well-known risk.

edited Apr 22, 2021 at 15:53

answered Apr 22, 2021 at 15:34

JL Peyret

12.2k4 gold badges67 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sjcexplorer Over a year ago

I need to iterate through 54 columns; if I have understood correctly using your suggestion would need this repeated 54 times? Could you suggest how you would iterate through 54 columns please? FYI, I have so far used the CS50 module to learn and this worked while using the CS50 module, but in trying to 'remove the crutches' this is now not working. Thanks for your help.

sjcexplorer Over a year ago

Thanks for the updated answer, very helpful - noted re Injection attacks. When iterating through a large number of columns, is there a better/safer way to do this? Thanks.

JL Peyret Over a year ago

You can cycle through your own hardcoded list of column names and sub them in as I showed in the SQL Injection example. As long as you control the column names yourself, in your code, the risk is fairly minimal. Make a comment next to it that the variables are not user supplied. Note that even reading column names from a file is an attack vector, if the file, apparently "just data", is compromised. Finally, having to cycle through 54 column names seems like a bit of a questionable design approach to the database design, but I can't say without seeing what your table structure looks like.

Collectives™ on Stack Overflow

How to fix syntax error with UPDATE in SQLite3 Python module

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related