PDO MySQL: Use PDO::ATTR

 

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

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

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

    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.

    I'm using MySQL 5.1.61 and PHP 5.3.2

    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?

    To answer your concerns:

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

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

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

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

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

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

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

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

  • 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 yet exception )
  • Need to fetch all rows from result or close cursor before next query ( with mysqlnd or emulated prepares it automatically does this work for you and doesn't go out of sync with mysql server )

    • These bugs I figured out in my simple project when migrated to other server which used libmysql for pdo_mysql module. 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 .

    When PDO::ATTR_EMULATE_PREPARES is 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_mysql based on libmysql ("mysqlnd" substring does't appear in "Client API version" field of pdo_mysql section in phpinfo) - you should not turn PDO::ATTR_EMULATE_PREPARES off.

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

    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.

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

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

    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.

    链接地址: http://www.djcxy.com/p/93780.html

    上一篇: 准备好的语句和二阶SQL注入

    下一篇: PDO MySQL：使用PDO :: ATTR