The way PDO parametrized query works

Question

PLEASE READ THE QUESTION CAREFULLY. It is not usual silly "my code doesn't work!!!" question.

When I run this code with intended error

try {
  $sth = $dbh->prepare("SELECT id FROM users WHERE name INN(?,?) ");
  $sth->execute(array("I'm","d'Artagnan"));
} catch (PDOException $e) {
    echo $e->getMessage();
}

I get this error message

You have an error in your SQL syntax ... near 'INN('I\'m','d\'Artagnan')' at line 1

But I thought for years that query and data being sent to the server separately and never interfere. Thus I have some questions (though I doubt anyone got an answer...)

1. Where does it get such a familiar string representation - quoted and escaped? Is it being made especially to report an error or is it a part of actual query?
2. How does it work in real? Does it substitute a placeholder with data or not?
3. Is there a way to get whole query, not only little bit of it, for debugging purposes?

Update

mysqli does it as expected: it throws an error says near 'INN(?,?)'

Answer 1

Oh boy, what a day Cassy has the right answer and I'm going to bed...

Answer 2

I'm not sure about all the details, but I will try to answer.

1. The quotation happens on the database side. The database escapes and sanitizes all values (see bullet 2) it receives so that it gets interpreted correctly. The moment the error is thrown, the database (in this case MySQL) prints out the query it tried to run. This wouldn't be so helpful if it just showed the prepared part.

2. No, it doesn't. At preparation time the query gets compiled on the server side. When a query is executed with values, only the values are transmitted. This is pretty much the same as calling PREPARE and EXECUTE on the database directly.

3. This depends on the database you're using. MySQL for example can log all queries to a log file (check my.cnf settings for that). But you can also use debugDumpParams() on PHP side.

I hope this was a bit helpful.

Answer 3

try adding

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

;)