Mysqli prepared statements and mysqli_real_escape_string

Question

I am currently using the mysqli php extension.

Traditionally, I used mysqli_real_escape_string to exit user input. However, I am looking at changing the code (I hope as few steps as possible) to use prepared statements.

I want to be clear on this - if I use prepared statements to bind all my variables, can I be sure that SQL injection is not possible? (And completely abandon mysqli_real_escape_string?)

thanks

Answer 1

If you bind all your variables correctly, you can significantly reduce the risk of SQL injection. You can still get SQL injection if you create SQL dynamically, for example:

'SELECT * FROM ' . $tablename . ' WHERE id = ?' 

But if you avoid such situations, you are unlikely to have problems.

Answer 2

Speaking of security, there is no difference between both methods if you correctly bind or format variables.

Binding is simply simpler because it can only be used for any occasion, while escaping cannot (so you need to use some variables instead of escape quotes).

Also, keep in mind that no bindings or escaping can make the identifier safe. So, if you need to use the name of a field or statement in your query, you must use a value hardcoded in the script.

Answer 3

Here is my high-level view on this topic.

When using dynamic SQL strings, you rely on an evacuation function that works correctly. Unfortunately, this is not always the case, as can be seen in this (admittedly old) example:

http://dev.mysql.com/doc/refman/5.0/en/news-5-0-22.html

After your data values ​​have been escaped, the SQL string must be parsed and compiled by the database server. If the escaping function did not do its job properly or a smart new SQL injection attack is detected, there is a chance that the server will mistakenly receive data for SQL statements.

If you use prepared statements with parameters, the statement is parsed and compiled first. Data values ​​are combined with the compiled statement when it is executed. This separates SQL logic from data values ​​— the possibility of confusion should never arise.

So, yes, you can do without mysqli_real_escape_string , but I would not say that using prepared statements with parameters makes SQL injection impossible. This makes it much more complicated, but, as with the mysqli_real_escape_string error, I believe that there is always the possibility that a detected (or newly created) error will seem impossible, perhaps.