|  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #57655 PDO_DBLIB prepare statements quoting integers
Submitted: 2007-05-08 14:10 UTC Modified: 2017-10-31 22:01 UTC
Avg. Score:4.5 ± 0.5
Reproduced:2 of 2 (100.0%)
Same Version:0 (0.0%)
Same OS:2 (100.0%)
From: nkgrant at gmail dot com Assigned:
Status: Duplicate Package: PDO DBlib
PHP Version: 5.2.1 OS: Linux (Debian Sarge)
Private report: No CVE-ID: None
 [2007-05-08 14:10 UTC] nkgrant at gmail dot com
When using PDO_DBLIB to prepare statements, integers are quoted and fatal error is returned.

The freetds.log contains the actual query statement sent to the database server (Sybase ASE 11) and the integer value in the query statement was quoted. I tested a prepared statement using the PEAR::DB method and it worked fine.

using freetds-stable. (0.63)

php 5.2.1 config line:
./configure  --with-apache=../apache_1.3.33 --enable-gd-imgstrttf --enable-gd-native-ttf --enable-memory-limit --enable-sablot-errors-descriptive --enable-track-vars --enable-trans-sid --enable-xslt --with-config-file-path=/www/conf --with-curl=/usr/local/lib --with-expat --with-fdftk=/usr --with-freetype-dir=/usr --with-gd-native-ttf --with-gd --with-jpeg-dir=../jpeg-6b --with-openssl --with-png --with-regex --with-sablot-js --with-sybase-ct=/usr/local --with-xml --with-xmlrpc --with-xslt-sablot --with-zlib --enable-ftp --enable
-pdo -with-pdo-dblib --with-xsl --with-mysql --with-gmp --with-pdo-odbc=ibm-db2,/home/db2inst1/sqllib

Thanks for any help.

Reproduce code:
$dsn = 'dblib:host=';
$db = new PDO($dsn, 'user', 'password',array(PDO_ATTR_PERSISTENT => 1));

$sql = "SELECT x from y..z where x = ?";
$sth = $db->prepare($sql);
while ($row = $sth->fetch(PDO::FETCH_ASSOC)){

Expected result:
Array ( [x] => 1 )

Actual result:
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 20018 Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed. Use the CONVERT function to run this query. [20018] (severity 5) [(null)]' in [__FILE__]:[__LINE__] Stack trace: #0 [__FILE__]([__LINE__]): PDOStatement->execute(Array) #1 {main} thrown in [__FILE__] on line [__LINE__]


Add a Patch

Pull Requests

Add a Pull Request


AllCommentsChangesGit/SVN commitsRelated reports
 [2007-10-10 11:14 UTC] richardtector at thekeelecentre dot com
bindParam() should allow you to force it to handle it as an integer.

I must admit, I find it frustrating that you can't simply specify the parameter types during the prepare as in DB/MDB2 where you just pass an array of types: array('integer', 'boolean') etc.

Hope that helps.

 [2009-08-17 13:01 UTC] a at a dot com
Unfortunately, it does not work even with bindParam(..., PDO::PARAM_INT). 

I've tried many (all?) potential solutions, but did not found any, except using convert()
 [2014-10-21 04:17 UTC]
-Package: PDO_DBLIB +Package: PECL
 [2014-10-21 04:17 UTC]
All PDO_DBLIB binds are done as strings. To quote one of the PDO authors: "...strings give the greatest fidelity...". There is no formal specification for how PDO *should* bind PHP zvals to SQL Variables. This may change in the future.

The workaround would be to bind:
   cast(:myVar as int)
 [2014-10-23 03:49 UTC]
-Status: Open +Status: Feedback
 [2014-10-23 03:49 UTC]
Works in trunk with SQL Server 2008.

$db = new PDO($dsn,$user,$pass);

$sql = "SELECT x from (select cast(1 as int) as x) as tab where x = ?";
$sth = $db->prepare($sql);
while ($row = $sth->fetch(PDO::FETCH_ASSOC)){
 [2014-10-23 03:55 UTC]
-Status: Feedback +Status: Open
 [2014-10-25 04:36 UTC]
-Assigned To: +Assigned To: ssufficool
 [2014-10-25 04:36 UTC]
PECL module is not maintained. Will try to fix in master. 

This only affects Sybase ASE as it does not have the same implicit conversions supported by MS SQL Server.

Binding variables according to ZVAL type or specific binding type may result in BC breakage.
 [2016-06-26 18:15 UTC]
-Assigned To: ssufficool +Assigned To:
 [2016-06-26 18:15 UTC]
-Status: Open +Status: Assigned
 [2017-10-24 08:42 UTC]
-Package: PECL +Package: PDO DBlib
 [2017-10-31 22:01 UTC]
-Status: Open +Status: Duplicate
 [2017-10-31 22:01 UTC]
See #73234.
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Thu Dec 09 10:03:36 2021 UTC