php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #49255 PDO fails to insert boolean FALSE to MySQL in prepared statement
Submitted: 2009-08-14 15:53 UTC Modified: 2009-08-21 14:05 UTC
Votes:1
Avg. Score:3.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:0 (0.0%)
From: carysmith at creighton dot edu Assigned:
Status: Not a bug Package: PDO related
PHP Version: 5.2.10 OS: Windows 2003 Ent.
Private report: No CVE-ID:
 [2009-08-14 15:53 UTC] carysmith at creighton dot edu
Description:
------------
Before I report the bug, would just like to say that PDO is excellent and I am enjoying working with it.

The Issue:
When attempting to insert a Boolean FALSE into a MySQL Boolean column, the insert fails. Inserting a 0 does not. 

The following example code is borrowed from another post which was similar http://bugs.php.net/bug.php?id=38386 and modified to accommodate how I am using it.

Reproduce code:
---------------
<?php
$dbh = new PDO( 'mysql:host=localhost; dbname=Testing', 'DevUser', 'P@ssw0rd!');

// Create table
$query = 'CREATE TABLE Testing.test_boolean(TheBoolean BOOLEAN);';
$createStatement = $dbh->prepare( $query);
$createStatement->execute();

// Attempt to insert Boolean records into table - (FALSE insert fails without error)
$query = 'insert into Testing.test_boolean set TheBoolean = ?;';
$statement = $dbh->prepare( $query);
$statement->execute(array(true));
$statement->execute(array(false));

// However by replacing Boolean with integers does work
$statement->execute(array(1));
$statement->execute(array(0));
?>

Expected result:
----------------
4 rows added to the table:

1
0
1
0



Actual result:
--------------
3 rows added to the table:

1
1
0

Investigation of the MySQL logs shows the Boolean FALSE is inserted as an empty string '' which fails. Please verify if I am missing something or this cannot be reproduced. In the meantime I am changing the code to use numbers instead of Boolean.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2009-08-21 11:52 UTC] sjoerd-php at linuxonly dot nl
Thank you for your report.

When PDO replaces question marks in execute(), it does not take the type of the columns into account. It thus does not know that the parameter to execute() should be interpreted as a boolean, it simply replaces question marks by strings. Because the string representation of false is the empty string, it replaces the question mark by ''.

It makes no sense to change the string representation of false to 0, because that would give problems on other places in PHP. It makes no sense to parse the query to determine the type, as this is very hard to do.

To work around this problem, you should convert the boolean value yourself. You can do so like this:
$numeric_boolean = (int)$php_boolean;
 [2009-08-21 11:54 UTC] sjoerd@php.net
Marking as bogus, because prepared statements are supposed to work like this. Feel free to contact me if you disagree.
 [2009-08-21 14:05 UTC] carysmith at creighton dot edu
"It makes no sense to change the string representation of false to 0,
because that would give problems on other places in PHP. It makes no
sense to parse the query to determine the type, as this is very hard to
do."

This is not at all a request to make the string representation of false to 0. I definitely understand not wanting to change the way things are parsed into the system but the concept of requiring a 'work-around' to pass an extremely simple type to a database seems a shame especially considering a true value works as expected, but these are the cards we are dealt. Loose typing can be a great boon, but also a curse.


Thank you for responding.
 
PHP Copyright © 2001-2015 The PHP Group
All rights reserved.
Last updated: Mon May 25 11:02:11 2015 UTC