Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:63041
Mailing-List: contact internals-help@lists.php.net; run by ezmlm
Received-SPF: pass (pb1.pair.com: domain meetme.com designates 74.125.149.71 as permitted sender)
MIME-Version: 1.0
Sender: wfitch@meetme.com
Date: Mon, 17 Sep 2012 13:45:17 -0400
Message-ID: <CAArmra1vgXt3iPUXoVxwdMdhayx1ezSuFjyZqnAfUELTFuO2Cg@mail.gmail.com>
To: Ilia Alshanestsky <ilia@prohost.org>, Wez Furlong <wez@php.net>, Edin Kadribasic <edink@emini.dk>
Cc: internals@lists.php.net
Content-Type: multipart/mixed; boundary=bcaec504091231d5a004c9e95613
Subject: pdo_pgsql Boolean Issues
From: willfitch@php.net (Will Fitch)

--bcaec504091231d5a004c9e95613
Content-Type: multipart/alternative; boundary=bcaec504091231d59204c9e95611

--bcaec504091231d59204c9e95611
Content-Type: text/plain; charset=ISO-8859-1

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

--bcaec504091231d59204c9e95611
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi, all -<div><br></div><div>There&#39;s a bug in the current version of 5.=
3 and 5.4 with pdo_pgsql and boolean PDO types. =A0Here&#39;s a summary of =
the issue:</div><div><br></div><div>The following cases cause pgsql boolean=
 types to be converted to an incompatible (long) format:</div>
<div><br></div><div>1. PQprepare is not available (HAVE_PQPREPARE is undefi=
ned). This happens when the libpq version &lt; 8.0</div><div>2. PQprepare i=
s available, but either PDO_PGSQL_ATTR_DISABLE_NATIVE_PREPARED_STATEMENT or=
=A0PDO_ATTR_EMULATE_PREPARES are true (emulation handled by PDO, and the pa=
rameter hook=A0pgsql_stmt_param_hook just skips parameter checks)</div>
<div><br></div><div>This results in PDO converting the parameter to a long =
(default behavior for boolean). =A0Take the following example:</div><div><b=
r></div><div>$pdo =3D new PDO($dsn);</div><div>$pdo-&gt;setAttribute(PDO::A=
TTR_EMULATE_PREPARES, true);=A0</div>
<div><div>$query =3D $pdo-&gt;prepare( &#39;SELECT :foo IS FALSE as val_is_=
false&#39; );</div><div>$query-&gt;bindValue( &#39;:foo&#39;, false, PDO::P=
ARAM_BOOL );</div><div>$query-&gt;execute( );</div><div>print_r($query-&gt;=
errorInfo());</div>
</div><div><br></div><div>This results in the following:</div><div><br></di=
v><div><div>Array</div><div>(</div><div>=A0 =A0 [0] =3D&gt; 42804</div><div=
>=A0 =A0 [1] =3D&gt; 7</div><div>=A0 =A0 [2] =3D&gt; ERROR: =A0argument of =
IS FALSE must be type boolean, not type integer at character 8</div>
<div>)</div></div><div><br></div><div>This happens because true and false a=
re converted to their long formats (1 and 0 respectively), which are invali=
d values for Postgres. =A0However, in the sole event that PQprepare is avai=
lable and emulation is disabled, boolean parameters are correctly converted=
 to &quot;t&quot; and &quot;f&quot;. =A0</div>
<div><br></div><div>As noted in bug #62593, disabling emulation fixes the i=
ssue. =A0There are a couple of issues with this approach, though. =A0First,=
 it forces you to make multiple calls to the server when you actually only =
need to escape input. =A0Second, and most important in my case, when using =
middleware like pgbouncer, it&#39;s not possible to use true prepared state=
ments. =A0The calls from PQprepare and PQexec will have separate handles.</=
div>
<div><br></div><div>The attached patch updates the driver to behave like so=
:</div><div><br></div><div>1. Do we have PQprepare and is emulation turned =
off? =A0If so, let the driver handle via PQprepare and PQexec</div><div>2. =
Is PQprepare unavailable? If so, modify the original param by replacing the=
 long 1 or 0 format to &quot;t&quot; or &quot;f&quot;</div>
<div>3. Is PQprepare available and emulation turned on? If so, modify the o=
riginal param by replacing the long 1 or 0 format to &quot;t&quot; or &quot=
;f&quot;</div><div><br></div><div>While I&#39;ve spent the better part of a=
 week trying to determine the best solution, I want to run this by Ilia, We=
z and/or Edin for input. =A0Anyone else on the list is also encouraged to p=
rovide feedback as well.</div>
<div><br></div><div>- Will</div>

--bcaec504091231d59204c9e95611--
--bcaec504091231d5a004c9e95613--