Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:113526
MIME-Version: 1.0
References: <CAG9XoMS5QySfvO5eZv=vTiGefcynkOL3mGwq8K5Knq1OQRqWSQ@mail.gmail.com>
In-Reply-To: <CAG9XoMS5QySfvO5eZv=vTiGefcynkOL3mGwq8K5Knq1OQRqWSQ@mail.gmail.com>
Date: Sun, 14 Mar 2021 23:03:09 +0000
Message-ID: <CA+p1FaZJzb-ZmaL2ORuduDR+VWAbKvFi=Q6oSweYGTv7-r_JrQ@mail.gmail.com>
To: PHP Internals <internals@lists.php.net>
Content-Type: multipart/alternative; boundary="000000000000a563f005bd872314"
Subject: Re: [PHP-DEV] PDO::PARAM_INT and pgsql driver
From: davidgebler@gmail.com (David Gebler)

--000000000000a563f005bd872314
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

The information you were given on StackOverflow is somewhat misleading,
since it is referring to the behaviour of PDO::quote(), not anything to do
with binding parameters. The referenced bug report is indeed not a bug.

Still, I don't really use Postgres but a quick smoke test indicates you're
not wrong about the behaviour of bindValue/bindParam on the PG driver when
emulation mode is switched off.

$pdb->exec('CREATE OR REPLACE FUNCTION baz(in int, out f1 text) AS $$
SELECT CAST($1 AS text) || \' as int\' $$ LANGUAGE SQL;');
$pdb->exec('CREATE OR REPLACE FUNCTION baz(in text, out f1 text) AS $$
SELECT $1 || \' as string\' $$ LANGUAGE SQL;');

var_dump( $pdb->query('SELECT baz(23);')->fetchAll() );
 ["baz"]=3D> string(9) "23 as int"

var_dump( $pdb->query('SELECT baz(\'23\');')->fetchAll() );
["baz"]=3D>string(12) "23 as string"

$st=3D$pdb->prepare('SELECT baz(?)'); $st->bindValue(1,3,PDO::PARAM_INT);
$st->execute(); var_dump($st->fetchAll());
["baz"]=3D>string(11) "3 as string"

Looks like your only option is:
$st=3D$pdb->prepare('SELECT baz(CAST(? AS int))');
$st->bindValue(1,3,PDO::PARAM_INT); $st->execute();
var_dump($st->fetchAll());
which gives the expected
["baz"]=3D>string(8) "3 as int"

Whether this is a *bug* I can't really say, there might be a reason
Postgres is implemented this way on PDO, I don't know, I'm not a Postgres
guy.

But the funny thing is it works exactly as expected when emulation is
switched ON:

$pdb->setAttribute(PDO::ATTR_EMULATE_PREPARES,true);
$st =3D $pdb->prepare('SELECT baz(?)'); $st->bindValue(1,3,PDO::PARAM_INT);
$st->execute(); var_dump($st->fetchAll());
  ["baz"]=3D>string(8) "3 as int"

Regards,
David

On Sun, Mar 14, 2021 at 8:19 PM Benjamin Morel <benjamin.morel@gmail.com>
wrote:

> Hi internals,
>
> I just stumbled upon what I consider to be a bug with the PDO pgsql drive=
r.
> *TL;DR: the driver treats parameters bound with PARAM_INT the same as
> PARAM_STR.*
>
> Take the following example:
>
> ```
> $pdo =3D new PDO('pgsql:host=3Dlocalhost;port=3D5432', 'postgres', 'postg=
res');
>
> $statement =3D $pdo->prepare("
>     SELECT ST_AsText(
>         ST_Transform(
>             ST_GeomFromText('POINT(0 0)', 2154),
>             ?
>         )
>     )
> ");
>
> $statement->bindValue(1, 4326, PDO::PARAM_INT);
> $statement->execute();
> ```
>
> This fails with the following message:
>
> PDOException: SQLSTATE[XX000]: Internal error: 7 ERROR: could not parse
> > proj string '4326'
>
>
> This is because the pgsql driver seems to treat everything as PARAM_STR,
> despite being explicitly requested to bind the value as PARAM_INT; the
> placeholder is therefore replaced with the string '4326' instead of the
> integer 4326.
>
> The problem is, in PostGIS, the ST_Transform()
> <https://postgis.net/docs/ST_Transform.html> function has different
> signatures with different behaviours depending on whether the second
> parameter is an integer or a string.
>
> As far as I can see, because of this issue, *there is no way to pass an
> actual integer to ST_Transform()*, which forces me to use
> PostgreSQL-specific cast syntax to get the behaviour I need, in a library
> <https://github.com/brick/geo> I maintain that's supposed to work with an=
y
> GIS-enabled database.
>
> Is there any reason why the pgsql driver doesn't respect PDO::PARAM_STR?
>
> I asked this question on StackOverflow
> <https://stackoverflow.com/q/66625661/759866>, and was pointed to the
> following bug, which I'm not sure is directly related, but was closed as
> "not a bug":
>
> https://bugs.php.net/bug.php?id=3D50206
>
> *Should this be requalified as a bug and be fixed?*
>
> Thanks in advance for your consideration,
>
> =E2=80=94 Benjamin
>

--000000000000a563f005bd872314--