Q: Can you give an example that parameterized query prevent the SQL injection attack when a user input to the query still contains a special character to cause harm?
A: There have been some multibyte character exploits in code that doesn't properly account for character sets, resulting in holes in the escaping mechanism. (Where the "escape string" thinks it's working on a string in particular encoding, but the actual bytes are in a different encoding, and sneakily sliding single quotes into the SQL text.)
But I don't really think that's the strongest argument for prepared statements with bind placeholders.
A strong argument is that when we look at the code, we see static SQL text, not dynamically generated...
$sql = 'SELECT fee, fi FROM fo WHERE fum = ?';
$dbh->prepare($sql);
We see that code, and we look at that SQL text... and we recognize immediately, there is no way that the SQL text is going to be other than what we see. We don't have to look anywhere else in the code; we see it right there on two lines.
When we see this:
$sql = "SELECT fee, fi FROM fo WHERE fum = $fumval";
That's double quotes, there's variable interpretation going on. Is $fumval guaranteed to be safe for inclusion in the SQL text, where did $fumval come from? Should there be single quotes around $fumval, or are we guaranteed that it's already enclosed in single quotes?
Okay, maybe there's a line right before that:
$fumval = $dbh->quote($unsafe_fumval);
If that line isn't right above the generation of the SQL text, we need to go check... are we guaranteed the $fumval is safe?
The point is this... the SQL is being dynamically constructed. It might be better if it was done like this:
$sql = 'SELECT fee, fi FROM fo WHERE fum = ' . $dbh->quote($unsafe_fumval);
For a simple statement, maybe it's six of one and half a dozen of the other. But when SQL statements get larger, involving multiple tables and dozens of column references and values, the dynamic construction gets harder to verify that there are not any problems in it.
Is it possible to write secure code using dynamically generated SQL and "escape string" processing on values? YES.
Is it possible to write vulnerable code that uses prepared statements with dynamically generated SQL text? YES.
It's really the pattern of static SQL text, passing values provided through bind placeholders is what gets us the bang for our buck... code that is written in a way in which we can identify as not vulnerable to SQL injection.
