Why do sqlite3 DB_API qmark and named style not work in "select where" queries?

Question

Assuming I have a database with table users with a row:

ID = 0, name = 'myName'

I can get the ID value (assuming I'm connected) by using either:

cursor.execute("""SELECT ID FROM users WHERE %s = %s""" % ('name', 'myName'))
print(cursor.fetchone())

or

cursor.execute("""SELECT ID FROM users WHERE {} = {}""".format('name', 'myName'))
print(cursor.fetchone())

which both work perfectly fine but are bad practice according to the documentation.

The documentation suggest using either qmark or named style for queries with variable input. The problem arises when using the suggested styles in a SELECT * FROM myDatabase WHERE ? = ? query.

qmark style:

cursor.execute("""SELECT ID FROM users WHERE ? = ?""", ('name', 'myName'))
print(cursor.fetchone())

named style

cursor.execute("""SELECT ID FROM users WHERE :column = :cell""", {'column': 'name', 'cell':'myName'})
print(cursor.fetchone())

Using either of the latter styles results in a None returned. When using the qmark or named style in the context of INSERT such as used in the example in the documentation it does work as expected.

(To clarify, with the qmark style, adding parentheses to each ? or the entire ? = ? does not change the outcome. Using parentheses with each ? and using an additional argument causes execute() to malfunction due to too many arguments given.)

What causes this behaviour? Is it possible to use qmark or named style in a SELECT...WHERE ? = ? query?

Answer 1

Parameter substitution is for values, not identifiers (the names of columns and tables etc). RDBMS have different rules for quoting values and identifiers. Using the parameter substitution placeholder for identifiers leads to the identifier being incorrectly quoted, for example

cur.execute('SELECT * FROM tbl WHERE ? = ?', ('col1', 42))

ends up as

SELECT * FROM tbl WHERE 'col1' = 42

note the single quotes around col1, which cause it to evaluated as a string, not a column name.

If you want to have dynamic identifiers as well as values in your query then use string formatting for the identifiers and parameter substitution for the values. For example, using double quotes for the identifier

cur.execute('SELECT * FROM tbl WHERE "{}" = ?'.format('col1'), (42,))

Here's an example of string formatting resulting in an error

>>> conn = sqlite3.connect(':memory:')
>>> conn.execute('create table tbl (col1 date)')
<sqlite3.Cursor object at 0x7f56abcf1ce0>
>>> cur = conn.cursor()
>>> cur.execute('INSERT INTO tbl (col1) VALUES(?)', ('2021-05-01',))
<sqlite3.Cursor object at 0x7f56abc8f030>
>>> cur.execute('INSERT INTO tbl (col1) VALUES(%s)' % '2021-05-01')
<sqlite3.Cursor object at 0x7f56abc8f030>
>>> conn.commit()
>>> cur.execute('SELECT col1 FROM tbl WHERE %s = %s' % ('col1', '2021-05-01'))
<sqlite3.Cursor object at 0x7f56abc8f030>
>>> for row in cur:print(row)
... 
(2015,)

When string formatting is used in the INSERT and SELECT statements, the date is evaluated as an arithmetic expression, resulting in the wrong value being stored and retrieved. Errors like this are annoying, but using string formatting can also leave your application to SQL injection attacks, which could have more serious consequences.