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的本机准备更适合于错误报告.

1. PDO's prepare emulation is better for performance since MySQL's native prepare bypasses the query cache.
2. MySQL's native prepare is better for security (preventing SQL Injection).
3. MySQL's native prepare is better for error reporting.

我不知道这些陈述的真实性.在选择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)

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屋！