PostgreSQL 8.1.4 Released to Plug Injection Hole

alurkar writes to tell us that PostgreSQL released version 8.1.4 today in order to combat a security flaw allowing a SQL injection attack. From the article: "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.'"

Comments

Re:This is why...

[jrockway]

It especially bugs me because it's easier to Do Things Right. The DBI manpage for perl doesn't even mention the sloppy way that nearly everyone uses... but they do it anyway! In nearly every database application / script I look at, people do things like $dbh->execute("SELECT * FROM foo WHERE bar=$bar AND baz=$baz") after "escaping" $bar and $baz. No, no, no!

It's much easier to prepare a query handle and then execute it as needed:

$sth = $dbh->prepare("SELECT a,b,c FROM foo WHERE bar=? and baz=?")
$sth->execute($bar, $baz);


Not only is it more efficient (if you're going to use the same query twice), it's secure by default. Let the database programmers handle the Hard Stuff (parsing) so that you can concentrate on your application.

Speaking of which, is there a way to do this in PHP? I've never seen a PHP script that did anything like this (which is probably why bugtraq is 99% php SQL injection holes).

Use placeholders!

[mortonda]

This is why I gripe and complain anytime I see someone doing sql calls without using placeholder routines, such as perl's DBI or PEAR::DB for php. From the technical doc that someone posted above:

If your code is doing escaping "by hand", for instance by doubling quotes and backslashes, you really need to fix it to use the library routines instead. If you're avoiding the need for escaping at all, by sending variable strings as out-of-line parameters, then you've saved yourself a whole lot of trouble and can stop worrying.

Start using a proper placeholder syntax and variable substitution for parameters when it comes to untrusted data. It solves a lot of problems.

Re:Character encodings yet again

[edwdig]

Unicode isn't a character encoding, it's a character set. According to this unicode faq, there are 13 different encodings for Unicode. Switching to Unicode doesn't help the problem of character encodings.

Re:This is why...

[Dwonis]

Speaking of which, is there a way to do this in PHP? I've never seen a PHP script that did anything like this (which is probably why bugtraq is 99% php SQL injection holes).

Most people probably aren't aware of it, but several years ago, I wrote a few short scripts for PHP 4 that specifically address this problem. Currently-supported database backends are MySQL and anything that DBX supports, but it wouldn't take much to adapt it to PostgreSQL.

It basically lets you write code like this:

require_once "mysqlext.php";
$link = mysql_connect(...);
$results = mysql_execute($link, "SELECT a,b,c FROM foo WHERE bar=? and baz=?", array($bar, $baz));

It doesn't have the performance benefits that real prepared statements have, but I still find it handy for typical PHP4 database work.

The code is released under the MIT license, so feel free to use it.

Re:This is why...

[Slashcrunch]

For PHP, Zend_Db has a way of doing this which is very similar to the way you do it in Perl and Java. It's quite nice. There are other ways of doing this as well :) // get a Zend_Db_Adapter (basically a DB connection)
$db = getConnection(); // the sql with a placeholder for a parameter called 'id'
$sql = 'select * from Foo where id = :id'; // anyparameters are defined in the array. in this case, just 'id'
$params = array('id' => $id); // send the query
$result = $db->query($sql, $params);

Re:I dont see how UTF-8 is vulnerableg

[RuneB]

PostgreSQL ignored invalid UTF-8 sequences, meaning a ' character at the end of a incomplete sequence could cause only one ' to be seen by the parser when escaped.
See http://www.postgresql.org/docs/techdocs.50 for the details.