PDO queries with integer type

Question

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?

However from the logs - logs of which system? MySQL? PHP? Some custom logger? — N.B.
– N.B., Commented Jul 20, 2017 at 21:58
So, If I'm not mistaken, MySQL might be quoting integer values in its logs. It's easy to verify whether I'm right or wrong - simply run a query from MySQL terminal and check if ints get quoted or not in the logs. If not, excellent, PHP is to blame (emulated prepares and potential lack of mysqlnd). If it is quoted, then MySQL simply quotes all parameters for logging purposes. — N.B.
– N.B., Commented Jul 20, 2017 at 22:17
@Kiran I'm curious, is this the first time that this happens? — Funk Forty Niner
– Funk Forty Niner, Commented Jul 20, 2017 at 22:18
i have been using pdo for long. but today i enabled logs to analyze queries and then noted this thing. I believe the behavior was same all the time. I searched similar questions and could not find any. so i was under the impression that i am doing something wrong. — Kiran
– Kiran, Commented Jul 20, 2017 at 22:20

Ayak973 · Accepted Answer · 2017-07-20 22:07:38Z

1

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.

answered Jul 20, 2017 at 22:07

Ayak973

4541 gold badge6 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kiran Over a year ago

my php version is 5.6.31

Ayak973 Over a year ago

I've read on github that the fix has been merged to PHP7.1, but cannot find more useful informations, since the bugtracker says "PHP version: irrelevant".

Funk Forty Niner · Accepted Answer · 2017-07-20 22:07:51Z

1

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

answered Jul 20, 2017 at 22:07

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

10 Comments

Kiran Over a year ago

my programming knowledge is limited. Possibly a silly question, but I read articles suggesting to avoid MySQL auto typecasting as much as possible. But when we use PDO, for integer types, we have to then depend on mysql typecasting (string to int). is this a good approach?

Funk Forty Niner Over a year ago

@Kiran it's not a silly question. I believe this post softwareengineering.stackexchange.com/questions/24378/… will explain it faster than I, as would security.stackexchange.com/questions/13776/… - which would depend on its application. If any of the approaches work, then by all means, do.

Funk Forty Niner Over a year ago

@Kiran there is one thing though when dealing with integers, is to make sure that you're not passing a possible octal. One such as $var = 01; would fail in having a leading zero, should this be relevant, and would require it to be quoted. I.e. $var = '01';

Funk Forty Niner Over a year ago

@Kiran "Casting" though, would be necessary though when having to deal with converting integers to strings and other possible uses. Here are a few references dev.mysql.com/doc/refman/5.7/en/cast-functions.html --- mysqltutorial.org/mysql-cast

Funk Forty Niner Over a year ago

@Kiran That would mean that this is happening at the core level of PDO's data processing, not just for bindValue().

|

Collectives™ on Stack Overflow

PDO queries with integer type

2 Answers 2

2 Comments

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related