Bug #81367 [Com]: bug ? PDO::bindValue data type inconsistent with expected results

Edit report at https://bugs.php.net/bug.php?id=81367&edit=1 ID: 81367 Comment by: dharman@php.net Reported by: cevincheung at gmail dot com Summary: bug ? PDO::bindValue data type inconsistent with expected results Status: Open Type: Bug Package: PDO MySQL Operating System: Ubuntu 20.04.2 PHP Version: 8.0.9 Block user comment: N Private report: N New Comment: > 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. Previous Comments: ------------------------------------------------------------------------ [2021-08-17 11:49:24] cevincheung at gmail dot com bindValue(':id', int, PDO::PARAM_INT) got: int(1) ------------------------------------------------------------------------ [2021-08-17 11:40:21] 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" ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=81367&edit=1