SQL 查询参数化如何工作? [英] How does SQL query parameterisation work?

问题描述

我觉得问这个问题有点傻，因为我似乎是世界上唯一一个不明白的人，但无论如何都在这里.我将使用 Python 作为示例.当我使用原始 SQL 查询(我通常使用 ORM)时，我使用参数化，就像这个使用 SQLite 的例子:

I feel a little silly for asking this since I seem to be the only person in the world who doesn't get it, but here goes anyway. I'm going to use Python as an example. When I use raw SQL queries (I usually use ORMs) I use parameterisation, like this example using SQLite:

方法 A:

username = "wayne"
query_params = (username)
cursor.execute("SELECT * FROM mytable WHERE user=?", query_params)

我知道这行得通，而且我知道这是普遍推荐的做法.执行相同操作的 SQL 注入漏洞的方法如下:

I know this works and I know this is the generally recommended way to do it. A SQL injection-vulnerable way to do the same thing would be something like this:

方法 B:

username = "wayne"
cursor.execute("SELECT * FROM mytable WHERE user='%s'" % username)

据我所知，我了解 SQL 注入，如这篇维基百科文章所述.我的问题很简单:方法 A 与方法 B 有什么真正不同?为什么方法 A 的最终结果与方法 B 不一样?我假设 cursor.execute() 方法(Python 的 DB-API 规范的一部分)负责正确转义和类型检查输入，但这从未在任何地方明确说明.这就是上下文中的所有参数化吗?对我来说，当我们说参数化"时，所有的意思都是字符串替换"，比如 % 格式.错了吗?

As far I can tell I understand SQL injection, as explained in this Wikipedia article. My question is simply: How is method A really different to method B? Why is the end result of method A not the same as method B? I assume that the cursor.execute() method (part of Python's DB-API specification) takes care of correctly escaping and type-checking the input, but this is never explicitly stated anywhere. Is that all that parameterisation in this context is? To me, when we say "parameterisation", all that means is "string substitution", like %-formatting. Is that incorrect?

推荐答案

参数化查询实际上并不进行字符串替换.如果您使用字符串替换，那么 SQL 引擎实际上会看到一个类似于

A parameterized query doesn't actually do string replacement. If you use string substitution, then the SQL engine actually sees a query that looks like

SELECT * FROM mytable WHERE user='wayne'

如果您使用 ? 参数，那么 SQL 引擎会看到一个类似于

If you use a ? parameter, then the SQL engine sees a query that looks like

SELECT * FROM mytable WHERE user=<some value>

这意味着它甚至在看到字符串wayne"之前，就可以完全解析查询并理解查询的作用.它将wayne"粘贴到它自己的查询表示中，而不是描述查询的 SQL 字符串.因此，SQL 注入是不可能的，因为我们已经通过了该过程的 SQL 阶段.

Which means that before it even sees the string "wayne", it can fully parse the query and understand, generally, what the query does. It sticks "wayne" into its own representation of the query, not the SQL string that describes the query. Thus, SQL injection is impossible, since we've already passed the SQL stage of the process.

(以上是概括性的，但或多或​​少传达了这个想法.)

(The above is generalized, but it more or less conveys the idea.)

这篇关于SQL 查询参数化如何工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！