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

go to bug id or search bugs for

Bug #81367 bug ? PDO::bindValue data type inconsistent with expected results
Submitted: 2021-08-17 11:40 UTC Modified: 2021-09-08 10:18 UTC
From: cevincheung at gmail dot com Assigned:
Status: Not a bug Package: PDO MySQL
PHP Version: 8.0.9 OS: Ubuntu 20.04.2
Private report: No CVE-ID: None
View Add Comment Developer Edit
 [2021-08-17 11:40 UTC] cevincheung at gmail dot com 
Description:
------------
$db = new PDO(...);
$stmt = $db->prepare("select :id col");
$stmt->bindValue(':id', '1' /* string */, PDO::PARAM_STR /* string */);
$stmt->execute();

var_dump($stmt->fetch(PDO::FETCH_ASSOC)); // got: string(1) "1"   ok, right ~

but:
$stmt->bindValue(':id', '1' /* string */, PDO::PARAM_INT /* int */);
$stmt->execute();

var_dump($stmt->fetch(PDO::FETCH_ASSOC)); // got: string(1) "1"  why ? 


why ?  What exactly is the third parameter of bindValue used for ?

Wireshark report (for bindValue(':id', '1', PDO::PARAM_INT)):
Parameter:
   Type: FILED_TYPE_VAR_STRING (253)
   Unsigned: 0
   Value: 1


Test script:
---------------
$db = new PDO(...);
$stmt = $db->prepare("select :id col");
$stmt->bindValue(':id', '1' /* string */, PDO::PARAM_INT /* int */);
$stmt->execute();

var_dump($stmt->fetch(PDO::FETCH_ASSOC)); // got: string(1) "1"

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2021-08-17 11:49 UTC] cevincheung at gmail dot com 
bindValue(':id', int, PDO::PARAM_INT)
got:  int(1)
 [2021-09-08 10:15 UTC] dharman@php.net 
> What exactly is the third parameter of bindValue used for?

That is a good question. Most of the time all parameters can be bound as string. Databases like MySQL or SQLite (and many others) should make no fuss if they receive an integer as a string. This means 99% of the time, you don't need to use bindValue/bindParam. However, in some very rare cases, the type of value provided makes a difference. Unless you run into such a situation (and you will know as SQL developer when you need it) then you can forget about specifying this type hint. 

The third parameter is mostly just that, a type hint. So when you provide an integer you can tell PDO that it is an integer and PDO will not perform a cast to a string. There are some edge cases when specifying the type hint WILL cause a type cast, e.g. passing bool and marking it as an int and vice versa. In all other cases, PDO will try to cast the value to a string. 

This is a rather niche functionality and not documented in detail. There is however very detailed documentation for PDO that is not maintained by PHP group: https://phpdelusions.net/pdo#methods The author of that guide does a really good job at describing all functionalities of PDO.
 [2021-09-08 10:18 UTC] dharman@php.net
-Status: Open +Status: Not a bug
 [2021-09-08 10:18 UTC] dharman@php.net 
Since I see no actionable point here I am closing this as not a bug. This works how it was designed IMHO. The fact that this is not a very intuitive design is not a bug. There are bugs related to this functionality though, but this report doesn't mention these bugs.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Tue Apr 16 22:01:27 2024 UTC