php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Request #66632 bindValue() and bindParam() fail silently for PARAM_BOOL datatype
Submitted: 2014-02-03 12:39 UTC Modified: 2020-12-11 15:06 UTC
Votes:17
Avg. Score:4.5 ± 1.3
Reproduced:14 of 16 (87.5%)
Same Version:4 (28.6%)
Same OS:12 (85.7%)
From: david at beroff dot com Assigned: nikic (profile)
Status: Closed Package: PDO MySQL
PHP Version: 5.4.24 OS: Linux
Private report: No CVE-ID: None
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: david at beroff dot com
New email:
PHP Version: OS:

 

 [2014-02-03 12:39 UTC] 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
)

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2014-02-25 12:22 UTC] uw@php.net
-Type: Bug +Type: Feature/Change Request
 [2014-02-25 12:22 UTC] 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-25 14:01 UTC] 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-11-02 17:52 UTC] 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
 [2015-12-15 19:09 UTC] cruxic at gmail dot com
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)
 [2020-12-11 15:06 UTC] nikic@php.net
-Status: Open +Status: Closed -Assigned To: +Assigned To: nikic
 [2020-12-11 15:06 UTC] nikic@php.net
Looks like this issue has been fixed by https://github.com/php/php-src/commit/7d1aa7534d756477d45f8fa63b5467589ccca031 quite a while ago.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Dec 05 15:01:31 2024 UTC