php.net |  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
Votes:2
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
Description:
------------
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=1.2.3.4:4100';
$db = new PDO($dsn, 'user', 'password',array(PDO_ATTR_PERSISTENT => 1));
$db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

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


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__]

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

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.

Richard
 [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] ssufficool@php.net
-Package: PDO_DBLIB +Package: PECL
 [2014-10-21 04:17 UTC] ssufficool@php.net
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:
   :myVar
as
   cast(:myVar as int)
 [2014-10-23 03:49 UTC] ssufficool@php.net
-Status: Open +Status: Feedback
 [2014-10-23 03:49 UTC] ssufficool@php.net
Works in trunk with SQL Server 2008.

require("php_pdo_login.php");
$db = new PDO($dsn,$user,$pass);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = "SELECT x from (select cast(1 as int) as x) as tab where x = ?";
$sth = $db->prepare($sql);
$sth->execute(array(1));
while ($row = $sth->fetch(PDO::FETCH_ASSOC)){
    print_r($row);
}
 [2014-10-23 03:55 UTC] ssufficool@php.net
-Status: Feedback +Status: Open
 [2014-10-25 04:36 UTC] ssufficool@php.net
-Assigned To: +Assigned To: ssufficool
 [2014-10-25 04:36 UTC] ssufficool@php.net
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] ssufficool@php.net
-Assigned To: ssufficool +Assigned To:
 [2016-06-26 18:15 UTC] ssufficool@php.net
-Status: Open +Status: Assigned
 [2017-10-24 08:42 UTC] kalle@php.net
-Package: PECL +Package: PDO DBlib
 [2017-10-31 22:01 UTC] adambaratz@php.net
-Status: Open +Status: Duplicate
 [2017-10-31 22:01 UTC] adambaratz@php.net
See #73234.
 
PHP Copyright © 2001-2019 The PHP Group
All rights reserved.
Last updated: Wed Apr 24 22:01:26 2019 UTC