0

When I run the SQL query below I get the error print(db.execute("SELECT * FROM (?);"), (tableName)) sqlite3.OperationalError: near "?": syntax error

db = sqlite3.Connection(":memory:")
db.execute("CREATE TABLE Students(name);")
tableName = "Students"
var1 = "Jon"
var2 = "Steve"
var3 = "Chuckie"
db.execute("INSERT INTO Students VALUES (?), (?), (?)", (var1, var2, var3))
print(db.execute("SELECT * FROM (?);"), (tableName))

What is the correct way to pass in parameters to a SQL query?

static const
  • 953
  • 4
  • 16
  • You cannot parametrize table name. If you try to concatenate/interpolate query string it would be possible but without proper quoting it would lead to SQL Injection. – Lukasz Szozda Jun 20 '19 at 16:33
  • I need to parametrize the table name, what would be the best way to do this then? @LukaszSzozda –  Jun 20 '19 at 16:37

2 Answers2

0

Pass the values in a tuples,

query = ("INSERT INTO  billed_items(item_name,billed_qty,price,item_bill_series) VALUES(?,?,?,?)")
c.execute(query,((name),(no),(price),(series))
conn.commit()
static const
  • 953
  • 4
  • 16
0

You can't dynamically bind object names in SQL, only values. For such a behavior, you'll have to resort to string manipulation:

queryTemplate = 'SELECT * FROM %s'
tableName = 'Students'
query = queryTemplate % tableName

print(query)

EDIT:
To address the concerns in the comment - yes, this technique is indeed more vulnerable to SQL Injection attacks. The common practice to protect against SQL Injection in such scenarios is to use a whitelist of allowed tables.

E.g.:

allowedTables = ['students', 'teachers']
if tableName not in allowedTables:
    raise ValueError('Wrong table')
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • `Students; DROP TABLE ...; --` – Lukasz Szozda Jun 20 '19 at 16:38
  • How do you protect against such a SQL injection? –  Jun 20 '19 at 16:45
  • @laddie_03 probably by using a whitelist of some sorts. See my edited answer for a simple implementation – Mureinik Jun 20 '19 at 16:50
  • Does the python string function .format() work for SQL queries? –  Jun 20 '19 at 17:07
  • Are we still using the modulo operator for string interpolation? The [consensus years ago](https://stackoverflow.com/questions/5082452/string-formatting-vs-format) is to use `str.format`. In fact, even Python's [PEP 3101](https://www.python.org/dev/peps/pep-3101/) advises so! That's like using the [implicit over explicit join](https://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins) in SQL which ANSI-1992 recommended the current standard. – Parfait Jun 20 '19 at 19:06