5

Am I losing my mind, or does the Postgres PDO driver just not support prepared statements, but instead emulates them client side?

The following code returns NO ERROR for the prepare() call, even though it should. Instead, it returns the applicable error when execute() is called.

Edit: Since according to Daniel Vérité I'm wrong, I added his suggested code. I still get the error. My code now looks like the below, with Daniel's line added.

<?php
$pdo = new PDO("pgsql:host=myhost;dbname=mydatabase");

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);  // as suggested by Daniel

$sth = $pdo->prepare('COMPLETE GARBAGE');
echo "[prepare] errorInfo = " . print_r($sth->errorInfo(), true);

$sth->execute();
echo "[execute] errorInfo = " . print_r($sth->errorInfo(), true);

PHP version 5.3.15, PHP Postgres client version 9.1.4, Postgres server version 9.2.1.

CXJ
  • 4,301
  • 3
  • 32
  • 62

1 Answers1

9

See http://www.php.net/manual/en/pdo.prepare.php

Note:

Emulated prepared statements does not communicate with the database server so PDO::prepare() does not check the statement.

(in fact real prepared statements are not sent immediately anyway, see answer to Q2 below)

Anyway you may issue:

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

to get real prepared statements implemented with the SQL PREPARE command. See http://www.php.net/manual/en/pdo.setattribute.php for more.

On further discussion and tests, two questions arise:

Q1. Why does pdo::getAttribute(PDO::ATTR_EMULATE_PREPARES) yield an error?
Indeed setAttribute doesn't error out but getAttribute(PDO::ATTR_EMULATE_PREPARES) says:

'SQLSTATE[IM001]: Driver does not support this function: driver does not support that attribute'

Looking at the documentation for pdo::getAttribute, it says The constants that apply to database connections are as follows, and a number of constants follow from PDO::ATTR_AUTOCOMMIT to PDO::ATTR_TIMEOUT, and it's remarkable that PDO::ATTR_EMULATE_PREPARES is not in them. So strictly speaking, we should not expect getAttribute(PDO::ATTR_EMULATE_PREPARES) to work, anyway.

Now looking at the source code to be sure, it appears that the pdo_pgsql driver provides a pdo_pgsql_get_attribute function that has a switch statement on:

  • PDO_ATTR_CLIENT_VERSION
  • PDO_ATTR_SERVER_VERSION
  • PDO_ATTR_CONNECTION_STATUS
  • PDO_ATTR_SERVER_INFO

and that's it. No trace of PDO_ATTR_EMULATE_PREPARES which is why ultimately this error appears.

On the other hand, the function pdo_pgsql_set_attr has a switch statement on:

  • PDO_ATTR_EMULATE_PREPARES
  • PDO_PGSQL_ATTR_DISABLE_NATIVE_PREPARED_STATEMENT

which confirms that this attribute is actually taken into account when set. So PDO is just inconsistent with getAttribute that doesn't match setAttribute.

Q2 - When prepare emulation is false, why doesn't a bogus statement immediately raise an error when prepared?

Consider this piece of code in pgsql_statement.c:

        if (!S->is_prepared) {
stmt_retry:
            /* we deferred the prepare until now, because we didn't
             * know anything about the parameter types; now we do */
            S->result = PQprepare(H->server, S->stmt_name, S->query, 
                        stmt->bound_params ? zend_hash_num_elements(stmt->bound_params) : 0,
                        S->param_types);

It shows that PQprepare is used (so that's a "real" prepared statement), but also that the statement is not immediately sent to the server. That's why the dbo::prepare("bogus statement") won't return false: it's actually not sent to the server for lack of parameter types.

Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
  • The Postgres PDO driver does not implement ATTR_EMULATE_PREPARES. – CXJ Jul 05 '13 at 16:43
  • It certainly appears to be emulated. I'm asking why, when Postgres natively supports prepared statements, and in fact, PHP's "native" API to Postgres, e.g. pg_prepare(), does this correctly. It's the PDO driver that's strange. Is it broken? Undocumented? Just my version? – CXJ Jul 05 '13 at 16:45
  • Prepared statements were implemented in the client-server protocol in PG-7.4 released in 2003. PDO for PG was first released at about the same time, so it had to work with the existing base 7.3 and lower, hence the need for emulation. – Daniel Vérité Jul 05 '13 at 17:32
  • @CXJ: as for your remark that the driver doesn't support ATTR_EMULATE_PREPARES, you're just plain wrong, read the source code. – Daniel Vérité Jul 05 '13 at 17:33
  • $pdo->getAttribute(PDO::ATTR_EMULATE_PREPARES); returns this error message: PHP Warning: PDO::getAttribute(): SQLSTATE[IM001]: Driver does not support this function: driver does not support that attribute in /Users/cxj/test.php on line 10 That sure looks like "does not support" to me. – CXJ Jul 05 '13 at 17:52
  • See revised code in question, now includes $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); – CXJ Jul 05 '13 at 17:58
  • @CXJ: the "driver does not support..." doesn't make sense to me since the [source code](https://github.com/php/php-src/tree/master/ext/pdo_pgsql) has quite a lot of "if real prepares are on, then do this, else do that...". I'll dig deeper and update the answer – Daniel Vérité Jul 05 '13 at 18:17
  • @CXJ: so I've digged into the source code and updated the answer according to what I've found. – Daniel Vérité Jul 05 '13 at 19:27
  • It appears PDO _never_ sends the prepared statement to the server before execute() is called, regardless of parameters. Correct? libpq does support prepared statements without parameter types; it will infer the types. See PQprepare() and PQdescribePrepared() in the [libq documentation](http://www.postgresql.org/docs/9.2/static/libpq-exec.html). – CXJ Jul 05 '13 at 19:53
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/32973/discussion-between-cxj-and-daniel-verite) – CXJ Jul 05 '13 at 19:54
  • When emulation is on PDO has a bug where it turns INT params into strings after execute, we can use this to check if emulation is on: `function isEmulated(){ $stmt = $pdo_dbh->prepare('SELECT ? AS x'); $int = 1; $stmt->bindParam(1, $int, PDO::PARAM_INT); $stmt->execute(); return !is_int($int); }` – Timo Huovinen Oct 26 '13 at 15:11