logo标签列表

Python中预编译语句和参数化查询的混淆问题

pythondatabaseprepared-statementsql-injectionparameterized-query
34
据我所知，准备好的语句主要是数据库功能，它允许将参数与使用这些参数的代码分开。例如：
PREPARE fooplan (int, text, bool, numeric) AS
    INSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);

参数化查询可以替代手动字符串插值，因此不需要进行以下操作：

cursor.execute("SELECT FROM tablename WHERE fieldname = %s" % value)

我们可以做。
cursor.execute("SELECT FROM tablename WHERE fieldname = %s", [value])

现在，看起来预处理语句在数据库语言中被广泛使用，参数化查询主要用于连接到数据库的编程语言中，尽管我见过一些例外情况。
问题在于询问预编译语句和参数化查询之间的区别容易带来很多混淆。它们的目的显然是相同的，但它们的方法似乎是不同的。然而，有来源表明两者是相同的。MySQLdb和Psycopg2似乎支持参数化查询，但不支持预编译语句（例如，在这里查看MySQLdb以及在postgres驱动的TODO列表或这个回答中查看SQLAlchemy组）。实际上，有一个gist实现了支持预编译语句的Psycopg2游标以及一个简短的解释。还有建议通过子类化Psycopg2的游标对象来手动提供准备好的语句。

我希望能够得到以下问题的权威答案：

  • 准备语句和参数化查询之间有意义的区别吗？在实践中，这是否重要？如果使用参数化查询，需要担心准备语句吗？

  • 如果有区别，Python生态系统中准备语句的当前状态是什么？哪些数据库适配器支持准备语句？

在Python中，我认为“参数化”查询是光标对象的一个功能，通常使用预处理语句来实现。有时，在编写SQL代码时，这两者是可以互换使用的，具体取决于强调哪个方面（将参数放入语句中还是缓存查询计划）。 - Gordon Linoff
谢谢。也许参数化意味着准备好的语句，但是为什么那么多人争论他们的数据库适配器不支持准备好的语句呢（例如这个评论引用了这个项目）？https://dev59.com/jnI-5IYBdhLWcg3wQV4Z#xd0LoYgBc1ULPQZFJHVz [https://pythonhosted.org/oursql/api.html?highlight=prepared#oursql.Connection.cursor] - r_31415
4个回答
32

  • 预处理语句 (Prepared statement)：对于数据库中的一个预先解释的查询程序的引用，已准备好接受参数。

  • 参数化查询 (Parametrized query)：您的代码以这样的方式进行查询，即您将值与一些具有占位符值的 SQL 一起传递，通常是 ? 或类似 %s。

这里的混淆似乎源于（表面上）没有区分直接获取预处理语句对象和通过'参数化查询'方法传递值的能力，该方法非常像它...因为它就是它，或者至少为您创建了一个。

例如：SQLite3 库的 C 接口具有许多用于处理 预处理语句对象 的工具，但 Python API 几乎没有提到它们。您无法随时准备语句并多次使用它。相反，您可以使用 sqlite3.executemany(sql, params)，它接收 SQL 代码，内部创建预处理语句，然后在循环中使用该语句来处理您给出的可迭代参数组中的每个参数元组。

Python 中的许多其他 SQL 库都以同样的方式运行。使用预处理语句对象可能会带来真正的烦恼，会产生歧义，而在像 Python 这样偏向于清晰和易用而非原始执行速度的语言中，它们并不是最好的选择。基本上，如果您发现自己必须对一个被重新解释的复杂 SQL 查询进行成千上万次或数百万次的调用，那么您应该考虑采用不同的方法。无论如何，有时人们希望直接访问这些对象，因为如果您保留相同的预处理语句，数据库服务器就不必一遍又一遍地解释相同的 SQL 代码；大多数情况下，这将是从错误方向上解决问题，您可以通过其他方式或通过重构代码获得更大的节省。

在IT技术中，准备好的语句和参数化查询方法可以使您的数据与SQL代码分离，这一点更为重要。 这比字符串格式化要好得多！ 从一个形式或另一个形式来看，您应该将参数化查询和准备好的语句视为将可变数据从应用程序传递到数据库的唯一方法 如果您试图以其他方式构建SQL语句，它不仅运行速度会慢得多，而且您还会容易受到其他问题的影响。

*例如，通过使用生成器函数生成要馈送到DB中的数据，然后使用executemany()一次性全部插入，而不是每次循环调用execute()。

tl;dr

参数化查询是一种操作，内部生成准备好的语句，然后传入参数并执行。

编辑：许多人都会看到此答案！我还想澄清的是，许多数据库引擎还具有准备好的语句的概念，可以使用纯文本查询语法显式构建，然后在客户端会话的生命周期内重复使用（例如，在postgres中）。有时，您可以控制是否缓存查询计划，以节省更多时间。一些框架自动使用它们（我看到过Rails的ORM积极地这样做），有时有用，有时则对它们进行操作时会产生负面影响。

此外，如果您要挑剔，参数化查询并不总是在后台使用准备好的语句；如果可能的话，它们应该这样做，但有时只是格式化参数值。此处“准备好的语句”和“参数化查询”的真正区别只是您使用的API形式。

