php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #44643 bound parameters ignore explicit type definitions
Submitted: 2008-04-05 00:19 UTC Modified: 2021-05-11 12:46 UTC
Votes:53
Avg. Score:4.7 ± 0.6
Reproduced:48 of 48 (100.0%)
Same Version:15 (31.2%)
Same OS:27 (56.2%)
From: ethan dot nelson at ltd dot org Assigned: cmb (profile)
Status: Closed Package: PDO ODBC
PHP Version: 7.4 OS: *
Private report: No CVE-ID: None
View Add Comment Developer Edit
 [2008-04-05 00:19 UTC] 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

Patches

Add a Patch

Pull Requests

Pull requests:

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2008-10-28 17:37 UTC] 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
 [2009-03-20 17:12 UTC] 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
 [2009-05-21 21:49 UTC] 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
 [2010-08-17 20:46 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.
 [2013-06-12 03:56 UTC] ssufficool@php.net
-Summary: bound parameters ignore explicit type definitions +Summary: PDO_ODBC: bound parameters ignore explicit type definitions
 [2014-01-01 12:52 UTC] felipe@php.net
-Package: PDO related +Package: PDO ODBC
 [2014-03-08 22:22 UTC] wschalle at gmail dot com 
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
 [2014-03-09 01:50 UTC] wschalle at gmail dot com 
As an addendum, I think pdo_sqlsrv has a very thorough implementation of type translation between PHP and SQL types and back. It is exhaustively complete, but doesn't perform quite as well as pdo_odbc's one-size-fits-all routine.

At least some of what pdo_sqlsrv is doing instead of calling SQLDescribeParam is an example of the right general way to solve the issues in pdo_odbc that occur when SQLDescribeParam is not supported or supported only partially.
 [2020-09-28 13:37 UTC] cmb@php.net 
Related To: Bug #48295
 [2021-05-11 12:46 UTC] cmb@php.net
-Summary: PDO_ODBC: bound parameters ignore explicit type definitions +Summary: bound parameters ignore explicit type definitions -Status: Open +Status: Verified -PHP Version: 5.2CVS-2009-03-20 +PHP Version: 7.4 -Assigned To: +Assigned To: cmb
 [2021-05-11 12:46 UTC] cmb@php.net 
While I cannot reproduce this with ODBC Driver 17 for SQL Server,
I consider it not unlikely that other still supported drivers have
that issue, and I agree that the fallback to either
SQL_LONGVARCHAR or SQL_LONGVARBINARY is very rough.
 [2021-05-11 12:51 UTC] cmb@php.net 
The following pull request has been associated:

Patch Name: Fix #44643: bound parameters ignore explicit type definitions
On GitHub:  https://github.com/php/php-src/pull/6973
Patch:      https://github.com/php/php-src/pull/6973.patch
 [2021-05-12 11:48 UTC] git@php.net 
Automatic comment on behalf of cmb69
Revision: https://github.com/php/php-src/commit/23a3bbb468adb611150a51fe82f8e5a22fbfae5c
Log: Fix #44643: bound parameters ignore explicit type definitions
 [2021-05-12 11:48 UTC] git@php.net
-Status: Verified +Status: Closed
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved. 		Last updated: Fri Oct 22 07:03:33 2021 UTC