php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Request #75937 PDO_DBLIB prepare statements quoting numerics
Submitted: 2018-02-08 18:21 UTC Modified: 2018-02-08 20:41 UTC
Votes:3
Avg. Score:5.0 ± 0.0
Reproduced:3 of 3 (100.0%)
Same Version:2 (66.7%)
Same OS:2 (66.7%)
From: equaproduction at gmail dot com Assigned:
Status: Open Package: PDO DBlib
PHP Version: Irrelevant OS:
Private report: No CVE-ID: None
 [2018-02-08 18:21 UTC] 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' )]

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2018-02-08 18:24 UTC] peehaa@php.net
-Status: Open +Status: Feedback
 [2018-02-08 18:24 UTC] 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 20:25 UTC] 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 20:30 UTC] peehaa@php.net
-Status: Feedback +Status: Open
 [2018-02-08 20:39 UTC] 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:41 UTC] adambaratz@php.net
-Type: Bug +Type: Feature/Change Request -Operating System: Centos 7.2 +Operating System: -PHP Version: 7.2.2 +PHP Version: Irrelevant
 [2020-03-03 12:00 UTC] gregor dot friedl at araplus dot at
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.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sat Nov 23 22:01:28 2024 UTC