php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #53132 PDO converts (int)0 to NULL
Submitted: 2010-10-21 19:44 UTC Modified: 2014-01-01 12:47 UTC
Votes:4
Avg. Score:4.5 ± 0.9
Reproduced:3 of 3 (100.0%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: novitools dot novi at web dot de Assigned:
Status: Open Package: PDO MySQL
PHP Version: 5.3.1 OS: Linux
Private report: No CVE-ID:
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: novitools dot novi at web dot de
New email:
PHP Version: OS:

 

 [2010-10-21 19:44 UTC] novitools dot novi at web dot de
Description:
------------
PDO Driver for MySQL, client library version 5.0.84

When using the bindColumn method of the pdo statement object, PDO converts the value Null to the number 0, if you set the type to PDO::PARAM_INT. Nevertheless PDO did'n convert a Null Value, when you select PDO::PARAM_STR as type.

So PDO::PARAM_STR can handle Null-Values, but PDO::PARAM_INT can't.

Test script:
---------------
<?php
$DB_HOST = 'localhost';
$DB_NAME = '***';
$DB_PASSWORD = '***;
$DB_USERNAME = '***;

$Dsn = 'mysql:host='.$DB_HOST.';dbname='.$DB_NAME;
$Database = new PDO($Dsn, $DB_USERNAME, $DB_PASSWORD);
$Database->setAttribute(PDO::ATTR_ERRMODE           , PDO::ERRMODE_EXCEPTION);
$Database->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_BOUND);

$Sql = <<<SQL
    SELECT '', Null, 0, Null
SQL;
  
$Statement = $Database->prepare($Sql);
$Statement->bindColumn(1, $Test1, PDO::PARAM_STR); // string(0) ""
$Statement->bindColumn(2, $Test2, PDO::PARAM_STR); // NULL
$Statement->bindColumn(3, $Test3, PDO::PARAM_INT); // int(0)
$Statement->bindColumn(4, $Test4, PDO::PARAM_INT); // NULL
$Statement->execute();
$Statement->fetch();
var_dump($Test1);
var_dump($Test2);
var_dump($Test3);
var_dump($Test4);
?>

Expected result:
----------------
string(0) "" NULL int(0) NULL

Actual result:
--------------
string(0) "" NULL int(0) int(0)

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2010-10-21 23:22 UTC] felipe@php.net
-Status: Open +Status: Assigned -Assigned To: +Assigned To: mysql
 [2010-11-08 18:16 UTC] uw@php.net
-Status: Assigned +Status: Feedback
 [2010-11-08 18:16 UTC] uw@php.net
5.2 only gets critical security updates.

"This release marks the end of the active support for PHP 5.2. Following this release the PHP 5.2 series will receive no further active bug maintenance. Security fixes for PHP 5.2 might be published on a case by cases basis. All users of PHP 5.2 are encouraged to upgrade to PHP 5.3.", http://www.php.net/archive/2010.php#id2010-07-22-1


Please try with 5.3
 [2010-11-08 20:45 UTC] novitools dot novi at web dot de
-Status: Feedback +Status: Assigned
 [2010-11-08 20:45 UTC] novitools dot novi at web dot de
Same Problem with 5.3.1.
 [2010-11-22 10:41 UTC] uw@php.net
-Status: Assigned +Status: Feedback -PHP Version: 5.2.14 +PHP Version: 5.3.1
 [2010-11-22 10:41 UTC] uw@php.net
What about PHP 5.3.4-RC. Is it specific to mysqlnd or libmysql?
 [2011-01-04 16:22 UTC] uw@php.net
-Status: Feedback +Status: Open -Assigned To: mysql +Assigned To:
 [2011-01-04 16:22 UTC] uw@php.net
I don't think this is tied to MySQL, thus unassigning mysql. 

The test script is using prepared statement emulation. It does first a bound fetch and then it does another associative/indexed fetch.  That's because the test script does not read fetch(PDO::FETCH_BOUND), see also PDO documebntation on PDOStatement::fetch(). The result of the associative/indexed fetch is ignored, however, for the sake of the NULL/0 discussion that's irrelevant. It is only an aside note API usage.

Because the prepared statement emulation is not turned off explicitly PDO will emulate the query using non-prepared statements. MySQL will hint the column type string to PDO for non-prepared statements and, in case of the SQL value NULL, the PDO MySQL driver will pass a char* with the C value of NULL to the PDO core. IMHO the driver job ends at this point. The driver, in this case MySQL, has passed NULL to the PDO core.

At this point some magic in the PDO core converts the desired value of NULL. The magic is user requested and part of bindColumn(). After fetching the char* NULL value from the MySQL driver and converting it to a zval containing NULL the PDO core follows the instructions of the user and converts NULL into a string (pdo_stmt.c, line 640ff, switch (new_type) ...). 

This is basically what the PDO core does because of the conversion requested in bindColumn():

var_dump((int)NULL);
-> int(0)

I think the PDO core should preserve NULL values for all user-requested conversions. The PDO core should handle NULL in that switch. A PDO driver that has returned a NULL value should not have to worry about any conversion logic happening in the core.

Because I believe this is a PDO core bug and the PDO design should be altered, I have unassigned the bug from group mysql.
 [2011-01-04 18:29 UTC] novitools dot novi at web dot de
Sorry I forgot to set $Pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_BOUND); in the test case. In my projects the attribute is set, so I don't need to call fetch(PDO::FETCH_BOUND) instead of fetch().

Purely out of interest, why does the test script emulates prepared statements?

I thought it really uses prepared statements like mysqli?
 [2014-01-01 12:47 UTC] felipe@php.net
-Package: PDO related +Package: PDO MySQL
 
PHP Copyright © 2001-2017 The PHP Group
All rights reserved.
Last updated: Tue Aug 29 15:01:52 2017 UTC