Python SQLite3 multiple string manipulation

Question

Using SQLite3, how do you use multiple variables in one request? Using one works but once you add a second it gives you the syntax error below. I have tried researching this and I cannot find any documentation/other questions in the same situation.

Code:

self.db.execute("INSERT INTO complements (:record) VALUES (:field)", {"record": record, "field": field})

Error:

|Traceback (most recent call last):
  File "(path)", line 22, in <module>
    db.add("Adjective", "adjective")
  File "(path)", line 17, in add
    self.db.execute("INSERT INTO complements (:record) VALUES (:field)", {"record": record, "field": field})
sqlite3.OperationalError: near ":record": syntax error

Pretty sure you can't parameterize column names, only values. You'll have to use format strings to parameterize the query. Take care to prevent SQL injection! — Luatic
– Luatic, Commented May 25, 2022 at 10:55
Are there any other ways to parameterize them? I could create different functions but I'd prefer one — Zax71
– Zax71, Commented May 25, 2022 at 10:58

Luatic · Accepted Answer · 2022-05-25 11:01:55Z

1

Column names (and other identifiers like table names) can't be parameterized which is why you are getting a syntax error. Only "values" (the data to be inserted) can be parameterized. To fix this, format the query yourself, for example using format strings:

record, field = ...
self.db.execute(f"INSERT INTO complements ({record}) VALUES (:field)", {"field": field})

if record is user-provided you must take care to prevent SQL injection, e.g. by checking whether it is a valid column name: record in ["some", "column", "names"]; it is however rather uncommon to parameterize identifiers in queries, so this feels like an XY-Problem.

answered May 25, 2022 at 11:01

Luatic

11.3k3 gold badges20 silver badges40 bronze badges

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

Collectives™ on Stack Overflow

Python SQLite3 multiple string manipulation

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related