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

This is why...

[ArchAngelQ]

whitelisting, not blacklisting, is a good idea. Stop trying to define a set of 'wrong' data. Define a set of good data.

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

Re:This is why...

[onlyjoking]

Speaking of which, is there a way to do this in PHP?

PHP5's mysqli extension enables you to use prepared queries.

Re:This is why...

[jrockway]

> 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 :)

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:This is why...

[OnyxRaven]

PEAR::DB supports almost the exact same method.

$data = array('one',2);

(short)
$result = $db->query('select * from table where foo=? and bar=?',$data);

(prepare)
$stmt = $db->prepare('select * from table where foo=? and bar=?');
$result = $db->execute($stmt,$data);

Works with mysql, pgsql, mssql... etc etc. MDB2 is the new version of this library which uses much the same syntax. Uses database-specific escaping/quoting automatically.

http://pear.php.net/manual/en/package.database.php

Re:This is why...

[a.d.trick]

Yes, but properly escaping everything is at least as important. Whitelisting and blacklisting can't be used in a lot of situations (for example text fields) without causing problems. The easiest way to do that is to use an existing library that handles most of that for you. The more you have automated, the less room there is for human error. Unfortunatly, PHP coders tend to trive in reinventing the wheel.

Widespread problems...

[ByTor-2112]

Most of the PHP apps I've ever had the (mis)pleasure to peruse make liberal use of this type of "escaping" rather than calling the provided "escape_string" functions. That never made any sense to me, but the practice appears to be quite common.

Re:Widespread problems...

[Jac_no_k]

I recently switched from coding for single company to joining a consulting firm. I'm shocked at how sloppy the commands sent to the database are. It drives me nuts and makes me want to fix all the code... but since I'm low man on the totem pole, my concerns are replied generally with lame excuses like "emulate the coding style of the original author", "we don't get paid much, so it's okay to be sloppy", or "we have a deadline to meet". And no, I can't find a new gig.

Some of the sites I've worked with are vulnerable to this type of injection attack. From my perspective, this is widespread, a bit scary, and should be nice little eye openner as sites get hacked. This may actually be a blessing as I could start pointing what happens with sloppy code and not being given enough time clean everything up.

Re:Widespread problems...

[ultranova]

Loads of fun that worthless whore is.

If a whore is "loads of fun", then she's not worthless, since she can propably get a good price once the word spreads. Just because you are trolling is no excuse to be illogical.

Now let's see if someone mods me Insightfull or Informative...

Character encodings yet again

[Bogtha]

Mismatches between different character encodings seem to have been responsible for vast swathes of security vulnerabilities over the past few years. The sooner everybody moves to programming languages and software that use Unicode natively, the more secure we will all be.

Unfortunately, the languages receiving the most attention for web development have abysmal Unicode support. PHP and Ruby haven't a clue, although the next version of PHP is supposed to be much better in this respect. Python developers can at least handle things fairly well, although it's still a bit of a pain in the neck.

This vulnerability is probably going to cause quite a few problems for people, as it's a client issue that will probably need whatever adapter you use to be updated. Here is the user guide to the vulnerability for PostgreSQL. psycopg should be fixed shortly.

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.

Plug Injection Hole

[fudgefactor7]

heh, heh, heh... I'll plug your injection hole, baby!

The jokes, they write themselves!

[Kha Na Set]

Must....not....make....joke....about...injection hole...being plugged...

Damn, too late.

=\

Why is everybody still using this toy DB?

[Anonymous Coward]

That's why I prefer Postgre. Oh, wait...

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:Use placeholders!

[TheLink]

Heh, as they fix all the stupid things, it starts to look more and more like Perl ;).

Take away the popular but bad PHPisms like addslashes, magic quotes, cgi parameters automatically entering variable namespace, the combining of normal arrays with associative arrays/hashes (makes it messier to distinguish numeric keys from the indices), and you end up with something that is more Perl-ish than PHP-ish.

PHP and MySQL, what a combination... hehe.

This is not specific to Postgres

[Ayanami Rei]

Oracle and MySQL suffer from similar vulnerabilites when going UTF8 -> database charset. The "answer" in Oracle is to use UTF-16 on the backend and a select 8/16-bit encoding in the front end if you want to support multiple locales. I'm not sure what the implications are for MySQL.

Re:Guess its time

[jaredmauch]

Not faster, (for my application). I currently insert about 35k rows per second, mysql just can't handle that last I tried. For the inserts of the raw data that I have (about 250 mil rows a day, and i only save every 1:10k) these opteron 252's are just too slow with the one index. I'd like to be able to index more than a single column.

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.

I dont see how UTF-8 is vulnerable

[Srin Tuar]

I can understand how SJIS and BIG5 are vulnerable.

But in a UTF-8 string, no single byte will match a single quote besides the single quote character (0x27).

