Slashdot Mirror
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.'"
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).
My other car is first.
heh, heh, heh... I'll plug your injection hole, baby!
Must....not....make....joke....about...injection hole...being plugged...
Damn, too late.
=\
If someone wants to try and help me with this problem, let me know, but I had trouble getting mysql to insert (actually doing COPY since it's about 10x faster) anywhere near what I can get with PG8.
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.
> didn't realize that dates of the form 2006-05-01 needed quotes
:)
There's a difference between fundamentally flawed thinking and a bug. Someone found this bug, it was fixed in 30 seconds, and suddenly everyone using DBI had a more robust solution. Software's never going to be "perfect", but it's likely that a group of people trying to solve a problem in general is going to have a better product than something you came up with after lunch
My other car is first.
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:
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.
PostgreSQL defaults to SQL-ASCII encoding, which is unaffected by this particular attack. Only clients which connect using a multibyte encoding would be affected.
Actually, this really isn't a vulnerability in the database server itself -- the update just intentionally breaks certain badly written applications in order to protect them from themselves. If PHP's addslashes() ends up creating valid multibyte characters that produce unexpected behavior, that's really PHP's problem -- Postgres is just doing what it's told.
Multi-Layered validation is the only way to go.
Client validation is only useful for round-trip bandwidth reduction, it's nice to have, but not secure in any way. It can stop the occasional accidental bad input. (e.g. entering strings when numerical data is called for, pop up a message box telling you not to do that), it won't stop anyone really interested in corrupting your data.
The app server should be validating everything being posted to it. Is this string too long, too short, not a string, wrong encoding, etc...
The DB server should ALSO be validating everything coming from the app server. Don't trust your application server, it could have a bug, it could have been hacked, it might not be your app server, who knows. Strict stored procedures with no r/w access to tables is a really the only way to go. (To: My Co-Workers, Using select * queries and running as dbo and/or sa is usually a sign that you're not doing it right)
Yes, it's paranoid thinking, yes, it's more work and yes, there is a slight performance hit, but it is secure and it's damn hard to break.
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) // the sql with a placeholder for a parameter called 'id' :id'; // anyparameters are defined in the array. in this case, just 'id' // send the query
$db = getConnection();
$sql = 'select * from Foo where id =
$params = array('id' => $id);
$result = $db->query($sql, $params);
'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.'
How come the php documentation doesn't mention this?