PDO MySQL: Use PDO::ATTR_EMULATE_PREPARES or not?

by

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

More Related Contents:

  1. PHP PDO and Mysql [closed]
  2. PDO Fetch statement only retrieves first column
  3. PHP – Using PDO with IN clause array
  4. How to properly set up a PDO connection
  5. PHP PDO prepared statements
  6. Getting raw SQL query string from PDO prepared statements
  7. How do I set ORDER BY params using prepared PDO statement?
  8. “Invalid parameter number: parameter was not defined” Inserting data
  9. Having issue with matching rows in the database using PDO
  10. PDO binding values for MySQL IN statement [duplicate]
  11. PDO mysql: How to know if insert was successful
  12. How can I properly use a PDO object for a parameterized SELECT query
  13. How do I return integer and numeric columns from MySQL as integers and numerics in PHP?
  14. implement LIKE query in PDO
  15. PHP PDO prepared statement — MySQL LIKE query
  16. Fetching single row, single column with PDO
  17. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  18. PDO Connection Test
  19. How do I loop through a MySQL query via PDO in PHP?
  20. PDO error: SQLSTATE[HY000]: General error: 2031
  21. Laravel 5.4 on PHP 7.0: PDO Exception – Could not find driver (MySQL)
  22. Using LIKE in bindParam for a MySQL PDO Query [duplicate]
  23. Use of PDO in classes [duplicate]
  24. How to handle PDO exceptions [duplicate]
  25. Check if row exists in the database before inserting
  26. php artisan migrate throwing [PDO Exception] Could not find driver – Using Laravel
  27. PDO and MySQL Fulltext searches
  28. PHP/PDO/MySQL: inserting into MEDIUMBLOB stores bad data
  29. PHP: mysql v mysqli v pdo [closed]
  30. PDO and MySQL ‘between’

Leave a Comment