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:
- PQprepare is not available (HAVE_PQPREPARE is undefined). This happens
when the libpq version < 8.0 - 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:
- Do we have PQprepare and is emulation turned off? If so, let the driver
handle via PQprepare and PQexec - Is PQprepare unavailable? If so, modify the original param by replacing
the long 1 or 0 format to "t" or "f" - 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
hi Will,
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.
Thanks for your detailed report!
Could you please open a bug report and attach the bug to it please? It
will be much easier to track and fix the issue(s).
You can also create a pull request on github as this patch may need
some work or testing (incl. adding tests). It could be easier to have
a PR then.
Cheers,
Pierre
@pierrejoye | http://blog.thepimp.net | http://www.libgd.org
Thanks, Pierre - The PR can be found at
https://github.com/php/php-src/pull/198
hi Will,
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.Thanks for your detailed report!
Could you please open a bug report and attach the bug to it please? It
will be much easier to track and fix the issue(s).You can also create a pull request on github as this patch may need
some work or testing (incl. adding tests). It could be easier to have
a PR then.Cheers,
Pierre
@pierrejoye | http://blog.thepimp.net | http://www.libgd.org
I'll make a point of reviewing this over the weekend; thanks!
--Wez.
Thanks, Pierre - The PR can be found at
https://github.com/php/php-src/pull/198
Going to bump this thread.
https://bugs.php.net/bug.php?id=62593
https://github.com/php/php-src/pull/198
The pull request hasn't had feedback in over a week, and I'd like to make
some progress as this is preventing some from moving to 5.3/5.4.
I'll make a point of reviewing this over the weekend; thanks!
--Wez.
Thanks, Pierre - The PR can be found at
https://github.com/php/php-src/pull/198
This request was given a +1 from Wez - does anyone else want to provide
feedback? If not, can we get it merged to trunk and queued for release?
Going to bump this thread.
https://bugs.php.net/bug.php?id=62593
https://github.com/php/php-src/pull/198The pull request hasn't had feedback in over a week, and I'd like to make
some progress as this is preventing some from moving to 5.3/5.4.I'll make a point of reviewing this over the weekend; thanks!
--Wez.
Thanks, Pierre - The PR can be found at
https://github.com/php/php-src/pull/198