php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #53132 PDO converts (int)0 to NULL
Submitted: 2010-10-21 19:44 UTC Modified: 2021-04-08 19:19 UTC
Votes:8
Avg. Score:4.8 ± 0.7
Reproduced:7 of 7 (100.0%)
Same Version:2 (28.6%)
Same OS:3 (42.9%)
From: novitools dot novi at web dot de Assigned: nikic (profile)
Status: Closed Package: PDO MySQL
PHP Version: 5.3.1 OS: Linux
Private report: No CVE-ID: None
View Developer Edit
 [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

Pull Requests

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
 [2020-08-28 15:20 UTC] cmb@php.net 
> At this point some magic in the PDO core converts the desired
> value of NULL.

Well, if NULL was desired, why has PDO::PARAM_INT been given instead
of PDO::PARAM_NULL?  Yes, rhethoric question.

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

Because that is default for PDO MySQL.
 [2021-04-08 19:19 UTC] dharman@php.net
-Status: Open +Status: Closed -Assigned To: +Assigned To: nikic
 [2021-04-08 19:19 UTC] dharman@php.net 
The fix for this bug has been committed.
If you are still experiencing this bug, try to check out latest source from https://github.com/php/php-src and re-test.
Thank you for the report, and for helping us make PHP better.

Fixed by Nikita in PHP 8.1 back in December. See https://github.com/php/php-src/commit/caa710037e663fd78f67533b29611183090068b2
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Sat Dec 21 16:01:28 2024 UTC