Sqlite3 Error: near "<question mark>": syntax error [duplicate]

Question

I am trying to use placeholders, but they do not work. Here is a bad example of what i need, it works perfectly, but it is not protected against SQL-injections:

`def updateUser(self, user_id: int, **kwargs) -> bool:
        for arg, value in kwargs.items():
            try:
                sql = f"UPDATE user SET {arg}='{value}' WHERE user_id = {user_id};"
                self.con.execute(sql)
            except Exception as e:
                print(e)
                self.con.rollback()
                return False
        self.con.commit()
        return True
`

It works with any data type perfectly. Now the code that i want to use, but it don't work:

`def updateUser(self, user_id: int, **kwargs) -> bool:
        for arg, value in kwargs.items():
            try:
                self.con.execute("UPDATE user SET ?='?' WHERE user_id = ?;", (arg, value, user_id))
            except Exception as e:
                print(e)
                self.con.rollback()
                return False
        self.con.commit()
        return True

` This code returns error:

`>>> ud.updateUser(1, nick="test")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "<path>/inter.py", line 56, in updateUser
    self.con.execute("UPDATE user SET ?='?' WHERE user_id = ?;", (arg, value, user_id))
sqlite3.OperationalError: near "?": syntax error

`

I've tried every possible way to write this query (brackets, quotes), but it only works with f-string. What am i doing worng?

Parameterized queries like this (at least for most SQL engines) do not support a dynamic table name or column name. That part needs to be formatted in via the Python code. This still involves a risk of SQL injection if the text could come from user input. I gave the best duplicate links I could find for the topic. But the more important question is, why should the user be able to control, which table or column is queried? Whether or not it is valid to SET the user's info to a specific value will depend on which column it is, right? — Karl Knechtel
– Karl Knechtel, Commented Feb 9, 2023 at 14:11

Karl Knechtel · Accepted Answer · 2023-02-09 14:08:39Z

1

The issue is with the syntax of the query. The ? placeholder can only be used for values, not for column names or table names.

You need to specify the column name and value in the query string, not as placeholders. Here's an example:

def updateUser(self, user_id: int, **kwargs) -> bool:
    for arg, value in kwargs.items():
        try:
            sql = "UPDATE user SET {}=? WHERE user_id=?".format(arg)
            self.con.execute(sql, (value, user_id))
        except Exception as e:
            print(e)
            self.con.rollback()
            return False
    self.con.commit()
    return True

This code should work as expected and will also protect against SQL injection attacks.

edited Feb 9, 2023 at 14:08

Karl Knechtel

61.3k14 gold badges131 silver badges193 bronze badges

answered Feb 9, 2023 at 14:05

Drissi El Bouzaidi Nabil

863 bronze badges

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

1 Comment

Karl Knechtel Over a year ago

Welcome to Stack Overflow. I fixed your code formatting. For future reference: you need to use the backtick symbol for the code "fence", not quotation marks. (On a standard US keyboard, this is the key above the Tab key.)

Collectives™ on Stack Overflow

Sqlite3 Error: near "<question mark>": syntax error [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related