Edit report at https://bugs.php.net/bug.php?id=44643&edit=1
ID: 44643
Comment by: wschalle at gmail dot com
Reported by: ethan dot nelson at ltd dot org
Summary: PDO_ODBC: bound parameters ignore explicit type
definitions
Status: Open
Type: Bug
Package: PDO ODBC
Operating System: *
PHP Version: 5.2CVS-2009-03-20
Block user comment: N
Private report: N
New Comment:
This isn't due to a SQL server bug but instead it is due to well-documented laziness on the sql
server developers part when writing the SQLDescribeParam function in the SQL Server ODBC driver.
Basically when parameters are within a subquery, SQLDescribeParam doesn't return data for them.
The fact that this breaks queries is actually pdo_odbc's fault though, for relying entirely on
the output (or lack thereof) of SQLDescribeParam to dictate how it then passes the PHP variable
through to the SQLBindParam function.
This issue doesn't just exist with the SQL Server driver either, there are several DBMS's
with issues with SQLDescribeParam, but sql server's behavior is the worst.
PDO's handling of a bad return from SQLDescribeParam is not quite where it should be - right
now if SQLDescribeParam doesn't work, the driver makes the strange assumption that the
parameter should be sent as either SQL_LONGVARBINARY in the case of a LOB or SQL_LONGVARCHAR in the
case of a non-LOB type.
The correct behavior would be at the very least to use the specified param_type and/or the PHP type
to make a better fallback guess. Bound parameters specified as PDO_PARAM_INT should absolutely be
cast to long and sent to the server as SQLINTEGER.
I'd really like to see this bug fixed, as it is one of the only things keeping PHP running on
linux from accessing SQL server properly. My app supports a variety of DBMS's, but if a company
has data in SQL Server and we need to access it, the app either has to run on a windows server (god
why), use non-parameterized queries, replace all parameter placeholders with CAST(CAST(?,
varchar),int), use parameterized queries without any subqueries, or use direct exec queries. Not the
best of situations.
PERL has had the same issues - see:
https://rt.cpan.org/Public/Bug/Display.html?id=64968
https://rt.cpan.org/Public/Bug/Display.html?id=50852
http://www.martin-evans.me.uk/node/50
Previous Comments:
------------------------------------------------------------------------
[2010-08-17 20:46:11] 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.
------------------------------------------------------------------------
[2009-05-21 21:49:40] ethan dot nelson at ltd dot org
This also happens for the new Native Client 10.0 driver for SQL 2008.
SELECT * FROM (SELECT 'test' = :id) a WHERE a.test = :id2
Array
(
[0] => 42000
[1] => 402
[2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]The
data types text and text are incompatible in the equal to operator.
(SQLExecute[402] at ext\pdo_odbc\odbc_stmt.c:133)
[3] => 42000
)
PHP Version 5.2.9-2
System Windows NT LTD-PWWW2 5.2 build 3790
Build Date Apr 9 2009 08:22:37
Configure Command cscript /nologo configure.js "--enable-snapshot-
build" "--enable-debug-pack" "--with-snapshot-template=d:\php-
sdk\snap_5_2\vc6\x86\template" "--with-php-build=d:\php-
sdk\snap_5_2\vc6\x86\php_build" "--with-pdo-oci=D:\php-
sdk\oracle\instantclient10\sdk,shared" "--with-oci8=D:\php-
sdk\oracle\instantclient10\sdk,shared"
Server API ISAPI
------------------------------------------------------------------------
[2009-03-20 17:12:18] phpbugs at matthewboehm dot com
RHEL-5.3 php5.2-200903201530
Invalid character value for cast specification: 206 [FreeTDS][SQL
Server]Operand type clash: text is incompatible with int
(SQLExecute[206] at /usr/src/php5.2-
200903201530/ext/pdo_odbc/odbc_stmt.c:133
------------------------------------------------------------------------
[2008-10-28 17:37:00] ethan dot nelson at ltd dot org
<?php
ini_set("display_errors","yes");
$poo = new PDO("odbc:DEVELOPMENT");
$query = "SELECT * FROM (SELECT 'test' = :id) a WHERE a.test = :id2";
$stm = $poo->prepare($query);
echo $query."<br>\n";
$id = 1;
$stm->bindParam(':id',$id,PDO::PARAM_INT);
$id2 = 1;
$stm->bindParam(':id2',$id2,PDO::PARAM_INT);
echo "<pre>\n";
if ($stm->execute()) print_r($stm->fetchAll(PDO::FETCH_ASSOC));
else print_r( $stm->errorInfo());
echo "</pre>\n";
phpinfo();
?>
-----------The above returns the below result:
SELECT * FROM (SELECT 'test' = :id) a WHERE a.test = :id2
Array
(
[0] => 42000
[1] => 402
[2] => [Microsoft][SQL Native Client][SQL Server]The data types
text and text are incompatible in the equal to operator.
(SQLExecute[402] at ext\pdo_odbc\odbc_stmt.c:133)
[3] => 42000
)
PHP Version 5.2.7RC2-dev
------------------------------------------------------------------------
[2008-04-05 00:19:04] ethan dot nelson at ltd dot org
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 this bug report at https://bugs.php.net/bug.php?id=44643&edit=1