5
参数化查询是一种单个操作，它会在内部生成一个预处理语句，然后传入你的参数并执行。但仅当数据库适配器实际按照此方式实现时才能这样做。例如，流行的MySQL适配器仍使用字符串插值来创建最终的查询数据，他们会先对数据进行很好的清理。 - Martijn Pieters
@Widdershins 谢谢您的回复。 - r_31415
2
@RobertSmith：没有，至少对于长期维护的模块来说是这样。清理并不是那么困难，但最好还是由驱动程序完成。使用预处理语句和使用嵌入式文字面量之间的风险并没有多少区别，因为数据库无法区分语句中的嵌入式文字面量和程序员插入的未经过净化的输入。 - Martijn Pieters
@RobertSmith：没有，至少对于长期维护的模块来说是这样。虽然清理数据并不是那么难，但最好还是至少由驱动程序来完成。与使用预处理语句相比，风险既不会更大也不会更小，因为数据库无法区分语句中的嵌入式文字和程序员插入的未经过清理的输入。 - Martijn Pieters
@Martijn Pieters，你提出了一个很好的观点。不能保证库会依赖于数据库驱动程序的过滤器。也许驱动程序甚至不支持存储参数化查询；像MySQL这样糟糕的实现，我不会打赌。虽然对于使用该库的开发人员来说通常不会有太大危险，但是假设库的制造商正确地实现了他们的过滤器，这也存在另一个风险点。 - Mumbleskates
显示剩余8条评论
7

首先，你的问题表明你做了很好的准备 - 做得好。

我不确定我是否有资格提供权威答案，但我会尝试解释我的理解。

预处理语句是在数据库服务器一侧创建的对象，作为PREPARE语句的结果，将提供的SQL语句转换为带参数的临时过程。预处理语句在当前数据库会话的生命周期内存在，并在会话结束后被丢弃。SQL语句DEALOCATE允许显式地销毁预处理语句。

数据库客户端可以使用SQL语句EXECUTE通过调用其名称和参数来执行准备好的语句。

参数化语句通常是预处理语句的别名，因为预处理语句通常具有一些参数。

参数化查询似乎是使用较少的同义词（24百万谷歌搜索结果为参数化语句，14百万搜索结果为参数化查询）。可能有些人将此术语用于其他目的。

预处理语句的优点包括：

  • 实际准备好的语句调用更快（不计算PREPARE的时间）
  • 防止SQL注入攻击

执行SQL查询的参与者

真实应用程序可能会有以下参与者：

  • 应用程序代码
  • ORM包（例如SQLAlchemy）
  • 数据库驱动程序
  • 数据库服务器

从应用程序的角度来看，很难知道代码是否真正使用了数据库服务器上的预处理语句，因为任何参与者都可能缺乏对预处理语句的支持。

结论

在应用程序代码中，避免直接构建SQL查询，因为它容易受到SQL注入攻击。因此，建议使用ORM提供的参数化查询，即使它不会在数据库服务器端使用预处理语句，因为ORM代码可以优化以防止此类攻击。

根据性能原因决定是否使用预处理语句。如果您有一个简单的SQL查询，只执行几次，那么它不会有帮助，有时甚至会稍微减慢执行速度。

对于执行次数多且执行时间相对较短的复杂查询，效果最大。在这种情况下，您可以按照以下步骤操作：

  • 检查您要使用的数据库是否支持PREPARE语句。在大多数情况下，它将存在。
  • 检查您使用的驱动程序是否支持预处理语句，如果不支持，请尝试找到另一个支持它的驱动程序。
  • 检查ORM包级别的此功能的支持情况。有时会因驱动程序而异（例如，SQLAlchemy由于MySQL的管理方式而对预处理语句提出了一些限制）。
如果您正在寻找真正权威的答案，我建议向sqlalchemy的作者寻求帮助。
谢谢！我认为你在这里做出了正确的区分。预处理语句和参数化语句/查询是相同的，但在整个应用程序中应用方式不同。据我所知，Python中大多数数据库驱动程序不支持在数据库上使用预处理语句（例外情况是oursql），我的担忧主要集中在安全性方面（而不是性能）。我想，在不支持数据库上的预处理语句中存在一些安全风险。我非常想知道这种情况的程度。 - r_31415
0

一个 SQL 语句不能立即执行：DBMS 必须在执行之前解释它们。

预处理语句是已经解释的语句，DBMS 更改参数并立即开始查询。这是某些 DBMS 的特性，您可以实现快速响应（与存储过程相当）。

参数化语句只是您在编程语言中组成查询字符串的一种方式。由于 SQL 字符串的形式无关紧要，因此 DBMS 的响应速度较慢。

如果您测量执行 3-4 次相同查询（带有不同条件的选择），则使用参数化查询将看到相同的时间，而准备好的语句从第二次执行开始时间更短（首次 DBMS 仍需解释脚本）。

6
嗯，实际上，有关参数化较慢的部分并不完全正确。许多数据库将自动缓存执行计划，并在后续的操作中重复使用它，如果唯一改变的是绑定参数。另一个避免直接将变量注入到查询字符串中来滚动自己的查询的原因是SQL注入问题。 - JL Peyret
谢谢您的回答。我猜您间接地声称准备好的语句和参数化查询不同，并且速度是一个有意义的区别。 - r_31415
0

我认为关于使用executemany的评论未能处理应用程序随时间将数据存储到数据库并希望每个插入语句尽可能高效的情况。因此，希望准备一次插入语句并重复使用准备好的语句。 或者，可以将所需的语句放在存储过程中并重复使用它。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接