php PDO MySQL：是否使用 PDO::ATTR_EMULATE_PREPARES？

Question

提问by Andrew Ensley

This is what I've read so far about PDO::ATTR_EMULATE_PREPARES:

这是我到目前为止阅读的内容PDO::ATTR_EMULATE_PREPARES：

I don't know how true any of these statements are anymore. My greatest concern in choosing a MySQL interface is preventing SQL Injection. The second concern is performance.

我不知道这些陈述中的任何一个有多真实。我在选择 MySQL 接口时最关心的是防止 SQL 注入。第二个关注点是性能。

My application currently uses procedural MySQLi (without prepared statements), and utilizes the query cache quite a bit. It will rarely re-use prepared statements in a single request. I started the move to PDO for the named parameters and security of prepared statements.

我的应用程序当前使用过程 MySQLi（没有准备好的语句），并且相当多地利用查询缓存。它很少会在单个请求中重用准备好的语句。我开始转向 PDO 以获取命名参数和准备语句的安全性。

I'm using MySQL 5.1.61and PHP 5.3.2

我正在使用MySQL 5.1.61和PHP 5.3.2

Should I leave PDO::ATTR_EMULATE_PREPARESenabled or not? Is there a way to have both the performance of the query cache and the security of prepared statements?

我应该PDO::ATTR_EMULATE_PREPARES启用还是不启用？有没有办法同时拥有查询缓存的性能和准备好的语句的安全性？

Answer 1

采纳答案by Francis Avila

To answer your concerns:

回答您的疑虑：

MySQL >= 5.1.17 (or >= 5.1.21 for the PREPAREand EXECUTEstatements) can use prepared statements in the query cache. So your version of MySQL+PHP can use prepared statements with the query cache. However, make careful note of the caveats for caching query results in the MySQL documentation. There are many kinds of queries which cannot be cached or which are useless even though they are cached. In my experience the query cache isn't often a very big win anyway. Queries and schemas need special construction to make maximum use of the cache. Often application-level caching ends up being necessary anyway in the long run.
Native prepares doesn't make any difference for security. The pseudo-prepared statements will still escape query parameter values, it will just be done in the PDO library with strings instead of on the MySQL server using the binary protocol. In other words, the same PDO code will be equally vulnerable (or not-vulnerable) to injection attacks regardless of your EMULATE_PREPARESsetting. The only difference is where the parameter replacement occurs--with EMULATE_PREPARES, it occurs in the PDO library; without EMULATE_PREPARES, it occurs on the MySQL server.
Without EMULATE_PREPARESyou may get syntax errors at prepare-time rather than at execute-time; with EMULATE_PREPARESyou will only get syntax errors at execution time because PDO doesn't have a query to give to MySQL until execution time. Note that this affects the code you will write! Especially if you are using PDO::ERRMODE_EXCEPTION!

MySQL >= 5.1.17 (or >= 5.1.21 for the PREPAREand EXECUTEstatements)可以在查询缓存中使用准备好的语句。因此，您的 MySQL+PHP 版本可以将预准备语句与查询缓存一起使用。但是，请仔细注意 MySQL 文档中缓存查询结果的注意事项。有多种查询无法缓存或即使缓存也无用。根据我的经验，查询缓存通常不是一个很大的胜利。查询和模式需要特殊的构造才能最大限度地利用缓存。从长远来看，应用程序级缓存通常最终是必要的。
本机准备对安全性没有任何影响。伪准备的语句仍然会转义查询参数值，它只是在 PDO 库中使用字符串完成，而不是在使用二进制协议的 MySQL 服务器上完成。换句话说，无论您的EMULATE_PREPARES设置如何，相同的 PDO 代码都同样容易（或不易受到）注入攻击。唯一的区别是参数替换发生的地方——用EMULATE_PREPARES，它发生在 PDO 库中；没有EMULATE_PREPARES，它发生在 MySQL 服务器上。
如果没有EMULATE_PREPARES，你可能会在准备时间，而不是在执行时语法错误; 与EMULATE_PREPARES您只会在执行时收到语法错误，因为 PDO 在执行时没有提供给 MySQL 的查询。请注意，这会影响您将编写的代码！特别是如果您正在使用PDO::ERRMODE_EXCEPTION!

