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:Would like some clarification.

[ummit]

Here's the issue in a nutshell.

You're some "middleware" code, with the user above you and a SQL database beneath you. You've got this variable, $userinput, containing a string entered by the user. And you want to build a database query along the lines of

select * from table where somecolumn = 'user input';

where you want to plug the user-entered string in there to search for the user's input in the obvious way.

How do you do this? The obvious (but naive) way is to construct your SQL query on the fly, using your language's form of string concatenation. (Here I'll use "+".)

$query = "select * from table where somecolumn = '" + $userinput + "';";

But what if $userinput happens to contain a single quote? That's the problem here.

The obvious (but naive) thing to do is to go through $userinput checking for single quotes and escaping them in some way. You can either do this by hand, or call a function in your language that might seem to do it for you. But you're skating on thn ice here; you're playing games you shouldn't have to play; there might be cases you haven't thought of. One of them is obvious enough: if you're looking for ' characters and escaping them with \, that also means that, oh yeah, you also have to look for existing \ characters so you can escape them with \. Or you can call a function like PHP's "addslashes" which might seem to do this for you. But it turns out -- this is what the fuss is all about -- that there are other problems, if the diddling you're doing with ' and \ characters interacts badly with the diddling the database layer is doing with multibyte character encodings.

So the much better thing to do -- from several standpoints! -- is not to do this funky ad-hoc concatenate-the-strings SQL query construction after all. Use a "prepared statement", which lets you tell the database engine where to plug in those strings you've already got lying around in variables. At first this seems more roundabout, at first it's a little harder to think about, but once you've got your head wrapped arond it, it feels better anyhow, not to mention the fact that it insulates you from attacks like this one.

But yes, in answer to your question, if you're using prepared statements you should be safe, as long as your language's implementation of prepared statements isn't broken underneath.

Finally, for those wondering why this is such a big deal, why unexpected ' characters don't just cause syntax errors and random failures, how it is that this can be turned from a bug into an actual exploit -- here's the deal. Suppose that the $userinput string contained the text

foo' OR 'a' = 'a

Now, when the naive string concatenation is done, the resulting query is

select * from table where somecolumn = 'foo' OR 'a' = 'a';

But this query is always true! The user entering that string contrived to change the behavior of the query. Obviously the user needs to know or guess what the queries you're generating look like and how you're generating them, and the user may or may not acactually be able to subvert your query into doing something truly incorrect or destructive, but the possibility is very much there.