PDO MySQL:是否使用 PDO::ATTR_EMULATE_PREPARES? [英] PDO MySQL: Use PDO::ATTR_EMULATE_PREPARES or not?

问题描述

这是我目前所读到的关于 PDO::ATTR_EMULATE_PREPARES:

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

1. 由于 MySQL 的本机准备绕过查询缓存，PDO 的准备模拟对性能更好.
2. MySQL 的原生准备在安全性方面更好(防止 SQL 注入).
3. MySQL 的原生准备更适合错误报告.

我不知道这些陈述有多真实.我在选择 MySQL 接口时最关心的是防止 SQL 注入.第二个问题是性能.

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.

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

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.

我正在使用 MySQL 5.1.61 和 PHP 5.3.2

我是否应该启用 PDO::ATTR_EMULATE_PREPARES?有没有办法兼顾查询缓存的性能和预处理语句的安全性?

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

推荐答案

回答您的疑虑:

1. MySQL >= 5.1.17(对于 PREPARE 和 EXECUTE 语句，>= 5.1.21)可以在查询缓存中使用准备好的语句.因此，您的 MySQL+PHP 版本可以将准备好的语句与查询缓存一起使用.但是，请仔细注意 MySQL 文档中缓存查询结果的注意事项.有多种查询无法缓存或即使缓存也无用.根据我的经验，查询缓存通常不是一个很大的胜利.查询和模式需要特殊的构造才能最大限度地利用缓存.从长远来看，应用级缓存通常最终是必要的.

1. MySQL >= 5.1.17 (or >= 5.1.21 for the PREPARE and EXECUTE statements) 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.

本机准备对安全性没有任何影响.伪准备的语句仍然会转义查询参数值，它只会在 PDO 库中使用字符串完成，而不是在使用二进制协议的 MySQL 服务器上完成.换句话说，无论您的 EMULATE_PREPARES 设置如何，相同的 PDO 代码都同样容易(或不易受到)注入攻击.唯一的区别是参数替换发生的位置——使用 EMULATE_PREPARES，它发生在 PDO 库中；如果没有 EMULATE_PREPARES，它会发生在 MySQL 服务器上.

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_PREPARES setting. 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.

如果没有 EMULATE_PREPARES，您可能会在准备时而不是执行时遇到语法错误；使用 EMULATE_PREPARES 你只会在执行时得到语法错误，因为 PDO 直到执行时才向 MySQL 提供查询.请注意，这会影响您将编写的代码！特别是如果您使用 PDO::ERRMODE_EXCEPTION!

Without EMULATE_PREPARES you may get syntax errors at prepare-time rather than at execute-time; with EMULATE_PREPARES you 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!

额外的考虑:

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

• 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.

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

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.

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

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:

/**
 * 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;
}

这篇关于PDO MySQL:是否使用 PDO::ATTR_EMULATE_PREPARES?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！