From: ethan dot nelson at ltd dot org
Operating system: win2k3
PHP version: 5.2.5
PHP Bug Type: PDO related
Bug description: bound parameters ignore explicit type definitions
Description:
------------
There is a type switching problem with bound parameters in PDO when the
query contains a WHERE clause:
works - "SELECT * FROM (SELECT 'test' = 1) a"
works - "SELECT * FROM (SELECT 'test' = 1) a WHERE a.test = 1"
fails - "SELECT * FROM (SELECT 'test' = :id) a WHERE a.test = 1"
works - "SELECT * FROM (SELECT 'test' = 1) a WHERE a.test = :id"
fails - "SELECT * FROM (SELECT 'test' = :id) a WHERE a.test = :id2"
Reproduce code:
---------------
$con = new PDO('odbc:Brokerage EPC Database');
$query = "SELECT * FROM (SELECT 'test' = 1) a";
$stm = $con->prepare($query);
echo $query."<br>\n";
if ($stm->execute()) echo "Yea!<p>\n";
else echo "Boo!<p>\n";
$query = "SELECT * FROM (SELECT 'test' = 1) a WHERE a.test = 1";
$stm = $con->prepare($query);
echo $query."<br>\n";
if ($stm->execute()) echo "Yea!<p>\n";
else echo "Boo!<p>\n";
$query = "SELECT * FROM (SELECT 'test' = :id) a WHERE a.test = 1";
$stm = $con->prepare($query);
echo $query."<br>\n";
$id = 1;
$stm->bindParam(':id',$id,PDO::PARAM_INT);
if ($stm->execute()) echo "Yea!<p>\n";
else echo "Boo!<p>\n";
$query = "SELECT * FROM (SELECT 'test' = 1) a WHERE a.test = :id2";
$stm = $con->prepare($query);
echo $query."<br>\n";
$id2 = 1;
$stm->bindParam(':id2',$id2,PDO::PARAM_INT);
if ($stm->execute()) echo "Yea!<p>\n";
else echo "Boo!<p>\n";
$query = "SELECT * FROM (SELECT 'test' = :id) a WHERE a.test = :id2";
$stm = $con->prepare($query);
echo $query."<br>\n";
$id = 1;
$stm->bindParam(':id',$id,PDO::PARAM_INT);
$id2 = 1;
$stm->bindParam(':id2',$id2,PDO::PARAM_INT);
if ($stm->execute()) echo "Yea!<p>\n";
else echo "Boo!<p>\n";
Expected result:
----------------
SELECT * FROM (SELECT 'test' = 1) a
Yea!
SELECT * FROM (SELECT 'test' = 1) a WHERE a.test = 1
Yea!
SELECT * FROM (SELECT 'test' = :id) a WHERE a.test = 1
Yea!
SELECT * FROM (SELECT 'test' = 1) a WHERE a.test = :id2
Yea!
SELECT * FROM (SELECT 'test' = :id) a WHERE a.test = :id2
Yea!
Actual result:
--------------
SELECT * FROM (SELECT 'test' = 1) a
Yea!
SELECT * FROM (SELECT 'test' = 1) a WHERE a.test = 1
Yea!
SELECT * FROM (SELECT 'test' = :id) a WHERE a.test = 1
Boo!
SELECT * FROM (SELECT 'test' = 1) a WHERE a.test = :id2
Yea!
SELECT * FROM (SELECT 'test' = :id) a WHERE a.test = :id2
Boo!
Clearly, by the time the bound parameters hit the database, they've been
turned into text type parameters even though they were explicitly bound as
int parameters.
Error message:
Warning: PDOStatement::execute() [function.PDOStatement-execute]:
SQLSTATE[22018]: Invalid character value for cast specification: 206
[Microsoft][SQL Native Client][SQL Server]Operand type clash: text is
incompatible with int (SQLExecute[206] at ext\pdo_odbc\odbc_stmt.c:133) in
D:\Inetpub\include\config.inc on line 173
SQL Profiler trace:
declare @p1 int
set @p1=NULL
exec sp_prepare @p1 output,N'@P1 text,@P2 text',N'SELECT * FROM
ptrips_readable() a WHERE a.ptripgroupid = (
SELECT ptripgroupid FROM ptrips_readable() b WHERE b.ptripid = @P1
) AND a.actiontypeid <> @P2',1
select @p1
--
Edit bug report at http://bugs.php.net/?id=44643&edit=1
--
Try a CVS snapshot (PHP 5.2):
http://bugs.php.net/fix.php?id=44643&r=trysnapshot52
Try a CVS snapshot (PHP 5.3):
http://bugs.php.net/fix.php?id=44643&r=trysnapshot53
Try a CVS snapshot (PHP 6.0):
http://bugs.php.net/fix.php?id=44643&r=trysnapshot60
Fixed in CVS: http://bugs.php.net/fix.php?id=44643&r=fixedcvs
Fixed in release:
http://bugs.php.net/fix.php?id=44643&r=alreadyfixed
Need backtrace: http://bugs.php.net/fix.php?id=44643&r=needtrace
Need Reproduce Script: http://bugs.php.net/fix.php?id=44643&r=needscript
Try newer version: http://bugs.php.net/fix.php?id=44643&r=oldversion
Not developer issue: http://bugs.php.net/fix.php?id=44643&r=support
Expected behavior: http://bugs.php.net/fix.php?id=44643&r=notwrong
Not enough info:
http://bugs.php.net/fix.php?id=44643&r=notenoughinfo
Submitted twice:
http://bugs.php.net/fix.php?id=44643&r=submittedtwice
register_globals: http://bugs.php.net/fix.php?id=44643&r=globals
PHP 4 support discontinued: http://bugs.php.net/fix.php?id=44643&r=php4
Daylight Savings: http://bugs.php.net/fix.php?id=44643&r=dst
IIS Stability: http://bugs.php.net/fix.php?id=44643&r=isapi
Install GNU Sed: http://bugs.php.net/fix.php?id=44643&r=gnused
Floating point limitations: http://bugs.php.net/fix.php?id=44643&r=float
No Zend Extensions: http://bugs.php.net/fix.php?id=44643&r=nozend
MySQL Configuration Error: http://bugs.php.net/fix.php?id=44643&r=mysqlcfg
