PDO防注入原理分析以及注意事项

我们都知道，只要合理正确使用PDO,可以基本上防止SQL注入的产生，本文主要回答以下两个问题：

为什么要使用PDO而不是mysql_connect？

为何PDO能防注入？

使用PDO防注入的时候应该特别注意什么?

一、为何要优先使用PDO?

PHP手册上说得很清楚：

代码如下:
Prepared statements and stored procedures
Many of the more mature databases support the concept of prepared statements. What are they? They can be thought of as a kind of compiled template for the SQL that an application wants to run, that can be customized using variable parameters. Prepared statements offer two major benefits:

The query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters. When the query is prepared, the database will analyze, compile and optimize its plan for executing the query. For complex queries this process can take up enough time that it will noticeably slow down an application if there is a need to repeat the same query many times with different parameters. By using a prepared statement the application avoids repeating the analyze/compile/optimize cycle. This means that prepared statements use fewer resources and thus run faster.

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).

 

即使用PDO的prepare方式，主要是提高相同SQL模板查询性能、阻止SQL注入

同时，PHP手册中给出了警告信息

代码如下:
Prior to PHP 5.3.6, this element was silently ignored. The same behaviour can be partly replicated with the PDO::MYSQL_ATTR_INIT_COMMAND driver option, as the following example shows.
Warning
The method in the below example can only be used with character sets that share the same lower 7 bit representation as ASCII, such as ISO-8859-1 and UTF-8. Users using character sets that have different representations (such as UTF-16 or Big5) must use the charset option provided in PHP 5.3.6 and later versions.

意思是说，在PHP 5.3.6及以前版本中，并不支持在DSN中的charset定义，而应该使用PDO::MYSQL_ATTR_INIT_COMMAND设置初始SQL, 即我们常用的 set names gbk指令。

我看到一些程序，还在尝试使用addslashes达到防注入的目的，殊不知这样其实问题更多, 详情请看http://www.lorui.com/addslashes-mysql_escape_string-mysql_real_eascape_string.html

还有一些做法：在执行数据库查询前，将SQL中的select, union, ....之类的关键词清理掉。这种做法显然是非常错误的处理方式，如果提交的正文中确实包含 the students's union , 替换后将篡改本来的内容，滥杀无辜，不可取。