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
 [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: Sat Dec 21 14:01:32 2024 UTC