Are PHP MySQLi prepared queries with bound parameters secure?

Question

Historically, I've always used

mysql_real_escape_string()

for all input derived from users that ends up touching the database.

Now that I've completely converted over to MySQLi and I'm using prepared queries with bound parameters, have I effectively eliminated the possibility of SQL injection attacks?

Am I correct in saying I no longer need

mysql_real_escape_string()?

This is my understanding and the basis of a project of mine: http://sourceforge.net/projects/mysqldoneright/files/Base/MysqlDoneRight-0.23.tar.gz/download

This is not something I want to get wrong though as now that I've released it, it could affect others as well.

All user provided input will now end up in bind_parms.
The queries provided in the prepare phase are static.

Answer 1

Yes. Using the prepared query will escape parameters.

Answer 2

When you bind parameters to a prepared statement, it escapes the data automatically, so you shouldn't escape it before you send it through. Double escaping is usually a bad thing. At the very least, it produces ugly results with extra escaped characters later on.

Answer 3

It's not so simple. You can use bound parameters instead of interpolating application variables into SQL expressions in place of literal values only:

$sql = "SELECT * FROM MyTable WHERE id = ".$_GET["id"]; // not safe

$sql = "SELECT * FROM MyTable WHERE id = ?"; // safe

But what if you need to make part of the query dynamic besides a literal value?

$sql = "SELECT * FROM MyTable ORDER BY ".$_GET["sortcolumn"]; // not safe

$sql = "SELECT * FROM MyTable ORDER BY ?"; // doesn't work!

The parameter will always be interpreted as a value, not a column identifier. You can run a query with ORDER BY 'score', which is different from ORDER BY score, and using a parameter will be interpreted as the former -- a constant string 'score', not the value in the column named score.

So there are lots of cases where you have to use dynamic SQL and interpolate application variables into the query to get the results you want. In those cases, query parameters can't help you. You still have to be vigilant and code defensively to prevent SQL injection flaws.

No framework or data-access library can do this work for you. You can always construct a SQL query string that contains a SQL injection flaw, and you do this before the data-access library sees the SQL query. So how is it supposed to know what's intentional and what's a flaw?

Here are the methods to achieve secure SQL queries:

• Filter input. Trace any variable data that gets inserted into your SQL queries. Use input filters to strip out illegal characters. For instance, if you expect an integer, make sure the input is constrained to be an integer.

• Escape output. Output in this context can be the SQL query which you send to the database server. You know you can use SQL query parameters for values, but what about a column name? You need an escaping/quoting function for identifiers, just like the old mysql_real_escape_string() is for string values.

• Code reviews. Get someone to be a second pair of eyes and go over your SQL code, to help you spot places where you neglected to use the above two techniques.