Nio’s Weblog

PostgreSQL minor versions 8.1.4, 8.0.8, 7.4.13 and 7.3.15 have been updated to address a SQL injection vulnerability, for applications that embed untrusted input directly into SQL statements (i.e., avoiding the PreparedStatement facility for escaping input).

和 MySQL 的 SQL 注射类似，假设有如下代码：

<?php
$sql = "select * from users where username='".$username."' and password='".$password."'";
//....
?>

那么在填写密码的时候使用 "a' or username like '%' or password like '%"，这样 SQL 语句就变成了：

select * from users where username='a' and password='a'
    or username like '%' or password like '%'

这样就可以绕过密码检测了。

不过这个问题很容易解决，可以使用 prepare statment 的方式（PHP 调用 pg_prepare() 函数）解决，也可以通过用户名取出密码，然后再做字符串的比较，个人觉得，预防 SQL 注射，主要还是在编码者，当然相应的语言也应该提供简单方便的函数以供调用。

官方的解决办法更加具体：

Q: What else can I do to foil SQL injection attacks?
A: There are quite a number of steps you can use to foil SQL injection attacks in addition to these updates, which are things security-conscious application developers should be doing anyway:

1. Employ a good database security design, in which restrictive database permissions are assigned to public database roles.
2. Use parameterized prepared statements to execute queries (e.g. "SELECT * FROM table WHERE id = ?") (please note that PHP does not have proper support for this feature earlier than v5.1)
3. Use stored procedures to execute queries from web applications, instead of sending them directly to the database.
4. Hash or encrypt valuable data in the database.