0

From my Python code (Flask application, actually), I need to execute sqlite query, of the following structure 

SELECT some_column FROM My_table WHERE some_column=some_value;

Now, some_column recurs twice in the query, one way to execute it is: 

cursor.execute('SELECT ? FROM Users WHERE ?=?;', (some_column, some_column, some_value))

Which is not very nice/Pythonic. Then I came up with: 

cursor.execute('SELECT {0} FROM Users WHERE {0}=?;'.format(some_column), (some_value,))

Finally, I ended up using .format() all the way: 

cursor.execute('SELECT {0} FROM Users WHERE {0}={1};'.format(some_column, some_value), ())

I am wondering if there is prettier and/or more Pythonic way to pass recurring arguments into sqlite's cursor.execute()?

davidism
  • 121,510
  • 29
  • 395
  • 339

2 Answers2

2

First syntax is incorrect. In SQL parameterized queries can use parameters for values, not for table or column names.

Third form is bad because it hard codes a value in a query which is forbidden per best practices. It used to be common some times ago and was the cause for SQL injection security problems.

So the only possibility is second form: use string construction for table and column names, and parameters for values.

But anyway, your query is close to non sense: you ask the value of one single column when you fix that column value. For each selected row, the value will be some_value!

So, I assume that this was a simplified example of a more complex question, but without more context, I simply cannot imagine why you are asking this question and what is your real problem.

Serge Ballesta
  • 143,923
  • 11
  • 122
  • 252
  • I appreciate your explanation and pointing out incorrect syntax. Query is posted as it is though, and intended to check whether some entry (that needs to be unique) already exist in the DB, say email or login during user registration. E.g. `SELECT login FROM Users WHERE login='new_login';` will return `new_login` if it exists or `None` otherwise. –  Aug 07 '18 at 07:15
1

It's not a big improvement, but since you tagged Python 3, consider the f-string: 

f"SELECT {col} FROM Users WHERE {col}={val}"

As Klaus D. points out in a comment, however, it's not best practice to allow values to be format strings, as in some cases it makes your code vulnerable to SQL injection.

It's a little less compact, but you can use a mix of f-string (for column names) and sqlite ? syntax to input values (basically a mashup of your first two examples):

params = (val,)
q = f"SELECT {col} FROM Users WHERE {col} = ?"
cursor.execute(q, params)
andrew_reece
  • 20,390
  • 3
  • 33
  • 58
  • 1
    My gratitude to you for the answer. Am I right to conclude that using any form of format strings is SQL injection insecure **only** for column values, and not column or table names? –  Aug 07 '18 at 06:38