8.1.4 et. al. Security Release FAQ
Q: What's being released?
A: PostgreSQL minor versions 8.1.4, 8.0.8, 7.4.13 and 7.3.15. All of these versions implement a security patch which eliminates a specific vulnerability to SQL injection attacks.
Q: Who does this vulnerability affect?
A: Users of PostgreSQL servers which are exposed to "untrusted input", from the internet or otherwise, and use any "multi-byte encoding", such as UTF-8 or SJIS. Basically, most open source database users with Web applications.
Q: Who is not affected?
A: Several kinds of applications:
1. Users whose database applications are not exposed to untrusted input, such as single-user applications not exposed to the Internet.
2. Databases which are set up with "LATIN-1" or other single-byte encoding, on both client and server.
3. If application always sends untrusted strings as out-of-line parameters, instead of embedding them into SQL commands, it is not vulnerable. This is only available in PostgreSQL 7.4 or later.
Q: Is anyone in particular danger?
A: Applications using Far East encodings (SJIS, BIG5, GBK, GB18030, and UHC) which use ad-hoc methods to "escape" strings going into the database, such as regexes, or PHP3's addslashes() and magic_quotes. Since these bypass database-specific code for safe handling of strings, many such applications will need to be re-written to become secure. (Note that the PHP team deprecated addslashes() and magic_quotes in version 4.0 because of the security risk. Unfortunately, it still appears in a distressing number of freeware PHP applications online.)
Q: When will updates be available?
A: Source packages are available now, as well as binary packages for Windows, Red Hat Linux and a few other operating systems. Updates for PostgreSQL drivers for most programming languages will be available by Wednesday, with others to follow in less than a week.
Q: What should users do?
A: Three steps:
1. Read the full release notes on the new patches;
2. Update their database servers and their database drivers;
3. Remove any non-standard string escaping mechanisms from their applications, such as the popular "backslash-escape", or "\'", or at least modify them to use the SQL-standard doubling () to escape quotes.
Q: Ouch! Step #3 looks painful. Do I really need to refactor my application?
A: Only if you are using a Far Eastern multi-byte encoding (SJIS, BIG5, GBK, GB18030, and UHC). If you are using UTF-8, then applying the new version of PostgreSQL is sufficient. Of course, you should plan to migrate your application to use proper escaping functions when you can, so that it won't break when PostgreSQL transitions to SQL-standard strings (which don't have backslash escapes). SQL-standard strings will become the default soon, perhaps as soon as PostgreSQL 8.3.
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.
Q: What if I'm using PostgreSQL 7.2 or 7.1?
A: Then you should have upgraded two years ago. PostgreSQL 7.2 has been out-of-maintenance since 2004. Please upgrade to a more current version as soon as you can.
Q: Why did you release a security update which breaks my application?
A: Believe us, we tried not to. Six PostgreSQL programmers worked for four weeks to come up with a method to fix the vulnerability without affecting production applications. This was the best we could do -- it leaves most users' applications untouched.
Q: What if my application is not exposed to the internet, and I want to upgrade but disable the escaping changes?
A: Set "backslash_quote = on" in postgresql.conf. And make plans to secure your application at a more convenient time.
Q: You say PHP hackers shouldn't use addslashes() or magic_quotes_gpc. What should they use?
A: Any of the following:
1. pg_escape_string() (but look for a driver update soon)
2. PEAR-DB (safe against this exploit, but may be vulnerable to future issues as it uses ad-hoc escaping)
3. NOT PEAR-MDB2 (according to the PEAR development team, MDB2 is currently using a custom escape routine which is currently unsafe. Expect an update next week.)
5. If you're using magic_quotes_gpc, test using magic_quotes_sybase instead (and plan a refactor -- magic_quotes will not be in PHP 6.0)