Disabling PDO::ATTR_EMULATE_PREPARES causing 'unknown' issue

Question

Just a quick question regarding PDO's ATTR_EMULATE_PREPARES attribute- simply put, while left on default (true) everything works fine and dandy. Disable it however and, well, I don't even get a PHP error message, just a browser warning telling me that "the connection was reset".

For reference here is a sample of the code I was using

<?php
include_once("config.php");

try {
  $dbh = new PDO
  (
    "mysql:host=". DB_SERVER .";dbname=" . DB_NAME,
    DB_USER,
    DB_PASS,
    array
    (
      PDO::ATTR_PERSISTENT => true,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
      PDO::ATTR_EMULATE_PREPARES => true
    )
  );
} catch(PDOException $e) {
  echo "<pre>";
  print_r("Error: " . $e);
  echo "</pre>";
  die();
}

$idNum = "1";

$sth = $dbh->prepare("SELECT * FROM `table` WHERE `id` = ?;");
$sth->bindParam(1,$idNum);
$sth->execute();
$res = $sth->fetch();
?>

<pre>
<?=print_r($res); ?>
</pre>

Which nicely returns the query from my lovely test table...

Array
(
    [id] => 1
    [field1] => q12w3e4r5t6y7u8i9
    [field2] => kijhgbfvcdoikujyh
)

However were I to have the temerity to set the value of PDO::ATTR_EMULATE_PREPARES to false it would simply fail, and fail again until I return it to its original value. Is there anything I can do to find out what is causing this or have I missed something really simple?

My PHP version is currently 5.4.3 and MySQL is 5.5.24

Answer 1

This looks to be a bug in certain PHP versions:

https://bugs.php.net/bug.php?id=61411

It seems there is a problem running both

PDO::ATTR_PERSISTENT => true

and

PDO::ATTR_EMULATE_PREPARES => true

Which you have in your PDO attributes/options array.

Answer 2

Hi I figured out how to solve your (and mine as well) problem.

Experienced the same issue, just failure of the connection, no errors. For some reason it doesn't work because you added the ATTR_EMULATE_PREPARES option in the PDO-configuration array when you call the constructor of the PDO database-handler, somehow this is making PDO crash.

When you initiate the PDO database-handler without the ATTR_EMULATE_PREPARES option and then use setAttribute to disable emulation, it will work. Thus like this:

// Configure PDO to really prepare statements and to not emulate them
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

For people just reading this and wondering why turn off ATTR_EMULATE_PREPARES if possible, read: how safe are PDO prepared statements

Answer 3

I had the same problem and found two reasons this can occur when ATTR_EMULATE_PREPARES = false:

1. If a Select statement contains both the AND/OR operands, the query may fail. I had to separate these into different queries.

2. If an Insert contains bindValue holders, ATTR_EMULATE_PREPARES = false is more strict about making these holder names match exactly.

Answer 4

PHP`s MYSQL driver does not really support prepared statements, it has very poor performance. Lots of errors can emerge from disabling emulated statements for mysql.

I`ve just hit my head on this issue a couple of days ago.

Have this:

public at grik dot net 07-Mar-2012 04:23

With PDO_MYSQL you need to remember about the PDO::ATTR_EMULATE_PREPARES option.

The default value is TRUE, like $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,true);

This means that no prepared statement is created with $dbh->prepare() call. With exec() call PDO replaces the placeholders with values itself and sends MySQL a generic query string.

The first consequence is that the call $dbh->prepare('garbage'); reports no error. You will get an SQL error during the $dbh->exec() call. The second one is the SQL injection risk in special cases, like using a placeholder for the table name.

The reason for emulation is a poor performance of MySQL with prepared statements. Emulation works significantly faster.

Source: user contributed note.