php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #36561 PDO_ODBC/MSSQL does not work with bound params in subquery
Submitted: 2006-02-28 17:10 UTC Modified: 2009-04-25 14:37 UTC
Votes:14
Avg. Score:4.5 ± 0.6
Reproduced:14 of 14 (100.0%)
Same Version:6 (42.9%)
Same OS:7 (50.0%)
From: travis at raybold dot com Assigned: fmk
Status: Not a bug Package: PDO related
PHP Version: 5.2.4 OS: Windows XP
Private report: No CVE-ID:
 [2006-02-28 17:10 UTC] travis at raybold dot com
Description:
------------
Connecting to a MSSQL database through PDO_ODBC, I prepare a statement with a bound parameter inside of a subquery, and get a 42000 Syntax Error when I execute it. 

The error indicates that it is trying to compare text. ntext or image data, but the table consists solely of one integer field. Moving the bound parameter outside of the subquery makes it work.

I am using PHP 5.1.2, MSSQL 2000, Windows XP, IIS6. 
Configure Line: cscript /nologo configure.js "--enable-snapshot-build" "--with-gd=shared"



Reproduce code:
---------------
MSSQL:
CREATE TABLE zTest_TBL (
	TestID int NULL 
) 
INSERT INTO zTest_TBL (TestID) Values (1)

PHP:
<?
$iTestID=1;
$oConnection = new PDO($sDSN, $GLOBALS["sDatabase_Username"], $GLOBALS["sDatabase_Password"]);
$oStatement = $oConnection->prepare('SELECT TestID FROM zTest_TBL WHERE TestID IN (SELECT TestID FROM zTest_TBL WHERE TestID = :TestID)');
//$oStatement = $oConnection->prepare('SELECT TestID FROM zTest_TBL WHERE TestID = :TestID AND TestID  IN (SELECT TestID FROM zTest_TBL )');
$oStatement->bindParam(':TestID', $iTestID,PDO::PARAM_INT );
$oStatement->execute() or print_r($oStatement->errorInfo());
foreach($oStatement as $aRow) {
  print_r($aRow);
}
?>

Expected result:
----------------
Array ( [TestID] => 1 [0] => 1 ) 


Actual result:
--------------
Array ( [0] => 42000 [1] => 306 [2] => [Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. (SQLExecute[306] at ext\pdo_odbc\odbc_stmt.c:133) [3] => 42000 )

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-04-09 07:57 UTC] wez@php.net
Try using:
$oStatement->bindParam(':TestID', $iTestID);
instead of:
$oStatement->bindParam(':TestID', $iTestID,PDO::PARAM_INT );
The latter is implicitly binding for output, which might be part of your problem.
 [2007-05-24 20:50 UTC] travis at raybold dot com
Hey Wez, I never saw the feedback til I stumbled on it today, and clearly have been able to work around this, but it does keep stopping me.

It happens exactly the same if I omit the PDO::PARAM_INT as the final parameter.

I am still using the branched version you gave me... I'd be happy to test with the latest if you think it might be fixed there.

--Travis
 [2007-06-22 17:50 UTC] blohr at triad dot rr dot com
This bug affects my app, too. I'm using PHP 5.2.3 on Windows XP Pro SP2, under both IIS 5.1 and Apache 2.2, with SQL Server 2005 Express.

I don't know if it'll help or not, but here's some more reproduce code. Just fix the PDO DSN string to something valid.

<?php
$db = new PDO("odbc:dsn=$odbcDsn;uid=$user;pwd=$pass");

$createTable = 
    'CREATE TABLE ##test (
        intCol int,
        textCol varchar(20)
    )';
$createTable2 =
    'CREATE TABLE ##test2 (
        intCol2 int,
        textCol2 varchar(20)
    )';
$db->exec($createTable);
$db->exec($createTable2);

$ins = $db->prepare('INSERT INTO ##test (intCol, textCol) VALUES (:i, :t)');
$ins->execute(array('i'=>1, 't'=>'A String'));
$ins2 = $db->prepare('INSERT INTO ##test2 (intCol2, textCol2) VALUES (:i, :t)');
$ins2->execute(array('i'=>1, 't'=>'Another String'));

$sel = $db->prepare('SELECT * FROM ##test WHERE intCol = (SELECT intCol2 FROM ##test2 WHERE textCol2 = :t)');
$sel->execute(array('t'=>'Another String')) or var_dump($sel->errorInfo());

$sel = $db->prepare('SELECT * FROM ##test WHERE intCol = (SELECT intCol2 FROM ##test2 WHERE intCol2 = :i)');
$sel->execute(array('i'=>1)) or var_dump($sel->errorInfo());

