PDO MySQL: Use PDO::ATTR_EMULATE_PREPARES or not?

Question

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.

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.

I'd recommend enabling real database PREPARE calls as the emulation doesn't catch everything.., for example, it will 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)

MySQL Version: 5.5.34-0ubuntu0

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.

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