PDO::PARAM_INT and pgsql driver View as Markdown

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:

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

Hi,

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/