$sel = $db->prepare('SELECT * FROM ##test WHERE intCol = (SELECT intCol2 FROM ##test2 WHERE textCol2 = :t)');
$sel->bindValue('t', 'Another String');
$sel->execute() or var_dump($sel->errorInfo());

$sel = $db->prepare('SELECT * FROM ##test WHERE intCol = (SELECT intCol2 FROM ##test2 WHERE intCol2 = :i)');
$sel->bindValue('i', 1);
$sel->execute() or var_dump($sel->errorInfo());

$sel = $db->prepare('SELECT * FROM ##test WHERE intCol = (SELECT intCol2 FROM ##test2 WHERE textCol2 = :t)');
$sel->bindValue('t', 'Another String', PDO::PARAM_STR);
$sel->execute() or var_dump($sel->errorInfo());

$sel = $db->prepare('SELECT * FROM ##test WHERE intCol = (SELECT intCol2 FROM ##test2 WHERE intCol2 = :i)');
$sel->bindValue('i', 1, PDO::PARAM_INT);
$sel->execute() or var_dump($sel->errorInfo());

$sel = $db->prepare('SELECT * FROM ##test WHERE intCol = (SELECT intCol2 FROM ##test2 WHERE textCol2 = :t)');
$t = 'Another String';
$sel->bindParam('t', $t, PDO::PARAM_STR);
$sel->execute() or var_dump($sel->errorInfo());

$sel = $db->prepare('SELECT * FROM ##test WHERE intCol = (SELECT intCol2 FROM ##test2 WHERE intCol2 = :i)');
$i = 1;
$sel->bindParam('i', $i, PDO::PARAM_INT);
$sel->execute() or var_dump($sel->errorInfo());
?>
 [2007-08-30 16:20 UTC] travis at raybold dot com
the problem still occurs on:
PHP 5.2.4 (cli) (built: Aug 30 2007 07:06:31)
 [2007-08-31 07:33 UTC] jani@php.net
Very nice that you didn't bother trying with the RCs..
 [2007-08-31 07:35 UTC] jani@php.net
Assigned to the maintainer.
 [2008-01-06 23:02 UTC] emil at troxy dot net
I was able to reproduce this error using the 2008-01-05 snapshot of PDO_ODBC.

Reproduce code:
---------------
<?php
$db = new PDO('odbc:Driver={SQL Server}; Server=localhost; Uid=test; Pwd=test; Database=test;');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->exec('CREATE TABLE #foo (id INT NOT NULL)');
$db->exec('INSERT INTO #foo VALUES(1)');
$stmt = $db->prepare('SELECT id FROM #foo WHERE id IN (SELECT id FROM #foo WHERE id = ?)');
$stmt->bindValue(1, 1, PDO::PARAM_INT);
$stmt->execute();
var_dump($stmt->fetch(PDO::FETCH_ASSOC));
?>

Expected result:
----------------
array(1) { ["id"]=>  string(1) "1" }

Actual result:
--------------
PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 306 [Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. (SQLExecute[306] at ..\pecl_5_2\pdo_odbc\odbc_stmt.c:133)'

A trace using the Profiler tool shows that the bound integer value is incorrectly interpreted as text:

CREATE TABLE #foo (id INT NOT NULL)
go
INSERT INTO #foo VALUES(1)
go
declare @P1 int
set @P1=NULL
exec sp_prepare @P1 output, N'@P1 text', N'SELECT id FROM #foo WHERE id IN (SELECT id FROM #foo WHERE id = @P1)', 1
select @P1
go

Incorrect: N'@P1 text'
It should be: N'@P1 int'
 [2008-03-03 20:50 UTC] auroraeosrose@php.net
This appears to be a bug with prepared statements in the underlying microsoft client driver implementation...

Even the microsoft SQL Server 2005 Driver for php suffers from this bug

<?php
$iTestID=1;
$connection = sqlsrv_connect($dsn);
$statement = sqlsrv_prepare($connection,
	'SELECT TestID FROM zTest_TBL WHERE TestID IN (SELECT TestID FROM zTest_TBL WHERE TestID = ?)',
	array($iTestID));
$value = sqlsrv_execute($statement);
print_r(sqlsrv_errors());
?>

so it appears the issue is upstream and we have to wait for microsoft..
 [2009-04-25 14:37 UTC] jani@php.net
Not PHP bug -> bogus.
 [2010-08-17 20:47 UTC] kraven at kraven dot org
This is caused by a long standing MS SQL ODBC Client bug.  http://connect.microsoft.com/SQLServer/feedback/details/521409/odbc-client-mssql-does-not-work-with-bound-parameters-in-subquery

Last update on 8/3/2010 was that it would be resolved in a future release of the SQL Server Native Access Client.
 
PHP Copyright © 2001-2014 The PHP Group
All rights reserved.
Last updated: Thu Apr 17 01:01:56 2014 UTC