An additional consideration:

一个额外的考虑：

There is a fixed cost for a prepare()(using native prepared statements), so a prepare();execute()with native prepared statements may be a little slower than issuing a plain textual query using emulated prepared statements. On many database systems the query plan for a prepare()is cached as well and may be shared with multiple connections, but I don't think MySQL does this. So if you do not reuse your prepared statement object for multiple queries your overall execution may be slower.

a prepare()（使用本机准备好的语句）有一个固定的成本，因此prepare();execute()使用本机准备好的语句可能比使用模拟准备好的语句发出纯文本查询慢一点。在许多数据库系统上， a 的查询计划也prepare()被缓存，并且可能与多个连接共享，但我认为 MySQL 不会这样做。因此，如果您不为多个查询重用准备好的语句对象，您的整体执行速度可能会变慢。

As a final recommendation, I think with older versions of MySQL+PHP, you should emulate prepared statements, but with your very recent versions you should turn emulation off.

作为最后的建议，我认为对于旧版本的 MySQL+PHP，您应该模拟准备好的语句，但是对于最近的版本，您应该关闭模拟。

After writing a few apps that use PDO, I've made a PDO connection function which has what I think are the best settings. You should probably use something like this or tweak to your preferred settings:

在编写了一些使用 PDO 的应用程序之后，我制作了一个 PDO 连接功能，它具有我认为最好的设置。您可能应该使用这样的东西或调整到您的首选设置：

/**
 * Return PDO handle for a MySQL connection using supplied settings
 *
 * Tries to do the right thing with different php and mysql versions.
 *
 * @param array $settings with keys: host, port, unix_socket, dbname, charset, user, pass. Some may be omitted or NULL.
 * @return PDO
 * @author Francis Avila
 */
function connect_PDO($settings)
{
    $emulate_prepares_below_version = '5.1.17';

    $dsndefaults = array_fill_keys(array('host', 'port', 'unix_socket', 'dbname', 'charset'), null);
    $dsnarr = array_intersect_key($settings, $dsndefaults);
    $dsnarr += $dsndefaults;

    // connection options I like
    $options = array(
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    );

    // connection charset handling for old php versions
    if ($dsnarr['charset'] and version_compare(PHP_VERSION, '5.3.6', '<')) {
        $options[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES '.$dsnarr['charset'];
    }
    $dsnpairs = array();
    foreach ($dsnarr as $k => $v) {
        if ($v===null) continue;
        $dsnpairs[] = "{$k}={$v}";
    }

    $dsn = 'mysql:'.implode(';', $dsnpairs);
    $dbh = new PDO($dsn, $settings['user'], $settings['pass'], $options);

    // Set prepared statement emulation depending on server version
    $serverversion = $dbh->getAttribute(PDO::ATTR_SERVER_VERSION);
    $emulate_prepares = (version_compare($serverversion, $emulate_prepares_below_version, '<'));
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, $emulate_prepares);

    return $dbh;
}

Answer 2

回答by Sage Pointer

Beware on disabling PDO::ATTR_EMULATE_PREPARES(turning native prepares on) when your PHP pdo_mysqlis not compiled against mysqlnd.

谨防上禁用PDO::ATTR_EMULATE_PREPARES（打开本机上的准备），当你的PHPpdo_mysql的是，不要编译mysqlnd。

Because old libmysqlis not fully compatible with some functions, it can lead to strange bugs, for example:

由于 oldlibmysql与某些功能不完全兼容，因此可能会导致奇怪的错误，例如：

