PDO exceptions being affected by ATTR_EMULATE_PREPARES with PostgreSQL

Question

I am trying to force PDO to throw an exception when the number of bound variables is bigger than the number of params in the query. I preffer to use native prepare statements.

If i use emulated prepares (PDO::ATTR_EMULATE_PREPARES => true) the exception works fine, i tested using this code:

$Pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = 'SELECT id
        FROM my_table
        WHERE
            doc = :doc';

try {
    $Ps = $Pdo->prepare($sql, array(PDO::ATTR_EMULATE_PREPARES => true));

    $result = $Ps->execute(array(
        ':doc'  => '1234',
        ':name' => 'Myself'
    ));

} catch (PDOException $e) {
    throw new \Exception('Query failed: ' . $e->getMessage());
}

Throws:

Fatal error:  Uncaught exception 'Exception' with message 'Query failed: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens'

But if i change PDO::ATTR_EMULATE_PREPARES to false, no exception is throwed, i just receive false in $result. For security reasons i would like to force exceptions in this case too, but i am not seeing how to reach this. Any suggestions will be welcome.

EDIT 1

Using PDO::ATTR_EMULATE_PREPARES => false, if i do a var_dump($Ps->errorInfo()) i get:

array(3) {
  [0]=>
  string(5) "HY093"
  [1]=>
  int(7)
  [2]=>
  string(0) ""
}

Why would you ever have parameter count mismatch in this case? You are specifically controlling both the SQL generation and the parameter array creation. Why not simply throw your own exception if these do not match your expectations. — Mike Brant, Nov 04 '14 at 15:18
The idea is to prevent unexpected errors, we have some queries that can have dynamic params, so will be nice to know that PDO will thorw an error if something is wrong. I found [this answer](http://stackoverflow.com/questions/10113562) now, maybe the problem is related with our PostgreSQL version, is a little old (8.4.20). — Marcio Mazzucato, Nov 04 '14 at 15:28
Again if you are building the query dynamically, then you should be able to easily count the number of parameters being put into the query and double check the parameter array against this count before trying to execute with it. — Mike Brant, Nov 04 '14 at 15:43
@MikeBrant, I understood your point of view, it's a possibility, but instead of this, i will preffer to use `PDO::ATTR_EMULATE_PREPARES => true` if i didn't find other option. — Marcio Mazzucato, Nov 04 '14 at 15:48
It just seems odd to potentially significantly change the actual querying behavior against the database for something so trivial as being able to validate correct parameter count. — Mike Brant, Nov 04 '14 at 15:49
I get an exception in both cases. Can you transform your question into a self-contained testcase and add your PDO version? — Daniel Vérité, Nov 04 '14 at 15:51
This is extremely odd, whenever you get `false` from `PDO->execute()` the appropriate error handling (exception throwing in your case) should already be executed. -- What does `PDO->errorInfo()` returns in your case? (after `PDO->execute()` has executed) — pozs, Nov 04 '14 at 15:57
Also, not a good practice to set emulate prepares driver option in `PDO->prepare($sql[, $driver_options])` (it is mainly for `[PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL]`) -- could you try without it? (Use the `PDO` constructor, or the `setAttribute()` method to set attributes like this.) — pozs, Nov 04 '14 at 16:03
@pozs, Please see my Edit 1, i posted what i receive from `$Ps->errorInfo()`. When i try to set the driver options using the PDO constructor i get this exception: `SQLSTATE[IM001]: Driver does not support this function: driver does not support setting attributes`. — Marcio Mazzucato, Nov 04 '14 at 17:30
@DanielVérité, At this moment i can't do a self-contained testcase, but i can provide more informations. Is there something specific you want to know? — Marcio Mazzucato, Nov 04 '14 at 17:37
@MarcioSimao it seems there was a bug related to this: https://bugs.php.net/bug.php?id=39845 -- how about setting with `PDO->setAttribute()`? -- also, if you have older version of PHP, you could try set `PDO::PGSQL_ATTR_DISABLE_NATIVE_PREPARED_STATEMENT` to `false` — pozs, Nov 05 '14 at 12:07

score 3 · Answer 1 · edited May 23 '17 at 11:51

First, PostgreSQL itself errors out if we try to execute a prepared statement with a parameter in excess.

Demo in psql:

test=> prepare p as select 1+$1;
PREPARE
test=> execute p(1);
 ?column? 
----------
        2
(1 row)
test=> execute p(1,2);
ERROR:  wrong number of parameters for prepared statement "p"
DETAIL:  Expected 1 parameters but got 2.

When using PHP/PDO with true prepared statements, the PDO layer itself should error out even before passing the query to postgres (the prepare is deferred, this is mentioned with some more details in another question: PHP Postgres PDO driver does not support prepared statement?).

Demo with PHP 5.3.10-1ubuntu3.15, PG 9.1 (Ubuntu 12.04)

<?
$pdo = new PDO('pgsql:dbname=test');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$query = "SELECT 1+:foo";

try {
  $stmt = $pdo->prepare($query);
  $stmt->execute(array(":foo"=>1  ,":other"=>2));
  var_dump($stmt->fetchAll());
}
catch(PDOException $e){
  echo "Error ".  $e->getMessage() ."\n";
} 
?>

This produces:

Error SQLSTATE[HY093]: Invalid parameter number: :other

As a comparison point, the same test with

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

(emulated prepared statements) produces an error, with a different error message, the one mentioned in the question:

Error SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

Make sure you're using a recent version of PHP/PDO.

Excellent explanation, thanks! It should be something trivial, but what is the best way to see the PDO version i am using? — Marcio Mazzucato, Nov 04 '14 at 19:01

PDO exceptions being affected by ATTR_EMULATE_PREPARES with PostgreSQL

1 Answers1