php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #44707 The MySQL PDO driver resets variable content after bindParam on tinyint field
Submitted: 2008-04-12 01:30 UTC Modified: 2008-10-21 11:28 UTC
From: regli at yahoo dot com Assigned:
Status: Not a bug Package: PDO related
PHP Version: 5.2.5 OS: Windows Vista
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: regli at yahoo dot com
New email:
PHP Version: OS:

 

 [2008-04-12 01:30 UTC] regli at yahoo dot com
Description:
------------
In a prepare/bindParam scenario where the variable is already preset before the bindParam has been executed, a boolean field gets reset by the driver.  In the case of TRUE it resets it to "1" and in the case of FALSE it rests it to ''.  

Obviously this is highly undesirable as I get the input parameters in a function and then simply execute the prepare/bindParam.  However, as the database field is defined as tinyint, I naturally get a PDOException.  I tested the same code with IBM DB2 and this issue does not occur and it works perfectly (obviously the field definition is smallint).

If I reset the field after bindParam to FALSE and then again execute the statement, it works perfectly as it should.  The problem seems clearly to be in bindParam.

I tested this using the latest php_pdo_mysql.dll php-5.2.1(5_2) from 2008-01-11.



Reproduce code:
---------------
$stmt = db::getInstance()->prepare("INSERT INTO session (sessionName, privateSessionBool);
$stmt->bindParam(':sessionName', $sessionName);
$privateSessionBool) = false;
echo $privateSessionBool;
// here is where the problem occurs.
$stmt->bindParam(':privateSessionBool', $privateSessionBool);
echo $privateSessionBool;

Expected result:
----------------
The second bindParam statement should NOT change the field value of privateSessionBool to ''.  In fact bindParam should NEVER alter a fields value.  Note that it even changes TRUE booleans to '1' instead of leaving them alone.  I did not observe this behavior in other field types.

Actual result:
--------------
The first echo results in "false" whereas the second results in ''.

Any insert then results in:
SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'privateSessionBool' at row 1

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2008-07-03 15:08 UTC] uw@php.net
What a lovely variation of the old theme "The PDO SQL parser breaks your SQL". This is not a MySQL bug. This is not a driver bug. 

You have hit two PDO bugs!

However, you bug report is close to Bogus. Please pay more attention on your bug report. Well prepared bugs, including reproducible code examples, have a higher chance to be taken care of then bug reports like yours.

--- Bug 1 -- Type conversion

Your example is bogus. PDO does not promise not to change the variable type of a bound variable. PHP is dynamic and loosely typed. Its very "PHPish" from PDO not to take too much care about it.

However, if you care about types you should read the manually carefully and try all options on types which the API provides:

 bool PDOStatement::bindParam  ( mixed $parameter  , mixed &$variable  [, int $data_type  [, int $length  [, mixed $driver_options  ]]] )


You can give PDO a type hint when binding parameters. Make use of it:

$id = 1;
$mybool = false;
$stmt = $db->prepare('INSERT INTO test(id, mybool) VALUES (?, ?)');
$stmt->bindParam(1, $id);
$stmt->bindParam(2, $mybool, PDO::PARAM_BOOL);

If you do so, your variable will at least not change its type before you call execute(). If you omit this additional parameter your variable will be converted into a string by the call to bindParam().

However, at lastest the type of your variable $mybool will change from boolean to string due to a bug in ext/pdo/pdo_sql_parser.c upon execution of the statement. The file pdo_sql_parser.c comes from the PDO core module. It has nothing to do with MySQL.

The "bug" (PDO does not promise not to change the type of your bound parameters!) is around line 480. PDO recognizes that it got a boolean value and converts it into an PHP integer (long). It does this within a switch-case construct and falls through to the default handling. The default handling will convert the variable again from integer to string. 

You can check this yourself and do a quick hack of the parser using a "convert_back_to_boolean" flag.

And why do you try to bind a PHP boolean variable to an integer SQL column? Wouldn't it be more appropriate to use a PHP integer variable for a SQL integer column? PDO has not automatic PHP to SQL type mapping. 


--- Bug 2 --- PDOs SQL parser

PDOs SQL parser is very, very basic. It takes your INSERT statement and replaces all placeholders with string values!

For example, INSERT INTO test(id, mybool) VALUES (?, ?) will be converted by the INSERT INTO test(id, mybool) VALUES ('<somevalue>', '<somevalue>'). Of course MySQL will bail at you, if you send such a SQL statement! 

Search the bug archive for more examples of PDO generating invalid SQL.



--- What can you do? ---

- use ext/mysqli and be happy

 (nothing)
 (nothing)
 (nothing)

 - fix PDO
 - use MySQLs native prepared statements and keep your fingers off from named parameters and the PDO SQL parser

 [2008-07-03 19:55 UTC] uw@php.net
An *important* addition to 
--- Bug 2 --- PDOs SQL parser :

It is your task to teach the SQL parser that a value shall not be interpreted as a string. Make use of the PDO::PARAM_<type> constants! PDO can't know what you intend to do. PDO will always fall back to the safe default of strings and quote even integers unless you tell PDO not to do so!
 [2008-10-21 11:28 UTC] jani@php.net
What uwe said..
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Jul 16 19:01:28 2024 UTC