Everything

php

PDO::PARAM_INT and pgsql driver

php.internals

Benjamin Morel

5 years ago

Hi internals,

I just stumbled upon what I consider to be a bug with the PDO pgsql driver.
*TL;DR: the driver treats parameters bound with PARAM_INT the same as
PARAM_STR.*

Take the following example:

```
$pdo = new PDO('pgsql:host=localhost;port=5432', 'postgres', 'postgres');

$statement = $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 any
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=50206

*Should this be requalified as a bug and be fixed?*

Thanks in advance for your consideration,

— Benjamin

David Gebler

5 years ago

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"]=> string(9) "23 as int"

var_dump( $pdb->query('SELECT baz(\'23\');')->fetchAll() );
["baz"]=>string(12) "23 as string"

$st=$pdb->prepare('SELECT baz(?)'); $st->bindValue(1,3,PDO::PARAM_INT);
$st->execute(); var_dump($st->fetchAll());
["baz"]=>string(11) "3 as string"

Looks like your only option is:
$st=$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"]=>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 = $pdb->prepare('SELECT baz(?)'); $st->bindValue(1,3,PDO::PARAM_INT);
$st->execute(); var_dump($st->fetchAll());
["baz"]=>string(8) "3 as int"

Regards,
David

On Sun, Mar 14, 2021 at 8:19 PM Benjamin Morel <benjamin.morel@gmail.com>
wrote:

Matteo Beccati

5 years ago

Hi,

On 14/03/2021 21:18, Benjamin Morel wrote:

> 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. >
> [...]
>
> Is there any reason why the pgsql driver doesn't respect PDO::PARAM_STR?

Looks like a bug to me. I thought this had been fixed a while back, but
apparently it wasn't. I'll try to find some time to investigate in the
next few days.


Cheers

--
Matteo Beccati

Development & Consulting - http://www.beccati.com/

Christoph Becker

5 years ago

On 15.03.2021 at 14:12, Matteo Beccati wrote:

> On 14/03/2021 21:18, Benjamin Morel wrote:
>> 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. >
>> [...]
>>
>> Is there any reason why the pgsql driver doesn't respect PDO::PARAM_STR?
>
> Looks like a bug to me. I thought this had been fixed a while back, but
> apparently it wasn't. I'll try to find some time to investigate in the
> next few days.

Looks like the problem is in
<https://github.com/php/php-src/blob/php-7.4.16/ext/pdo_pgsql/pgsql_statement.c#L377-L391>

--
Christoph M. Becker

Benjamin Morel

5 years ago

> >> Is there any reason why the pgsql driver doesn't respect PDO::PARAM_STR?
> >
> > Looks like a bug to me. I thought this had been fixed a while back, but
> > apparently it wasn't. I'll try to find some time to investigate in the
> > next few days.
>
> Looks like the problem is in
> <
> https://github.com/php/php-src/blob/php-7.4.16/ext/pdo_pgsql/pgsql_statement.c#L377-L391
> >

I've opened a bug report to keep track of it:
https://bugs.php.net/bug.php?id=80892

Thank you,
Benjamin

Matteo Beccati

5 years ago

Hi Benjamin,

thanks, in fact I was planning to ask you to open a bug report in order
to reference it in the test files and commit message.

I have the fix ready and it doesn't seem to have side effects on the
existing tests. I'd expect to push it in the coming week, thanks for the
reminder ;-)


Cheers

On 21/03/2021 15:13, Benjamin Morel wrote:

>      >> Is there any reason why the pgsql driver doesn't respect
>     PDO::PARAM_STR?
>      >
>      > Looks like a bug to me. I thought this had been fixed a while
>     back, but
>      > apparently it wasn't. I'll try to find some time to investigate
>     in the
>      > next few days.
>
>     Looks like the problem is in
>     <https://github.com/php/php-src/blob/php-7.4.16/ext/pdo_pgsql/pgsql_statement.c#L377-L391
>     <https://github.com/php/php-src/blob/php-7.4.16/ext/pdo_pgsql/pgsql_statement.c#L377-L391>>
>
>
> I've opened a bug report to keep track of it:
> https://bugs.php.net/bug.php?id=80892
> <https://bugs.php.net/bug.php?id=80892>
>
> Thank you,
> Benjamin

--
Matteo Beccati

Development & Consulting - http://www.beccati.com/