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.mo...@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
>
