Req #66632 [Com]: bindValue() and bindParam() fail silently for PARAM_BOOL datatype
| From: | cruxic at gmail dot com | Date: | Tue, 15 Dec 2015 19:09:25 +0000 |
| Subject: | Req #66632 [Com]: bindValue() and bindParam() fail silently for PARAM_BOOL datatype | ||
| References: | 1 | Groups: | php.bugs |
| Request: | Send a blank email to php-bugs+get-197913@lists.php.net to get a copy of this message | ||
Edit report at https://bugs.php.net/bug.php?id=66632&edit=1
ID: 66632
Comment by: cruxic at gmail dot com
Reported by: david at beroff dot com
Summary: bindValue() and bindParam() fail silently for
PARAM_BOOL datatype
Status: Open
Type: Feature/Change Request
Package: PDO MySQL
Operating System: Linux
PHP Version: 5.4.24
Block user comment: N
Private report: N
New Comment:
I'm surprised at the comment above:
"You filed the bug against PDO_MySQL. From PDO_MySQL perspective it is a feature request. If it
was filed against PDO, I'd call it a bug."
The goal of a bug reporting system is to get things fixed. If it was filed against the wrong
package then it should be reassigned, not demoted to a "feature request".
This bug was filed against 5.4 but I verified today that it still exists in 7.0. It is probably the
same as #38386 (https://bugs.php.net/bug.php?id=38386)
Previous Comments:
------------------------------------------------------------------------
[2014-11-02 17:52:46] jon dot dufresne at gmail dot com
I recently ran into this same issue and was very surprised by the unexpected, broken behavior of
PDO::PARAM_BOOL. I bumped into this issue after disabling PDO::ATTR_EMULATE_PREPARES and noticed
many of unit tests were suddenly failing. So it is even more surprising that this works when
emulating prepares, but fails when using real prepared statements. I have since changed all
PDO::PARAM_BOOL to PDO::PARAM_INT in my code as a workaround. Below is the test case I used to
demonstrate to myself the broken behavior.
Test script:
---
<?php
$dns = "mysql:host=localhost;dbname=testdb";
$user = 'user';
$pass = 'pass';
$options = array(
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
);
$db = new PDO($dns, $user, $pass, $options);
$db->query("DROP TABLE IF EXISTS booltest");
$db->query("CREATE TABLE booltest (val BOOLEAN)");
$db->query("INSERT INTO booltest (val) VALUES (FALSE)");
$stmt = $db->prepare("SELECT COUNT(*) FROM booltest WHERE val = ?");
$stmt->bindValue(1, false, PDO::PARAM_BOOL);
$stmt->execute();
$count = $stmt->fetchColumn();
echo sprintf("count: %d\n", $count);
$stmt = $db->prepare("SELECT COUNT(*) FROM booltest WHERE val = ?");
$stmt->bindValue(1, false, PDO::PARAM_INT);
$stmt->execute();
$count = $stmt->fetchColumn();
echo sprintf("count: %d\n", $count);
---
Expected
---
count: 1
count: 1
---
Actual
---
count: 0
count: 1
---
All tests are on Fedora 20.
$ php -v
PHP 5.5.18 (cli) (built: Oct 16 2014 13:13:55)
Copyright (c) 1997-2014 The PHP Group
Zend Engine v2.5.0, Copyright (c) 1998-2014 Zend Technologies
with Xdebug v2.2.5, Copyright (c) 2002-2014, by Derick Rethans
$ mysql --version
mysql Ver 14.14 Distrib 5.5.38, for Linux (x86_64) using readline 5.1
------------------------------------------------------------------------
[2014-02-25 14:01:21] david at beroff dot com
> PARAM_INT works just fine for me...
Right; that's why I showed that as a proposed workaround.
> You filed the bug against PDO_MySQL.
I am but a lowly PHP user; I knowest not its internal details. :-)
I think I've filed two earlier documentation reports, and this is
my first report on the actual working code itself.
When presented with a choice, I just picked the package that seemed
to make the most sense to me. Clearly it got to the right party so
as to understand the issues involved. :-)
> From PDO_MySQL perspective it is a feature request.
I can understand your perspective. But maybe there should be some
corresponding documentation, perhaps in
http://www.php.net/manual/en/pdostatement.bindvalue.php
and
http://www.php.net/manual/en/pdostatement.bindparam.php
, along the
lines of "not all data types are supported by all drivers; see this
page for details". I just took a second look, and couldn't find
anything that would suggest that this is a missing feature.
> The core, however, ignores the failure and returns true for a
> failed bind*() call....
> If it was filed against PDO, I'd call it a bug.
So perhaps this report should be "split" into two components?
I don't want to file a duplicate post, but it sounds like there
are two separate teams that need to address this. Thanks!
------------------------------------------------------------------------
[2014-02-25 12:22:19] uw@php.net
PARAM_INT works just fine for me with mysqlnd. Use INT...
The MySQL PDO driver code does not cover PARAM_BOOL. Hence, the driver signals a failure to the
calling PDO core for PDO_PARAM_EVT_EXEC_PRE. The core, however, ignores the failure and returns true
for a failed bind*() call.
You filed the bug against PDO_MySQL. From PDO_MySQL perspective it is a feature request. If it was
filed against PDO, I'd call it a bug.
------------------------------------------------------------------------
[2014-02-03 12:39:12] david at beroff dot com
Description:
------------
bindValue( ... PDO::PARAM_BOOL ) and bindParam( ... PDO::PARAM_BOOL )
appear to cause the subsequent execute() to not do anything,
as well as to not report that the operation failed.
Reported earlier, (2006), but suspended due to no feedback:
https://bugs.php.net/bug.php?id=38386
https://bugs.php.net/bug.php?id=38546
Apparently may have already been fixed for Postgres:
https://bugs.php.net/bug.php?id=62593
May also be related to:
https://bugs.php.net/bug.php?id=41365
https://bugs.php.net/bug.php?id=57489
Software versions:
Linux Version 2.6.32.39-grsec-3.mosso5.1.x86_64
PHP Version 5.3.20 (external host; unable to upgrade)
PHP MySQL library version 5.0.77
MySQL server version 5.1.70
Test script:
---------------
<html><body><pre><?php
try {
$db = new PDO( "mysql:host=HOSTNAME;dbname=DATABASE", "USERNAME",
"PASSWORD",
array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ) );
$db->exec( "DROP TABLE IF EXISTS TestPDO;" );
$db->exec( "CREATE TABLE TestPDO (ColT BOOLEAN, ColF BOOLEAN) ENGINE = InnoDB;" );
$stmt = $db->prepare( "INSERT INTO TestPDO (ColT, ColF) VALUES (:ColT, :ColF);" );
$stmt->bindValue( ":ColT", TRUE, PDO::PARAM_BOOL );
$stmt->bindValue( ":ColF", FALSE, PDO::PARAM_BOOL );
$stmt->execute();
// Suggested workaround (from bug #38546 [2007-04-11 15:26 UTC]) doesn't work:
$colT = TRUE;
$colF = FALSE;
$stmt->bindParam( ":ColT", $colT, PDO::PARAM_BOOL );
$stmt->bindParam( ":ColF", $colF, PDO::PARAM_BOOL );
$stmt->execute();
// New proposed workaround:
$stmt->bindValue( ":ColT", $colT ? 1 : 0, PDO::PARAM_INT );
$stmt->bindValue( ":ColF", $colF ? 1 : 0, PDO::PARAM_INT );
$stmt->execute();
foreach ($db->query( "SELECT * FROM TestPDO" ) as $row)
print_r($row);
$db = NULL;
} catch (PDOException $e) {
echo $e->getMessage();
}
?></pre></body></html>
Expected result:
----------------
Array
(
[ColT] => 1
[ColF] => 0
)
Array
(
[ColT] => 1
[ColF] => 0
)
Array
(
[ColT] => 1
[ColF] => 0
)
Actual result:
--------------
Array
(
[ColT] => 1
[ColF] => 0
)
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=66632&edit=1