pdo_pgsql Boolean Issues View as Markdown

Hi, all -

There's a bug in the current version of 5.3 and 5.4 with pdo_pgsql and
boolean PDO types. Here's a summary of the issue:

The following cases cause pgsql boolean types to be converted to an
incompatible (long) format:

1. PQprepare is not available (HAVE_PQPREPARE is undefined). This happens
   when the libpq version < 8.0
2. PQprepare is available, but either
   PDO_PGSQL_ATTR_DISABLE_NATIVE_PREPARED_STATEMENT
   or PDO_ATTR_EMULATE_PREPARES are true (emulation handled by PDO, and the
   parameter hook pgsql_stmt_param_hook just skips parameter checks)

This results in PDO converting the parameter to a long (default behavior
for boolean). Take the following example:

$pdo = new PDO($dsn);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$query = $pdo->prepare( 'SELECT :foo IS FALSE as val_is_false' );
$query->bindValue( ':foo', false, PDO::PARAM_BOOL );
$query->execute( );
print_r($query->errorInfo());

This results in the following:

Array
(
[0] => 42804
[1] => 7
[2] => ERROR: argument of IS FALSE must be type boolean, not type
integer at character 8
)

This happens because true and false are converted to their long formats (1
and 0 respectively), which are invalid values for Postgres. However, in
the sole event that PQprepare is available and emulation is disabled,
boolean parameters are correctly converted to "t" and "f".

As noted in bug #62593, disabling emulation fixes the issue. There are a
couple of issues with this approach, though. First, it forces you to make
multiple calls to the server when you actually only need to escape input.
Second, and most important in my case, when using middleware like
pgbouncer, it's not possible to use true prepared statements. The calls
from PQprepare and PQexec will have separate handles.

The attached patch updates the driver to behave like so:

1. Do we have PQprepare and is emulation turned off? If so, let the driver
   handle via PQprepare and PQexec
2. Is PQprepare unavailable? If so, modify the original param by replacing
   the long 1 or 0 format to "t" or "f"
3. Is PQprepare available and emulation turned on? If so, modify the
   original param by replacing the long 1 or 0 format to "t" or "f"

While I've spent the better part of a week trying to determine the best
solution, I want to run this by Ilia, Wez and/or Edin for input. Anyone
else on the list is also encouraged to provide feedback as well.

• Will