From PHP, i am querying MySQL database using PDO. Query is
$id = 5;
$stmt = $con >prepare("select name from employee where id= ? ");
$stmt ->bindValue(1, $id, PDO::PARAM_INT);
This is working as expected and the name of the employee with id 5 is fetched. However from the logs I could see that the query actually executed is
select name from employee where id= '5'
id was int type and binding was done using PDO::PARAM_INT. so the query executed should have been id= 5 and not id= '5'. MySql had to possibly covert string to int due to this,
Is this expected behavior with PDO or is there an error in my understanding?
It's possibly a bug in PHP, this ticket or related to this one. A commit to fix this bug have been submitted (Tue, 11 Oct 2016), wich says :
The prepared statement emulator (pdo_sql_parser.) figures out how to quote each query parameter. The intended type is specified by the PDO::PARAM_ consts, but this direction wasn't always followed
What is your version of PHP? An update can probably fix it.
A user-contributed note in http://php.net/manual/en/pdostatement.bindvalue.php specifies the following:
"Emulated prepares work more stable in this cases, because they convert everything to strings and just decide whenever to quote argument or not to quote."
Ref: http://php.net/manual/en/pdostatement.bindvalue.php#119956