php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #80892 PDO::PARAM_INT is treated the same as PDO::PARAM_STR
Submitted: 2021-03-21 14:10 UTC Modified: 2024-05-20 08:18 UTC
Votes:23
Avg. Score:4.0 ± 1.0
Reproduced:13 of 14 (92.9%)
Same Version:9 (69.2%)
Same OS:7 (53.8%)
From: benjamin dot morel at gmail dot com Assigned:
Status: Re-Opened Package: PDO PgSQL
PHP Version: 8.0.3 OS: Linux
Private report: No CVE-ID: None
View Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
If you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: benjamin dot morel at gmail dot com
New email:
PHP Version: OS:

 

 [2021-03-21 14:10 UTC] benjamin dot morel at gmail dot com
Description:
------------
As reported on https://externals.io/message/113521

The test script below fails 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 a function without using explicit, PostgreSQL-specific CAST syntax.

I believe this is a bug and the pgsql driver should respect PDO::PARAM_STR just like the mysql driver does.

Test script:
---------------
$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();

Expected result:
----------------
POINT(...)

Actual result:
--------------
PDOException: SQLSTATE[XX000]: Internal error: 7 ERROR: could not parse proj string '4326'

Patches

Pull Requests

Pull requests:

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2021-03-21 14:11 UTC] benjamin dot morel at gmail dot com
Sorry, typo. Should read:

> should respect PDO::PARAM_INT just like the mysql driver does.
 [2021-03-21 16:32 UTC] mbeccati@php.net
-Assigned To: +Assigned To: mbeccati
 [2021-03-24 13:21 UTC] mbeccati@php.net
The following pull request has been associated:

Patch Name: Fix bug #80892 PDO::PARAM_INT on pdo_pgsql
On GitHub:  https://github.com/php/php-src/pull/6801
Patch:      https://github.com/php/php-src/pull/6801.patch
 [2021-04-12 06:23 UTC] git@php.net
Automatic comment on behalf of mbeccati
Revision: https://github.com/php/php-src/commit/340a06778c39054c7d715bceab82175840d92443
Log: Fix #80892: PDO::PARAM_INT is treated the same as PDO::PARAM_STR
 [2021-04-12 06:23 UTC] git@php.net
-Status: Assigned +Status: Closed
 [2021-04-29 10:09 UTC] mbeccati@php.net
-Status: Closed +Status: Re-Opened
 [2021-04-29 10:09 UTC] mbeccati@php.net
Unfortunately the fix led to a fairly significant behaviour change. It has been decided to revert the change and leave the fix for later, possibly 8.1+ only.
 [2021-05-03 08:31 UTC] nredon at solune dot com
A regression occurs with smallint datatype with PostgreSQL.



PLSQL Function :
----------------


CREATE OR REPLACE FUNCTION public.check_int_param(param_int integer) RETURNS text AS
$BODY$BEGIN
RETURN param_int::text; 
END;$BODY$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF
COST 100;

CREATE OR REPLACE FUNCTION public.check_smallint_param(param_smallint smallint) RETURNS text AS
$BODY$BEGIN
RETURN param_smallint::text;
END;$BODY$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF
COST 100;


PHP Test script :
----------------


$pdo = new PDO('pgsql:host=127.0.0.1;port=5432;dbname=test_php', 'postgres', 'postgres');

$val_test = 1234;

$statement = $pdo->prepare("SELECT check_int_param(:param1)"); //Integer datatype : OK
$statement->bindParam(':param1', $val_test, PDO::PARAM_INT);
$statement->execute();
$res = $statement->fetch(\PDO::FETCH_ASSOC);
var_dump($res); 
// array(1) { ["check_int_param"]=> string(4) "1234" }


$statement = $pdo->prepare("SELECT check_smallint_param(:param1)"); //Smallint datatype was OK in PHP 7.3.3 but not anymore.
$statement->bindParam(':param1', $val_test, PDO::PARAM_INT); //No PDO::PARAM_SMALLINT available
$statement->execute();
$res = $statement->fetch(\PDO::FETCH_ASSOC);
var_dump($res);
//Fatal error: Uncaught PDOException: SQLSTATE[42883]: Undefined function: 7 ERROR: function check_smallint_param(integer) does not exist LINE 1: SELECT check_smallint_param($1) ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.



Expected result:
----------------

// array(1) { ["check_int_param"]=> string(4) "1234" }



Workaround :
----------------
$statement = $pdo->prepare("SELECT check_smallint_param(:param1::smallint)");
 [2021-05-03 08:46 UTC] mbeccati@php.net
Thanks nredon at solune dot com for the feedback. That indicates that fixing this bug would potentially be not viable for 8.1 too.
 [2021-05-18 01:40 UTC] vivo50lte at yahoo dot com
The following pull request has been associated:

Patch Name: Add commit ID to build info
On GitHub:  https://github.com/php/doc-base/pull/17
Patch:      https://github.com/php/doc-base/pull/17.patch
 [2024-05-20 08:18 UTC] mbeccati@php.net
-Assigned To: mbeccati +Assigned To:
 [2024-05-20 08:18 UTC] mbeccati@php.net
I don't think this one can be fixed without improvements in PDO to avoid massive regressions.

Removing assignment.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Oct 31 23:01:28 2024 UTC