为什么sqlite3 DB_API qmark和命名样式在“select where"中不起作用?查询? [英] Why do sqlite3 DB_API qmark and named style not work in "select where" queries?
问题描述
假设我有一个数据库，其中包含一行 users 表:
Assuming I have a database with table users with a row:
ID = 0, name = 'myName'
我可以使用以下任一方法获取 ID 值(假设我已连接):
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())
或
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.
文档建议对具有可变输入的查询使用 qmark 或命名样式.在 SELECT * FROM myDatabase WHERE 中使用建议的样式时会出现问题?= ? 查询.
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 样式:
qmark style:
cursor.execute("""SELECT ID FROM users WHERE ? = ?""", ('name', 'myName'))
print(cursor.fetchone())
命名样式
cursor.execute("""SELECT ID FROM users WHERE :column = :cell""", {'column': 'name', 'cell':'myName'})
print(cursor.fetchone())
使用后一种样式会导致 None 返回.在 INSERT 的上下文中使用 qmark 或命名样式时，如文档中的示例中使用的那样，它确实按预期工作.
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.
(澄清一下，对于 qmark 样式，为每个 ? 或整个 ? = ? 添加括号不会改变结果.对每个 使用括号? 并使用额外的参数会导致 execute() 由于给出的参数过多而出现故障.)
(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.)
是什么导致了这种行为?是否可以在 SELECT...WHERE 中使用 qmark 或命名样式?= ? 查询?
What causes this behaviour? Is it possible to use qmark or named style in a SELECT...WHERE ? = ? query?
推荐答案
参数替换用于值，而不是标识符(列和表的名称等).RDBMS 对引用值和标识符有不同的规则.对标识符使用参数替换占位符会导致标识符被错误引用，例如
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))
最终为
SELECT * FROM tbl WHERE 'col1' = 42
注意 col1 周围的单引号，这导致它被评估为字符串，而不是列名.
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,)
在INSERT 和SELECT 语句中使用字符串格式时，日期被计算为算术表达式，导致存储和检索错误值.像这样的错误很烦人，但使用字符串格式也会让您的应用程序受到 SQL 注入攻击，这可能会产生更严重的后果.
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.
这篇关于为什么sqlite3 DB_API qmark和命名样式在“select where"中不起作用?查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
