PDO MySQL: Use PDO::ATTR_EMULATE_PREPARES or not?

To answer your concerns:

  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.

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

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

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.

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


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:

  1. Losing most significant bits for 64bit integers when binding as PDO::PARAM_INT ( 0x12345678AB will be cropped to 0x345678AB on 64bit machine )
  2. 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 )
  3. 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.

Tags:

Mysql

Php

Pdo

Recent Posts