It seems to me that simply inserting a backslash before every single quote and backslash in a given string will have the desired effect, and that UTF-8 is not particularly vulnerable to this problem. (quite by design- it was invented by none other than Ken Thompson)

Either that article is misleading somehow, or else the postgres developers are simply putting in some safeguards for common errors in things such as php scripts.

Would like some clarification.

[JLeslie]

I've only recently begun playing with PostgreSQL coming from Oracle. I've also been primarily a Java (JDBC) guy for the last couple years. I'm not sure I completely understand where this vulnerability lies. Would a Java PreparedStatement be vulnerable to this? Would the Postgres implementation of JDBC use 'addslashes()' to bind variables in a prepared statement? Or is this a higher level function? (I have not come across it myself, but like I said I'm still pretty new to Postgres).

I guess I see "affects PostgreSQL servers exposed to untrusted input, such as input coming from Web forms" and wonder if they're talking about some further functionality where postgres acts like a web server. My understanding of PreparedStatements is that they are bound at a very low level in the db to allow for maximum speed through caching etc...

Re:Josh Berkus

[SaDan]

"By the way, the dangling reference to a quote by one "Berkus" should be attributed to Josh Berkus." --Russ Nelson

On the bright side...

[quantum bit]

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.

Validate, Validate AND Validate

[Joe U]

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.

Re:Prepared Queries

[Tablizer]

People who don't use prepared queries doesn't deserve any better than having someone to fuck up your database!

Often such are combersome or impossible with dynamic query generation, such as Query-by-Example forms where the terms and sort options depend on user input.

Many "prepared" thingies also depend on positional parameters, which can get messy. Would you like to use and maintain a function with 19 positional parameters? That is what it can feel like.

Further, certain kinds of prepared statements seem to have the same flaw. The PS api may "check" the types, but still passes a string to the database such that if you can find a way past the syntactical type checkers, you can pull off the same thing.

I do wish that most database API's offered a "read-only" mode such that the query being sent to the database is designated read-only. That way the worse a hacker can do is grab table data but not change anything. If you have logins restricted only to necessary tables, then the damage exposure is further limited.

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.

The Prepare Command

[Qzukk]

... because counting out 500 question marks to figure out why the hell your parameters don't match up is MUCH more fun than being paged at 3AM because the entire production database was wiped out.

Re:Josh Berkus

[LearnToSpell]

"'By the way, the dangling reference to a quote by one "Berkus" should be attributed to Josh Berkus.' --Russ Nelson" --SaDan

Re:Guess its time

[jadavis]

Well, I was thinking that it might be possible if you bunched enough of them into the same transaction, had sufficiently small updates, used a single prepared statement over and over, turned fsync off, and had HUGE amounts of memory, then, perhaps?

You don't need to do all that.

The command:
time ruby -e 'puts "BEGIN;"; for i in 1..35000 do puts "INSERT INTO a VALUES(#{i});" end; puts "COMMIT;"' | ./81/bin/psql test

Gives me about 7-8 seconds with write caching off, fsync on. Yes, I turned off write caching with hdparm. This is on my PC, with an el-cheapo IDE drive and a modest 1GB of RAM.

If you think about it, why should it take a long time? It's all one transaction. It's basically just writing the data to a file (the log). The table "a" is just an int field, obviously. And with postgresql's MVCC, an INSERT is very cheap.

addslashes?

[Abstract]

'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?

Why choose either of those?

[Just Some Guy]

... because counting out 500 question marks to figure out why the hell your parameters don't match up is MUCH more fun than being paged at 3AM because the entire production database was wiped out.

In the Python DB-API, SQL strings look like:

select foo from bar where baz=%(baz)s

You create a dictionary (hash table) with a key "baz", pass that dict to the database along with your query, and it fills in the blanks. Your job as the programmer is to make sure that dict has all the keys in it to complete the query; it doesn't matter which order you assign them or if you don't use them all.

In fact, a very common case is to create on dict with all the values you'll need to execute a whole list of queries, and just keep passing the same dict rather than redoing it each time:

# I know this example is lame.
sqlparams = { 'zipcode': '12345', 'lastname': 'smith' }
cursor.execute('insert into customers (lastname) values (%(lastname)s)', sqlparams)
cursor.execute('insert into locations (zipcode) values (%(zipcode)s)', sqlparams)
cursor.execute('insert into shipments (lastname, zipcode) values (%(lastname)s, %(zipcode)s)', sqlparams)

It's about as easy as you can possibly make it and has no disadvantages that I've ever encountered. So, I'd take the position that it's better to protect the server and forget about old ideas like positional parameters. There are extremely programmer-friendly solutions to this problem if you know where to look.

Re:Josh Berkus

[poot_rootbeer]

'"\'By the way, the dangling reference to a quote by one "Berkus" should be attributed to Josh Berkus.\' --Russ Nelson" --SaDan' --LearnToSpell