Losing most significant bits for 64bit integers when binding as PDO::PARAM_INT( 0x12345678AB will be cropped to 0x345678AB on 64bit machine )
Inability to make simple queries like LOCK TABLES( it throws SQLSTATE[HY000]: General error: 2030 This command is not supported in the prepared statement protocol yetexception )
Need to fetch all rows from result or close cursor before next query ( with mysqlndor emulated prepares it automatically does this work for you and doesn't go out of sync with mysql server )

绑定时丢失 64 位整数的最高有效位PDO::PARAM_INT（0x12345678AB 在 64 位机器上将被裁剪为 0x345678AB）
无法进行简单的查询，例如LOCK TABLES（它抛出SQLSTATE[HY000]: General error: 2030 This command is not supported in the prepared statement protocol yet异常）
需要在下一次查询之前从结果中获取所有行或关闭游标（使用mysqlnd或模拟准备它会自动为您完成这项工作，并且不会与 mysql 服务器不同步）

These bugs I figured out in my simple project when migrated to other server which used libmysqlfor pdo_mysqlmodule. Maybe there are much more bugs, I don't know. Also I tested on fresh 64bit debian jessie, all listed bugs occur when I apt-get install php5-mysql, and disappear when I apt-get install php5-mysqlnd.

当迁移到libmysql用于pdo_mysql模块的其他服务器时，我在我的简单项目中发现了这些错误。也许还有更多的错误，我不知道。此外，我在新的 64 位 debian jessie 上进行了测试，所有列出的错误在 Iapt-get install php5-mysql时出现，在我apt-get install php5-mysqlnd.

When PDO::ATTR_EMULATE_PREPARESis set to true (as default) - these bugs don't happen anyway, because PDO doesn't use prepared statements at all in this mode. So, if you use pdo_mysqlbased on libmysql("mysqlnd" substring does't appear in "Client API version" field of pdo_mysqlsection in phpinfo) - you should not turn PDO::ATTR_EMULATE_PREPARESoff.

WhenPDO::ATTR_EMULATE_PREPARES设置为 true（默认） - 这些错误无论如何都不会发生，因为 PDO 在这种模式下根本不使用准备好的语句。因此，如果您使用pdo_mysql基于libmysql（“mysqlnd”子字符串未出现pdo_mysql在 phpinfo 部分的“客户端 API 版本”字段中） - 您不应该PDO::ATTR_EMULATE_PREPARES关闭。

Answer 3

回答by Will Morgan

I would turn off emulate prepares as you're running 5.1 which means PDO will take advantage of the native prepared statement functionality.

我会在您运行 5.1 时关闭模拟准备，这意味着 PDO 将利用本机准备好的语句功能。

PDO_MYSQL will take advantage of native prepared statement support present in MySQL 4.1 and higher. If you're using an older version of the mysql client libraries, PDO will emulate them for you.

PDO_MYSQL 将利用 MySQL 4.1 及更高版本中的原生准备语句支持。如果您使用旧版本的 mysql 客户端库，PDO 将为您模拟它们。

http://php.net/manual/en/ref.pdo-mysql.php

I ditched MySQLi for PDO for the prepared named statements and the better API.

为了准备好的命名语句和更好的 API，我放弃了 MySQLi for PDO。

However, to be balanced, PDO performs negligibly slower than MySQLi, but it's something to bear in mind. I knew this when I made the choice, and decided that a better API and using the industry standard was more important than using a negligibly faster library that ties you to a particular engine. FWIW I think the PHP team is also looking favourably at PDO over MySQLi for the future too.

然而，为了平衡，PDO 的执行速度比 MySQLi 慢可以忽略不计，但需要牢记这一点。我在做出选择时就知道这一点，并决定使用更好的 API 和使用行业标准比使用将您与特定引擎联系起来的速度可以忽略不计的库更重要。FWIW 我认为 PHP 团队也很看好 PDO 而不是 MySQLi 的未来。

Answer 4

回答by quickshiftin

I'd recommend enabling real database PREPAREcalls as the emulation doesn't catch everything.., for example, it will prepare INSERT;!

我建议启用真正的数据库PREPARE调用，因为模拟并不能捕获所有内容……例如，它会准备好INSERT;！

var_dump($dbh->prepare('INSERT;'));
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
var_dump($dbh->prepare('INSERT;'));

The output

输出

object(PDOStatement)#2 (1) {
  ["queryString"]=>
  string(7) "INSERT;"
}
bool(false)

I'll gladly take a performance hit for code that actually works.

我很乐意为实际工作的代码带来性能损失。

FWIW

PHP Version: PHP 5.4.9-4ubuntu2.4 (cli)

PHP 版本：PHP 5.4.9-4ubuntu2.4 (cli)

MySQL Version: 5.5.34-0ubuntu0

MySQL 版本：5.5.34-0ubuntu0

Answer 5

回答by dallin

I'm surprised no one has mentioned one of the biggest reasons to turn off emulation. With emulation on, PDO returns all integers and floats as strings. When you turn off emulation, integers and floats in MySQL become integers and floats in PHP.

我很惊讶没有人提到关闭仿真的最大原因之一。启用仿真后，PDO 将所有整数和浮点数作为字符串返回。当您关闭仿真时，MySQL 中的整数和浮点数将变成 PHP 中的整数和浮点数。

For more information, see the accepted answer for this question: PHP + PDO + MySQL: how do I return integer and numeric columns from MySQL as integers and numerics in PHP?.

有关更多信息，请参阅此问题的公认答案：PHP + PDO + MySQL：如何在 PHP 中将整数和数字列从 MySQL 返回为整数和数字？.

Answer 6

回答by Harry Bosh

Why switch emulation to ‘false'?
The main reason for this is that having the database engine do the prepare instead of PDO is that the query and the actual data are sent separately, which increases security. This means when the parameters are passed to the query, attempts to inject SQL into them are blocked, since MySQL prepared statements are limited to a single query. That means that a true prepared statement would fail when passed a second query in a parameter.
The main argument against using the database engine for the prepare vs PDO is the two trips to the server – one for the prepare, and another for the parameters to get passed – but I think the added security is worth it. Also, at least in the case of MySQL, query caching has not been an issue since version 5.1.

为什么将仿真切换为“假”？
这样做的主要原因是让数据库引擎做准备而不是 PDO 是查询和实际数据分开发送，这增加了安全性。这意味着当参数传递给查询时，试图将 SQL 注入其中的尝试会被阻止，因为 MySQL 准备好的语句仅限于单个查询。这意味着当在参数中传递第二个查询时，真正的准备好的语句将失败。
反对在准备与 PDO 中使用数据库引擎的主要论点是两次访问服务器——一次用于准备，另一次用于传递参数——但我认为增加的安全性是值得的。此外，至少在 MySQL 的情况下，查询缓存自 5.1 版以来就不再是问题。

https://tech.michaelseiler.net/2016/07/04/dont-emulate-prepared-statements-pdo-mysql/

php PDO MySQL：是否使用 PDO::ATTR_EMULATE_PREPARES？

提问by Andrew Ensley

采纳答案by Francis Avila

回答by Sage Pointer

回答by Will Morgan

回答by quickshiftin

回答by dallin

回答by Harry Bosh

Why switch emulation to ‘false'?

为什么将仿真切换为“假”？

相关推荐

最近更新

标签

php PDO MySQL：是否使用 PDO::ATTR_EMULATE_PREPARES？

提问by Andrew Ensley

采纳答案by Francis Avila

回答by Sage Pointer

回答by Will Morgan

回答by quickshiftin

回答by dallin

回答by Harry Bosh

Why switch emulation to ‘false'?

为什么将仿真切换为“假”？

相关推荐

php 让 PEAR 在 XAMPP 上工作（Windows 上的 Apache/MySQL 堆栈）

最佳 PHP 数据网格

如何使用 PHP 获取当前年份？

php .htaccess 文件允许访问图像文件夹以查看图片？

相关推荐

最近更新

标签