Req #75937 [Com]: PDO_DBLIB prepare statements quoting numerics

Edit report at https://bugs.php.net/bug.php?id=75937&edit=1 ID: 75937 Comment by: gregor dot friedl at araplus dot at Reported by: equaproduction at gmail dot com Summary: PDO_DBLIB prepare statements quoting numerics Status: Open Type: Feature/Change Request Package: PDO DBlib PHP Version: Irrelevant Block user comment: N Private report: N New Comment: We witnessed the same problem since upgrading to PHP 7.2. Database: Sybase ASE 15.7 Previously on PHP 7.0.33 we succeeded in binding decimal values with PDO::PARAM_INT $query = 'SELECT 1 WHERE 12.345 = :1'; $stmt = $db->prepare($query); $stmt->bindParam(':1', 12.345, PDO::PARAM_INT); $stmt->execute(); -> fetches 1 in PHP 7.0 - Ok After Upgrading to PHP 7.2.24 these bindings fail, since the values get rounded to integers. $query = 'SELECT 1 WHERE 12.345 = :1'; $stmt = $db->prepare($query); $stmt->bindParam(':1', 12.345, PDO::PARAM_INT); $stmt->execute(); -> fetches no rows in PHP 7.2 According to some comments, we tried binding decimal values with PDO::PARAM_STR, which results in this error. $query = 'SELECT 1 WHERE 12.345 = :1'; $stmt = $db->prepare($query); $stmt->bindParam(':1', 12.345, PDO::PARAM_STR); $stmt->execute(); -> SQLSTATE[HY000]: General error: 20018 Implicit conversion from datatype 'VARCHAR' to 'NUMERIC' is not allowed. Use the CONVERT function to run this query. [20018] (severity 16) [select 1 where 12.345 = '12.345'] Surprisingly the last try (PARAM_STR) works perfectly for Microsoft-SQL Databases! Sybase Database with PHP >= 7.2 seems to be affected only. Previous Comments: ------------------------------------------------------------------------ [2018-02-08 20:39:54] adambaratz@php.net I haven't seen that particular error. Wondering if it's an environmental thing. The pdo_dblib driver is unfortunately limited with how it can handle types. It doesn't support true prepared statements, so the only flexibility would be around what kind of SQL string you can get it to produce. There isn't one that handles decimal values. I had worked on an RFC to deal with this: https://wiki.php.net/rfc/pdo_float_type I had trouble getting traction. Perhaps you have a suggestion on how to revive it? All that said, I'd suggest working a CONVERT call into your query as a workaround. ------------------------------------------------------------------------ [2018-02-08 20:25:45] equaproduction at gmail dot com Actually : PDO::PARAM_BOOL Represents a boolean data type. PDO::PARAM_NULL Represents the SQL NULL data type. PDO::PARAM_INT Represents the SQL INTEGER data type. PDO::PARAM_STR Represents the SQL CHAR, VARCHAR, or other string data type. According to https://wiki.php.net/rfc/pdo_float_type (see Introduction) ; > The PDO extension does not have a type to represent floating point values. > > The current recommended practice is to use PDO::PARAM_STR. ------------------------------------------------------------------------ [2018-02-08 18:24:50] peehaa@php.net Am I missing something here? You are telling PDO the param must be treated as a string using \PDO::PARAM_STR so indeed it is treated as a string. ------------------------------------------------------------------------ [2018-02-08 18:21:57] equaproduction at gmail dot com Description: ------------ When using PDO_DBLIB to prepare statements, numerics are quoted and fatal error is returned. PDOStatement::bindValue() PDOStatement::bindParam() OS : Centos 7.2 PHP : 7.2.2 Freetds : 0.95.81 DBMS : Sybase ASE 12.5.4 & Sybase ASE 16.0 Test script: --------------- // create table foo ( n numeric(14,2) null ) $db = new \PDO("dblib:host=$hostname:$port;dbname=$dbname", "$username", "$pw"); $db->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION); $n = 123.45; $query = 'INSERT INTO foo ( n ) values ( :n )'; $stmt = $db->prepare($query); $stmt->bindParam(':n', $n, \PDO::PARAM_STR); $stmt->execute(); Actual result: -------------- SQLSTATE[HY000]: General error: 20018 Implicit conversion from datatype 'VARCHAR' to 'NUMERIC' is not allowed. Use the CONVERT function to run this query. [20018] (severity 16) [INSERT INTO foo ( n ) values ( '123.45' )] ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=75937&edit=1