php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #69856 bindColumn() casts NULL to zero if PDO::PARAM_INT is used
Submitted: 2015-06-17 03:07 UTC Modified: 2016-10-10 22:36 UTC
Votes:1
Avg. Score:4.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:1 (100.0%)
From: abcaeffchen at gmail dot com Assigned: adambaratz (profile)
Status: Closed Package: PDO related
PHP Version: 5.6.10 OS: Windows
Private report: No CVE-ID: None
 [2015-06-17 03:07 UTC] abcaeffchen at gmail dot com
Description:
------------
PDO fetches fields to strings by default. If the field contains NULL in database the returned value will also be null.

But if using PDOStatement::bindColumn with PDO::PARAM_INT, all null values are casted to zero (as integer), so from the PHP side one cannot tell if the database field contains zero or null.

The same happens with PDO::PARAM_BOOL. Here null is casted to false.

There is a workaround to this: After turning off emulated prepared statements the returned values have the expected types. But this results in bug #69257.

Test script:
---------------
$db = new PDO('mysql:host=127.0.0.1;dbname=my_db;charset=UTF8',$user,$pass);

// Let field contain NULL in all (matching) rows
$stmt = $db->prepare('SELECT field FROM table WHERE other_field = ?');
$stmt->execute([1]);
$stmt->bindColumn(1,$field,PDO::PARAM_INT);
$stmt->fetch(PDO::FETCH_BOUND);

var_dump($field);    // 0 (integer)


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2015-06-26 00:12 UTC] abcaeffchen at gmail dot com
-Summary: bindColumn() casts null zero if PDO::PARAM_INT is used +Summary: bindColumn() casts NULL to zero if PDO::PARAM_INT is used
 [2015-06-26 00:12 UTC] abcaeffchen at gmail dot com
Corrected the summary
 [2016-06-30 11:11 UTC] cmb@php.net
-Type: Bug +Type: Feature/Change Request -Package: PDO MySQL +Package: PDO related
 [2016-06-30 11:11 UTC] cmb@php.net
In my opinion, this behavior is not a bug. You ask for an int, and
you get one; same as `(int) null === 0`.

Of course, the desired behavior (getting null or an int) would be
useful, but other constants should be introduced for this (maybe
PARAM_NULLABLE_INT or PARAM_NULL_OR_INT), if only for backward
compatibility. Anyhow, changing to feature request.
 [2016-07-10 14:51 UTC] abcaeffchen at gmail dot com
If you turn off emulated prepared statements, the behavior is as expected. So I still think that this is a bug in emulating the prepared statement.

At least it is inconsistent.
Also notice, that with PDO::PARAM_STR can NULL be fetched from the database.
 [2016-09-08 23:10 UTC] cmb@php.net
-Type: Feature/Change Request +Type: Bug
 [2016-09-08 23:10 UTC] cmb@php.net
Sorry, I've overlooked your latest comment until now. Considering

> Also notice, that with PDO::PARAM_STR can NULL be fetched from
> the database.

I'm switching back to "bug". At least both types should be handled
consistently.
 [2016-10-10 22:36 UTC] adambaratz@php.net
-Status: Open +Status: Closed -Assigned To: +Assigned To: adambaratz
 [2016-10-10 22:36 UTC] adambaratz@php.net
I had a duplicate ticket in for this issue (#73234). The fix I committed references the newer ticket.
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Thu Oct 28 19:05:47 2021 UTC