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:1
Avg. Score:3.0 ± 0.0
Reproduced:0 of 0 (0.0%)
From: equaproduction at gmail dot com Assigned:
Status: Open Package: PDO DBlib
PHP Version: Irrelevant OS:
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: equaproduction at gmail dot com
New email:
PHP Version: OS:

 

 [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

Add a Patch

Pull Requests

Add a Pull Request

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
 
PHP Copyright © 2001-2019 The PHP Group
All rights reserved.
Last updated: Mon May 20 16:01:26 2019 UTC