I learned that using prepared statement (parameterized queries) can be used for preventing SQL injection attack with few exceptions mentioned in this post: Are PDO prepared statements sufficient to prevent SQL injection?.
My question is how a prepared statement prevents SQL injection? What I've learned so far:
When using parameterized query, the client sends the query to server for preparation (optimization) without any data, and later, parameters (user input or any) will be sent to the server so that the user data resides outside the original query.
When using prepared query, if user data is not escaped, then it will not prevent any SQL injection.
For this reason, I failed to test the example because using execute(), bind_param(), binVlaue(), binParam() are all escaping the data when used so that we can't send un-escaped data with them for testing the safety of prepared statement with un-escaped parameters.
The basic problem is this:
$value = "Foo' OR 1 = 1 --";
$query = "SELECT id FROM users WHERE name = '$value'";
Concatenated as above, this is the actual query:
SELECT id FROM users WHERE name = 'Foo' OR 1 = 1 --'
The problem here is the character ', which in a string literal context has the special meaning of terminating the string literal.
One technique to deal with this is to escape characters which have a special meaning in string literals:
SELECT id FROM users WHERE name = 'Foo\' OR 1 = 1 --'
The other way is to separate the string literal out entirely, so there cannot be any confusion about where it starts and ends:
query: SELECT id FROM users WHERE name = $1
$1: Foo' OR 1 = 1 --
The former technique is escaping, the latter is parameterized queries. You only need to use one of them. If you escape values that you also parameterize, you're just messing up your values with unnecessary backslashes.