The binding process in mysql prepared statements

Question

It is well publicized that prepared statements are a good defense against SQL injection attacks.

Can someone explain what happens in the binding process that makes this so.

My main confusion stems from the fact that a statement uses placeholders and these placeholders are replaced with variables. Therefore if the variables contain malicious sql then they are still bound in place of the placeholders?

Answer 1

No, the binding process asures that the binding values are a 1:1 match to the value that comes into the database.

So, 'xxx; DROP TABLE yyy;' as a value would be the actual value in the varchar field. When not using parameter binding, this code would be executed.

Answer 2

First of all, PDO sanitizes what a DB engine sees as constants, e.g.: values on an INSERT clause, comparison values on a WHERE clause, and so on, but it do not sanitizes identifiers or keywords or other things. (a side note: It not either sanitizes dynamic SQL, so you can't rely only on PDO if you use dynamic SQL)

About the point that you are asking, you shoud note that on any parameter binding, you provide the kind of binding that you want: string or numeric or boolean (or others, I do not remember). So he fact is:

• Declared numeric and boolean parameter values do no inject dangerous code as PDO can easy check it constraint to their type, and generate the corresponding text.
• Null values are easy converted to Null keyword.
• The conflictive values, of course then are of type string, and what does PDO do here? it sanitizes in the same way that mysqli::real_escape_string does.