PDO sends raw query to MySQL while Mysqli sends prepared query, both produce the same result

Question

I started to know how prepared statement works when using MySQLi and PDO, for first step, I enabled MySQL query monitoring as mentioned here: How can I view live MySQL queries?. Then I created the following test:

Using mysqli:

$stmt = $mysqli->prepare("SELECT * FROM users WHERE username =?")) {
$stmt->bind_param("i", $user);
$user = "''1''";

server logs:

  130802 23:39:39   175 Connect   ****@localhost on testdb
    175 Prepare   SELECT * FROM users WHERE username =?
    175 Execute   SELECT * FROM users WHERE username =0
    175 Quit

Using PDO:

  $user = "''1''";
  $sql = 'SELECT * FROM user WHERE uid =?';
  $sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
  $sth->bindParam(1, $user, PDO::PARAM_INT);

Server Logs:

  130802 23:41:42   176 Connect   ****@localhost on testdb
    176 Query SELECT * FROM user WHERE uid ='\'\'1\'\''
    176 Quit

However, both provides same result:

uid: 0
username: admin
role: admin

Note: uid = 0 is correct because intval("''1''") = 0

What is important here:

How PDO query gets same result while it sends different query to MySQL?

SELECT * FROM user WHERE uid ='\'\'1\'\''

I found only one indication from PHP Manual: http://www.php.net/manual/en/pdo.prepare.php

Note:

Emulated prepared statements does not communicate with the database server so PDO::prepare() does not check the statement.

But I am not sure How MySQL deals with this query and substitute '\'\'1\'\'' with 0. At that case monitoring queries will not be accurate if using PDO, at same time, using PDO is better for knowing exact queries that sent to MySQL but not MySQLi.

Update: after changing parameter type frm integer to string:

MySQLi Log:

    188 Prepare   SELECT * FROM awa_user WHERE username =?
    188 Execute   SELECT * FROM awa_user WHERE username ='\'\'1\'\''
    188 Quit

PDO Log:

    189 Query SELECT * FROM awa_user WHERE userame ='\'\'1\'\''
    189 Quit

This means that MySQLi and PDO escaping the data before sending to MySQL when using string, while for integers, mysqli applies intval() or something like that before sending the query, as also answered by Bill which is correct.

Answer 1

Your PDO is configured to emulate prepared queries, whereas mysqli is using true prepared queries.

The prepared query binds the string ''1'' as an integer parameter value. PHP coerces it to an integer using something like intval(). Any string with non-numeric leading characters is interpreted as 0 by PHP, so the parameter value sent after prepare is the value 0.

The fake prepared query uses string interpolation (instead of binding) to add the string ''1'' into the SQL query before MySQL parses it. But the result is similar, because SQL also treats a string with non-numeric leading characters in an integer context as the value 0.

The only difference is what ends up in the general query log when the parameter is bound before prepare versus after prepare.

You can also make PDO use real prepared queries, so it should act just like mysqli in this case:

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

PS: This may demonstrate a good reason why it's customary to start id values at 1 instead of 0.