Python Sqlite3 OperationalError when running UPDATE

Question

I'm running the following Python3 code on a Sqlite3 database:

db.execute("UPDATE user SET ? = ? WHERE id = ?", (name, quantity, str(g.user['id'])))

where db is my cursor object. However, this produces the error

sqlite3.OperationalError: near "?": syntax error.

Is this the right syntax for cursor.execute()?

AlexMTX · Accepted Answer · 2020-04-14 19:43:33Z

0

f-strings would do the job in python3

db.execute(f"UPDATE user SET {name} = {quantity} WHERE id = {str(g.user['id']}"

answered Apr 14, 2020 at 19:43

AlexMTX

724 bronze badges

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

4 Comments

AlexMTX Over a year ago

and don't forget to commit your changes

Nora Goodman Over a year ago

I know that using a ? provides security against an injection. Would f-strings provide the same level of security, or do I need to sanitize the input on my own?

AlexMTX Over a year ago

The problem here is that you parametrize a column {name} that is not allowed to be filled in using parameters ?. If you need secure parameters, you would need to specify a column than. There is a question about security stackoverflow.com/a/44752966/2630643

Nora Goodman Over a year ago

Got it. Thanks! My code ended up being db.execute("UPDATE user SET " + name + " = ? WHERE id = ?", (quantity, str(g.user['id']))) since the column name isn't specified by the user, I just had it as a variable in order to loop.

Collectives™ on Stack Overflow

Python Sqlite3 OperationalError when running UPDATE

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related