PDO not throwing exception with unbound parameters (and no variables in query)

Question

So I have no idea what's going on here

$link = new PDO('pgsql:dbname=' . $name . ';host=' . $host, $user, $password);
$link->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {
    $stmt = $link->prepare("SELECT s.*, d.invalid_column FROM students s ORDER BY s.student_id");
    $stmt->execute(array(1));
}
catch (PDOException $e) {
    print $e->getMessage();
}

When I run this little code example, I expect an exception to be thrown (as d.invalid_column is not a real column and I'm passing in parameters that cannot be bound), but the only thing that happens is that execute returns false and nothing else. Additionally $stmt->errorInfo() is blank and the code is 00000 which makes it hard to add a proper exception throw beyond something super generic with nothing else for the logs to assist me in tracking errors down when some end user reports an error.

If I add a single '?' somewhere to the query, the proper execution is thrown (that d.invalid_column is not a valid column), even if I add more parameters that don't bind to anything.

So ways to get this query to properly error:
1) Get rid all parameters
2) Add a '?' to the query

Is this just a bug in PDO or what?

edit: Setups that will throw an exception (invalid column):

    $stmt = $link->prepare("SELECT s.*, d.invalid_column, ? FROM students s ORDER BY s.student_id");
    $stmt->execute(array(1));

    $stmt = $link->prepare("SELECT s.*, d.invalid_column, ? FROM students s ORDER BY s.student_id");
    $stmt->execute(array(1,2,3));

    $stmt = $link->prepare("SELECT s.*, d.invalid_column, ? FROM students s ORDER BY s.student_id");
    $stmt->execute();

    $stmt = $link->prepare("SELECT s.*, d.invalid_column FROM students s ORDER BY s.student_id");
    $stmt->execute();

It's only when I have no ? in my query and pass something to execute() that things just fail silently and with no explanation from PDO.

Answer 1

That behavior is reproducible with the current PHP (5.6.13), and the query is not even sent to the server.

Your case is described in the doc as:

You cannot bind more values than specified; if more keys exist in input_parameters than in the SQL specified in the PDO::prepare(), then the statement will fail and an error is emitted.

0 value is expected, 1 value is given, and the statement fails, false being returned. So far, works as documented.

You may argue that "an error is emitted" would imply that when ERRMODE_EXCEPTION is on, an exception would be thrown. That's an argument, but it's not obvious that the PDO developers would agree with it.

Update:

Why is SQLCode not set?

Looking at PDO source code, specifically static PHP_METHOD(PDOStatement, execute) that handles PDO::execute(), you can see that all errors are handled by a macro: PDO_HANDLE_STMT_ERR()

#define PDO_HANDLE_STMT_ERR()   if (strcmp(stmt->error_code, PDO_ERR_NONE)) { pdo_handle_error(stmt->dbh, stmt TSRMLS_CC); }

The point is that, when passing a bound parameter when PDO expected none, the query never makes it to the SQL engine, so the SQL engine never has the opportunity to report an error accompanied with an SQLSTATE

PDO itself does not create a fake SQLSTATE on its own, at least no in that case, sostmt->error_code stays at PDO_ERR_NONE which is "00000".

It's understandable that you would prefer an exception to be raised, but then you should suggest that to https://bugs.php.net

Is it the same with MySQL ?

Yes, the root behavior is the same except that with the MySQL driver, the prepare is sent immediately to the SQL engine so if it's incorrect because of a bad column, it fails earlier and with a real SQL error. On the other hand, the PgSQL driver has a different implementation that makes it defer the server-side prepare. This particular behavior is discussed in detail at PHP Postgres PDO driver does not support prepared statement?

Anyway, here's a case with MySQL that demonstrates my explanation, that is:

• the query expects 0 parameter, 1 is given
• $stmt->execute returns false
• no exception gets raised
• PDO::errorCode is 00000

Code:

$link = new PDO('mysql:dbname=' . $name . ';host=' . $host, $user, $password);
$link->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {
    $stmt = $link->prepare("SELECT 1");
    $rc=$stmt->execute(array(1));
   if ($rc===false)
    echo "query failed, errorCode=", $link->errorCode(), "\n";
   else
    echo "query succeeded, errorCode=", $link->errorCode(), "\n";
}
catch (PDOException $e) {
    print "A PDOException has occurred";
    print $e->getMessage();
}

Result:

query failed, errorCode=00000

What happens under the hood is that the prepare is sent to the server and succeeds, but the execute step is cancelled by PDO due to the mismatch in parameters.

Here's a case that differs in the fact that the query refers to a non-existing column. I'm adding a print to show that $stmt->execute is not even called, as the exception is raised by $stmt->prepare

Code:

$link = new PDO('mysql:dbname=' . $name . ';host=' . $host, $user, $password);
$link->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {
    $stmt = $link->prepare("SELECT nonexisting");
    echo "Executing query\n";
    $rc=$stmt->execute(array(1));
   if ($rc===false)
    echo "query failed, errorCode=", $link->errorCode(), "\n";
   else
    echo "query succeeded, errorCode=", $link->errorCode(), "\n";
}
catch (PDOException $e) {
  print "A PDOException has occurred";
    print $e->getMessage();
}

Result:

A PDOException has occurredSQLSTATE[42S22]: Column not found: 1054 Unknown column 'nonexisting' in 'field list'

Note how the "Executing query" step never happens, because it's the prepare that fails, server-side.

Conclusion

• when the query is sent to the server, be it in prepare() or execute(), and it's the server that generates an error, then we can expect a PDOException to be raised.

• when the query is not sent to the server for an execution step, then PDO execute() can fail (returns false) but no exception is thrown and errorCode() stays at 00000

Answer 2

This was a bug in PDO and NikiC has recently fixed it. See bugs #72368 and #79131.

The problem was that PDO was not checking for errors coming from EVT_ALLOC. This was one of a number of issues related to error reporting that got fixed in the past few months.

If any of the methods in PDO return false without throwing an exception in the exception mode, then it is a bug. Please report any future bugs to https://bugs.php.net/ and if possible suggest a GitHub PR to fix it.