Sqlite3 Error: near "": syntax error

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, Feb 09 '23 at 14:11

score 0 · Accepted Answer · edited Feb 09 '23 at 14:08

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.

Welcome to Stack Overflow. I fixed your code [formatting](https://stackoverflow.com/help/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.) — Karl Knechtel, Feb 09 '23 at 14:09

Sqlite3 Error: near "": syntax error

1 Answers1