Pdo MySQL: Use Pdo::Attr_Emulate_Prepares or Not

PDO MySQL: Use PDO::ATTR_EMULATE_PREPARES or not?

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

Understand PDO::ATTR_EMULATE_PREPARES = false completly

You can bind on your own

EMULATE_PREPARES applies to both →bindParam and →execute likewise.

If you do not bind, the parameter is automatically set back to PDO :: ATTR_EMULATE_PREPARES = true

No. The PDO setting has no influence on manually interpolated tidbits. If that's what "not bind" was supposed to mean.

If EMULATE_PREPARES was enabled, it applied to any bound parameter. Per default it typecasts any input to strings, escapes it, and interpolates it as '$escaped_str' in place of the placeholder.

The difference is that execute() interprets all parameters as strings per default, whereas bindParam can typecast and interpolate integers/floats as literals.

I have a feeling this question is based on the assumption that more long-winded code improves security. It doesn't. The only reason to use →bindParam in either case is to work around the lack of typecasting on some MySQL query contexts (such as the LIMIT clause). (Albeit LIMIT 1*?, 1*? is often the better workaround there.)

PDO :: ATTR_EMULATE_PREPARES = false is a bit more secure

Sure.

You should pay attention to whether you get false from the request with PDO :: ATTR_EMULATE_PREPARES = false

Not gonna happen in reality. The support for emulation is driver-dependent. It's not going to change at runtime for the same database driver and database server.

And you can't really check it, since EMULATE_PREPARES is a initialization option, not something that you usually adapt per PDO::setAttribute in between queries.

Ok so: $stmt = $pdo->prepare("SELECT id, max_bench, max_squat FROM myTable WHERE weight < ?"); $stmt->execute([$int]); AND $stmt = $pdo->prepare("SELECT id, max_bench, max_squat FROM myTable WHERE weight < ?"); $stmt->bindParam(1, $int, PDO::PARAM_INT); $stmt->execute(); does the same with PDO :: ATTR_EMULATE_PREPARES = false and also is same secure?

It's mostly the same, if $int was an integer already. bindParam() only does a secondary typecast, if need be. Irrelevant since MySQL would do the same on its end anyway, depending on the target columns type.

Is PDO still emulating prepared statements for MySQL?

The answer you are referring to is more like a scary tale than a real help. If you read the fine print at the bottom, it says that with actual software versions you are all right (actual means released past 2010).

So you can tell that security-wise there is no difference whether prepared statements are emulated or not. Hence, the answer to your question is not that important.

Besides, you incorrectly understood a certain statement from it.

However, be aware that PDO will silently fallback to emulating statements that MySQL can't prepare natively

It doesn't mean then mysql doesn't support native prepared statements at all. It means that only for some certain kinds of queries mysql does not support prepared statements. For such queries you don't have too much a choice, so it doesn't really matter again.

To make it clear

PDO does still emulate prepared statements for MySQL by default, when no option is set.
For the most used query types such as SELECT, INSERT, UPDATE and such, PDO does not emulate prepared statements for MySQL if explicitly told to use native statements. by the way, the list of supported statements is quite inclusive
as this behavior is decided on the server side, changing PHP API from PDO to mysqli won't help.
for some rarely used query types it may silently fallback to emulating statements but it is neither a security concern nor you have a choice anyway.

To sum it up:

For convenience sake, disable the emulation as a connection option. Means you have to change your current single-line connection to a full-blown PDO connection script which I suggest as a canonical example and then just move on.

Why does PDO::ATTR_EMULATE_PREPARES = TRUE returns all values as strings

Provided that

PDO will emulate prepared statements/bound parameters for drivers that do not natively support them

... this feature (emulated prepared statements) is probably handled by a piece of code that is common to all drivers.

However, based on the fact that functions like PDOStatement::getColumnMeta() are not implemented for all drivers,

Not all PDO drivers support PDOStatement::getColumnMeta().

... I assume that the PDO common code base is unable to determine the database column type in a consistant, cross-database manner, and therefore must fall-back to a generic string type.

Unfortunately, the source code is far too obscure for me to verify these assumptions.

PDO::ATTR_EMULATE_PREPARES (multiple bind & limit)

But when I use this, another query with LIMIT in it doesn't work

You can change the emulation mode on the fly, turning it on and off when required.

I also tried to use bindValue function in combination with PDO::PARAM_INT but still It doesn't handle LIMIT value as INT.

Make sure that you did cast the value to an int explicitly, as PDO doesn't cast them for PDO::PARAM_INT.

Pdo MySQL: Use Pdo::Attr_Emulate_Prepares or Not