您是否应该仅将准备好的语句用于转义? [英] Should you use prepared statements for their escaping only?

问题描述

我看到很多人说您应该始终对数据库查询使用准备好的语句.但是，PHP文档说:

I see a lot of people saying you should always use prepared statements for database queries. However, the PHP docs say:

每个准备好的语句占用服务器资源.声明应 使用后立即将其显式关闭.如果未明确完成， 当语句句柄被释放时，该语句将关闭 PHP.

Every prepared statement occupies server resources. Statements should be closed explicitly immediately after use. If not done explicitly, the statement will be closed when the statement handle is freed by PHP.

使用准备好的语句并不总是最有效的方法 执行一条语句.仅执行一次的准备好的语句会导致 客户端与服务器之间的往返次数要多于未准备的语句.​​

Using a prepared statement is not always the most efficient way of executing a statement. A prepared statement executed only once causes more client-server round-trips than a non-prepared statement.

来自 http://php.net/manual/en/mysqli.quickstart.prepared- statement.php

鉴于上述情况，如果您只打算使用一次查询，那么使用准备好的语句不是更好吗?

Given the above, if you're only going to use a query once, isn't it better not to use prepared statements?

推荐答案

差异可忽略不计.

尽管如此，人们还是必须将本地准备好的陈述与准备好的陈述的一般概念区分开.

Nevertheless, one have to distinguish native prepared statements from the general idea of a prepared statement.

前者只是大多数DBMS支持的一种运行查询的形式，在此处进行了说明.其用法可以质疑.
后者是用占位符替换实际数据的总体思路，这意味着要进一步处理替换后的数据.它在编程中被广泛使用，一个著名的printf()函数就是一个例子.并且后一种方法必须始终用于对数据库运行查询，无论该查询是否由本机准备好的语句支持.因为:

The former is just a form of running queries supported by most of DBMS, explained here. Its usage can be questioned.
The latter is a general idea of substituting actual data with a placeholder, implying further processing of the substituted data. It is widely used in programming, a well-known printf() function is an example. And this latter approach have to be ALWAYS used to run a query against a database, no matter if it is backed by native prepared statements or not. Because:

• 准备好的语句使正确的格式化(或处理)成为不可避免的 .
• prepared语句在唯一适当的位置进行正确的格式化(或处理) -就在查询执行之前，而不是其他地方，因此，我们的安全不会依赖于诸如
  • 一些PHP的魔术"功能，它破坏了数据而不是使其变得安全.
  一个(或几个)程序员的良好意愿，他们可以决定在程序流中的某个地方格式化(或不格式化)我们的变量.这是非常重要的一点.
  • prepared statement makes proper formatting (or handling) inevitable.
  • prepared statement does proper formatting (or handling) in the only proper place - right before query execution, not somewhere else, so, our safety won't rely on such unreliable sources like
    • some PHP 'magic' feature which rather spoils the data than make it safe.
    • good will of one (or several) programmers, who can decide to format (or not to format) our variable somewhere in the program flow. That's the point of great importance.

    因此，即使您考虑不使用本机准备语句(也可以)，也必须始终使用占位符而不是实际数据来创建查询.为此，您可以使用 PDO ，它的工作原理与上述完全相同-默认情况下，它只是模拟准备 em>，表示将常规SQL查询创建为准备好的查询和数据，然后针对数据库运行.

    So, even if you consider not using native prepared statements (which is quite okay), you have to always create your queries using placeholders instead of the actual data. For this purpose you can use PDO, which works exactly as described above - by default it just emulate prepares, means regular SQL query being created out prepared query and data, and then run against database.

    但是，PDO缺少对许多重要数据类型(例如标识符或数组)的支持-因此，它使您无法始终使用占位符，从而使注入成为可能.幸运的是， safeMysql 具有每种数据类型的占位符，并允许您安全地运行查询.

    However, PDO lacks support for many important data types, such as identifier or an array - thus it makes you unable to always use placeholders and thus makes an injection quite possible. Luckily, safeMysql has placeholders for the every data type and allows you to run queries safely.

    这篇关于您是否应该仅将准备好的语句用于转义?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！