Why Do Sqlite3 DB_API Qmark And Named Style Not Work In "select Where" Queries?

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 us

Solution 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.