# PDO::PARAM_INT and pgsql driver - Thread: https://externals.io/message/113521 - Original archive: https://news-web.php.net/php.internals/113521 - Started by Benjamin Morel on 2021-03-14T20:18:53+00:00 - Messages: 6 - Reply by email to internals@lists.php.net - More for agents: search with https://externals.io/search.md?q={query} · RFCs at https://externals.io/rfc.md · JSON API at https://externals.io/api · documentation at https://externals.io/llms.txt --- ## #113521 — Benjamin Morel — 2021-03-14T20:18:53+00:00 URL: https://externals.io/message/113521#113521 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() 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 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 , 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 --- ## #113526 — David Gebler — 2021-03-14T23:03:09+00:00 URL: https://externals.io/message/113521#113526 · In reply to: #113521 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 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 > > […] > > > 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() > 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 > 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 > , 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 --- ## #113534 — Matteo Beccati — 2021-03-15T13:12:31+00:00 URL: https://externals.io/message/113521#113534 · In reply to: #113521 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/ --- ## #113536 — Christoph M. Becker — 2021-03-15T13:30:31+00:00 URL: https://externals.io/message/113521#113536 · In reply to: #113534 > > > […] > > 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 -- Christoph M. Becker --- ## #113644 — Benjamin Morel — 2021-03-21T14:13:23+00:00 URL: https://externals.io/message/113521#113644 · In reply to: #113536 > > […] > > 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 --- ## #113649 — Matteo Beccati — 2021-03-21T16:40:36+00:00 URL: https://externals.io/message/113521#113649 · In reply to: #113644 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 > > […] > PDO::PARAM_STR? > > […] > back, but > > […] > in the > > […] > > Looks like the problem is in > > > > > I've opened a bug report to keep track of it: > https://bugs.php.net/bug.php?id=80892 > > > Thank you, > Benjamin -- Matteo Beccati Development & Consulting - http://www.beccati.com/