在PHP中使用预准备语句/存储过程时，如何保护自己免受SQL注入？

Question

I've been looking at how best to protect against sql injection in PHP/mysql beyond just using the mysqli/mysql real escape since reading this Is mysql_real_escape_string enough to Anti SQL Injection?

I have seen this very good thread How can I prevent SQL injection in PHP?

I use to do alot of ms sql server stuff on the desktop/internal tools, we always wrote stored procedures to protect against this so I read up on the equivalent in PHP/mysql using PDO http://php.net/manual/en/pdo.prepared-statements.php

In the above there is the line :

The parameters to prepared statements don't need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible).

I've been lead to believe that PDO do protect against sql injection attacks so can anyone provide a instance where PDO isnt sufficient from a security standpoint?

Answer 1

You can still get SQL injections from stored procedures which are internally using the PREPARE syntax (in MySQL) to create dynamic SQL statements.

These need to be done with extreme care, using QUOTE() as necessary.

Ideally, we should not need to use PREPARE in stored routines, but in certain cases it becomes very difficult to avoid:

• Prior to MySQL 5.5, the LIMIT clause cannot use non-constant values.
• Lists used in an IN() clause cannot be (sensibly) parameterised, so you need to use dynamic SQL if this pattern is used
• It is sometimes desirable to use dynamically generated ORDER BY clauses.

etc

In the case where it is necessary to use PREPARE, then I would recommend, in order of preference:

• If something is an INT type (etc) it is not susceptible to SQL injection, and you can place the value into the query without a problem (e.g. for LIMIT)
• String values can be placed into an @variable before the EXECUTE, or passed in to the EXECUTE clause
• List-values (for example for IN()) need to be checked for validity.
• Finally, QUOTE() can be used to quote string values, which can be useful in some cases