PostgreSQL addresses SQL injection vulnerabilities

The PostgreSQL project released updated versions of the PostgreSQL 8.1, 8.0, 7.4, and 7.3 series today to address a SQL injection vulnerability.

The vulnerability was discovered in March by Akio Ishida, Yasuo Ohgaki, and Tatsuo Ishii. Ishida and Ohgaki are members of the Japanese PostgreSQL User Group, and Ishii is an employee of SRA-OSS Japan. The three were studying PostgreSQL, as well as other open source software, for the IPA security center.

The researchers discovered that PostgreSQL was vulnerable to a SQL injection attack through some applications running PostgreSQL. According to PostgreSQL core developer Josh Berkus, the vulnerability was passed to the PostgreSQL core team on April 1, 2006 by Ishii. The team verified that the discovery was a serious vulnerability, and began work on a patch.

Vulnerability details

The vulnerability affects PostgreSQL servers exposed to untrusted input, such as input coming from Web forms, in conjunction with multi-byte encodings like Shift-JIS (SJIS), 8-bit Unicode Transformation Format (UTF-8), 16-bit Unicode Transformation Format (UTF-16), and BIG5.

In particular, Berkus says that applications using “ad-hoc methods to ‘escape’ strings going into the database, such as regexes, or PHP3’s addslashes() and magic_quotes” are particularly unsafe. “Since these bypass database-specific code for safe handling of strings, many such applications will need to be re-written to become secure.” He also notes that the addslashes function was deprecated in PHP 4.0 due to security risks, but a “distressing” number of PHP applications continue to use the function.

The problem with addslashes

The problem with addslashes has been under discussion for some time, even before the discovery of the current vulnerability in PostgreSQL. Chris Shiflett explains how addslashes can modify an invalid multibyte character to a single quote to execute a SQL injection attack:

If I want to attempt an SQL injection attack … having single quotes escaped with a backslash is a bummer. If you’re using addslashes(), however, I’m in luck. All I need to do is inject something like 0xbf27, and addslashes() modifies this to become 0xbf5c27, a valid multi-byte character followed by a single quote. In other words, I can successfully inject a single quote despite your escaping. That’s because 0xbf5c is considered to be a single character, not two. Oops, there goes the backslash.

Changes made to PostgreSQL

Updated versions of PostgreSQL will reject invalid multibyte characters, and rejects the use of the backslash to escape a single quote in a string literal if the client_encoding is set to a vulnerable encoding.

The new versions also introduce a backslash_quote parameter to allow use of the backslash if needed, but users will have to choose to set it in an insecure mode deliberately.

The updated versions of PostgreSQL should address the vulnerability for users of UTF-8, but Berkus says that users of Far East encodings, including SJIS, BIG5, GBK, and others, are not safe if they continue to use backslash-escape “unless their application uses parameterized prepared queries exclusively.”

The PostgreSQL project also recommends several additional procedures to keep your database safe from SQL injection attacks:

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

Affected versions

All versions of PostgreSQL, prior to the versions released today, are vulnerable. The vulnerability also affects drivers for PostgreSQL, and Berkus says that new drivers should be available for ODBC, Ruby, Perl, .Net, and C++ by the end of the week.

Source packages of PostgreSQL 8.1.4, 8.0.8, 7.4.13, and 7.3.15 are available now, and Berkus says that binary packages should be available within 48 hours for “most platforms” either through the PostgreSQL project or through vendor updates. Users that are still using PostgreSQL 7.2 or earlier should upgrade to a newer PostgreSQL release, as maintenance for PostgreSQL 7.2 ended in 2004.

PostgreSQL users should see the FAQ, and read the release notes prior to upgrading.