0

I've been reading documentation for sqlite and found that many sources strongly recommend avoiding python string substitution in queries since it makes them vulnerable to injection attacks:

Avoid:

conn.execute("SELECT * FROM %s" % table_name)

conn.execute("SELECT * FROM {}".format(table_name))

I've seen several simple examples where string formatting like this is replaced with slqlite parameters:

values = ('my_table', '1')
conn.execute("SELECT * FROM ? WHERE ROWID = ?", values)

This works in the simple case, but fails when I want to retrieve the parameter values from a list as in this example:

Working code with string formatting:

c.execute("BEGIN TRANSACTION")
for i in range(len(amt_l)):
    c.execute("""
              INSERT INTO transactions ({}, {}, {}, {}, {})
              VALUES ('{}', '{}', '{}', '{}', '{}')
              """.format(header[0], header[1], header[2], header[3], header[4],
                         date_l[i], party_l[i], direction_l[i], ctr_party_l[i], amt_l[i]))
db.commit()

Non-working code using parameters:

c.execute("BEGIN TRANSACTION")
for i in range(len(amt_l)):
    values = (header[0], header[1], header[2], header[3], header[4],
              date_l[i], party_l[i], direction_l[i], ctr_party_l[i], amt_l[i])
    c.execute("""
              INSERT INTO transactions (?, ?, ?, ?, ?)
              VALUES (?, ?, ?, ?, ?)
              """, values)

Is there a way to use ? to fill in parameters in the sql query when the parameter values are retrieved from a list?

Evan
  • 603
  • 1
  • 11
  • 18
  • https://stackoverflow.com/questions/610056/is-it-possible-to-refer-to-column-names-via-bind-variables-in-oracle asks the underlying question for Oracle, but the answer is true to all sane SQL databases. (Also, https://stackoverflow.com/questions/9723931/passing-table-and-column-name-dynamically-using-bind-variables) – Charles Duffy Jun 26 '17 at 04:04
  • ...also, https://stackoverflow.com/questions/3135973/variable-column-names-using-prepared-statements asks that same question (and gets the same answer) for MySQL. – Charles Duffy Jun 26 '17 at 04:06

1 Answers1

2

You can use bind variables to parameterize values. You can't parameterize column names (or table names, or the names of other SQL objects).

Using untrusted data for these names would be innately insecure -- and moreover, making this possible would prevent up-front analysis (of which indexes can be used or otherwise how to efficiently execute a query), which is a non-security-related benefit of prepared statements.

Charles Duffy
  • 280,126
  • 43
  • 390
  • 441
  • Thanks Charles. I've been writing SQL for about a year now, but I'm relatively new to interacting with a database in python. Am I understanding correctly that the issue isn't that I'm filling in the parameter from a value in a list, but rather that I'm using a parameter for something I'm not allowed to? – Evan Jun 26 '17 at 04:19
  • @Evan, exactly right. – Charles Duffy Jun 26 '17 at 04:48