Please create an account to participate in the Slashdot moderation system

 


Forgot your password?
Close
typodupeerror
×

PostgreSQL 8.1.4 Released to Plug Injection Hole 162

Posted by ScuttleMonkey from the good-little-dutch-boy dept.
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.'"
This discussion has been archived. No new comments can be posted.

PostgreSQL 8.1.4 Released to Plug Injection Hole More

PostgreSQL 8.1.4 Released to Plug Injection Hole

Comments Filter:

  • This is why... (Score:2, Interesting)

    by ArchAngelQ ( 35053 )
    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... (Score:5, Informative)

      by jrockway ( 229604 ) * <jon-nospam@jrock.us> on Tuesday May 23, 2006 @09:54PM (#15391080) Homepage Journal
      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... (Score:3, Informative)

        by onlyjoking ( 536550 )
        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... (Score:5, Informative)

        by Dwonis ( 52652 ) * on Tuesday May 23, 2006 @10:47PM (#15391297)
        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 [dlitz.net] 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.

        • What's wrong with PEAR_DB?
        • It doesn't have the performance benefits that real prepared statements have, but I still find it handy for typical PHP4 database work.

          Hmm. How about this -- hash the string that's passed in. Use it as the key to a hashtable, the value of which is an integer that's incremented every time the function sees it. Once that reaches a certain threshold, prepare the statement normally but then store it in another hash table (keyed by the statement hash again) for reuse. You could eliminate the first step if you

      • Re:This is why... (Score:5, Informative)

        by Slashcrunch ( 626325 ) on Tuesday May 23, 2006 @11:15PM (#15391405) Homepage
        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... (Score:3, Informative)

        by OnyxRaven ( 9906 )
        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 [php.net]
      • You are talking about placeholders, which versions of DBD-Pg have fixed to require queries structured as in your "good" example.

        I ran into probrems upgrading to the newer DB-Pg module precisely because the new requirement is that placeholders for queries must be submitted as your new example shows, not as:

        $dbh->execute("SELECT * FROM foo WHERE bar=$bar AND baz=$baz"); or

        $dbh->execute("SELECT * FROM foo WHERE bar='$bar' AND baz='$baz' ");

        This can't be the same thing as the bug that was fixed, c

      • Actually with PEAR DB you can do _exactly_ that.

        PEAR DB however is not installed as standard, and is not installed on many webhosts. It's part of free PEAR library of good reusable code for PHP and is pretty easy to install if you are the admin of a server.
      • Hi,

        PDO and PEAR::DB both provide ways of doing this under PHP.

        See http://pear.php.net/ [php.net] and http://www.php.net/pdo [php.net] for examples.

        David.
      • Thanks for that. I must admit that as a relative perl noob I've done it the sloppy way before. The reason? I looked at the code of several peers and saw it done that way. I'm fairly sure that's how most sloppy practices spread - copying off of people who themselves copied off people, etc etc.

        This seems to illustrate the value of learning from a (good) book, etc rather than trying to learn things on the fly.

      • I'm interested - why is it not good to:
        $foo = pg_escape_string("This is my 'blah$*& 'crazy string %^Q*&*&^Q");
        $res = pg_query("SELECT * FROM foo WHERE bar = '$foo' ");
        Or similar with inserts, etc.

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

        Humour a DB neophyte. How is this secure by default? I still don't see any checking of the contents of $bar or $baz, or if $baz contains "; DELETE *", don't you still suffer the usual problems?

        Or does the execute() routine automatically safety-fy the variables passed? In which case, how does it know what is and isn't safe in a particular context?

        I just don't get it :(

        • > I still don't see any checking of the contents of $bar or $baz, or if $baz contains "; DELETE *", don't you still suffer the usual problems?

          No, because there is no "quoting" going on. Instead of making a string that you'd type into the SQL shell, you're directly telling the database what the query is. If $bar is "'\\\'"\''\""/\//'""''\DROP database foo'\""''\\'', then the database will be told to search for that exact string in the database, not to do whatever that long thing means when you type it i
      • just use adodb.sourceforge.net for php. You get syntax like the following:
        $res = $db->Execute('select name,age from people where sex=? and city=?',array($sex,$city));

    • Validate, Validate AND Validate (Score:5, Insightful)

      by Joe U ( 443617 ) on Tuesday May 23, 2006 @11:06PM (#15391375) Homepage Journal
      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:This is why... (Score:2, Informative)

      by a.d.trick ( 894813 )
      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... (Score:3, Informative)

    by ByTor-2112 ( 313205 ) on Tuesday May 23, 2006 @09:50PM (#15391065)
    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.
    • It's a lazy coding practice really.
    • PEAR::DB does this.

    • 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

  • Character encodings yet again (Score:3, Informative)

    by Bogtha ( 906264 ) on Tuesday May 23, 2006 @09:57PM (#15391090)

    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 [postgresql.org]. psycopg should be fixed shortly [initd.org].

    • Re:Character encodings yet again (Score:5, Informative)

      by edwdig ( 47888 ) on Tuesday May 23, 2006 @10:37PM (#15391255)
      Unicode isn't a character encoding, it's a character set. According to this unicode faq [cam.ac.uk], there are 13 different encodings for Unicode. Switching to Unicode doesn't help the problem of character encodings.
      • Switching to Unicode doesn't help the problem of character encodings.

        But what the GP has in mind is not just switching to Unicode, it's using a language implementation that has good Unicode support. This means that strings are internally represented as Unicode, and the language's character I/O libraries handle all conversion between external encodings and the internal representation. This means that program code doesn't do any conversions; all it does is specify a desired encoding when opening a charact

  • Plug Injection Hole (Score:5, Funny)

    by fudgefactor7 ( 581449 ) on Tuesday May 23, 2006 @10:00PM (#15391099)
    heh, heh, heh... I'll plug your injection hole, baby!
  • By the way, the dangling reference to a quote by one "Berkus" should be attributed to Josh Berkus.
  • Has anyone else found that Suse is really, really slow in releasing updated Postgres binaries? Are they tied to SLES releases? Anyone know anything?

    I know I'll probably get a million flames telling me to compile from source, but I'm not really that fond of supporting my own compilation job on a production server.
  • Must....not....make....joke....about...injection hole...being plugged...

    Damn, too late.

    =\

  • Why is everybody still using this toy DB? (Score:2, Funny)

    by Anonymous Coward
    That's why I prefer Postgre. Oh, wait...

  • Use placeholders! (Score:5, Informative)

    by mortonda ( 5175 ) on Tuesday May 23, 2006 @10:26PM (#15391198)
    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 [postgresql.org] 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.
    • But is pear:db standard PHP? I thought there was this thing called PDO?

      It annoys me that PHP is a newer language but the devs did not learn from the mistakes of the older languages.

      PHP seems to be a language that makes it HARD to do the right thing, and easy to do the "nearly-right" (AKA wrong) thing - addslashes, magic quotes.

      I have to deal with tons of PHP code written the wrong way (by someone else), because at that point in time there was no real good right way. Even now, it doesn't seem like the offici
      • PEAR::DB is intalled on a hundred times as many systems as PDO. If DB's not there, it's trivial to install, which can't be said of upgrading to PHP 5.1 with PDO.
        • Which proves my point that PHP doesn't make it easy to do the _right_ thing.

          It's not really standard is it, if with 5.1 the "future is supposed to be PDO?

          So do I modify tons of old broken PHP4 code to use PEAR::DB and then when PHP5.1 or whatever is ready, remodify stuff to do PDO? Will PEAR::DB still work on PHP5.1? If it does, then I'd ignore PDO ;). Of course the amount of 2nd round work involve depends on how easy it is to translate PEAR:DB to PDO - might only have to modify the 1st round DB lib.
      • PHP seems to be a language that makes it HARD to do the right thing, and easy to do the "nearly-right" (AKA wrong) thing - addslashes, magic quotes.


        Well, no argument there. But if you are going to use php, at least use placeholders!

  • I dont see how UTF-8 is vulnerable (Score:4, Interesting)

    by Srin Tuar ( 147269 ) <zeroday26@yahoo.com> on Tuesday May 23, 2006 @10:27PM (#15391206)
    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.
  • Only up to 8.1.3 were listed here as we composed this:

    http://www.postgresql.org/download/btlist [postgresql.org]

    Oh, and it would be gerat to have just ONE torrent to d'load, eg, per platform.

    Alternatively, create an All-In-One ISO (preferably CD-ROM set -and- a DVD ISO)

    (Help us to save you bandwidth...)

    "Remember: It isn't released until its torrents are released" :-/
  • 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 "affect
    • Would the Postgres implementation of JDBC use 'addslashes()' to bind variables in a prepared statement?

      No. Addslashes is a PHP function that many people use. It's not recommended for use with any database.

      JDBC would use the correct string escaping routines in the postgresql client library, PQescapeStringConn. That is perfectly safe.

      In fact, it appears the only real changes they made were to break bad code that produced invalid multibyte sequences. They also broke the use of the " \' " (backslash + single qu
    • No, it won't affect you if you are using prepared statements. It may affect you if you are not using them.

  • On the bright side... (Score:5, Interesting)

    by quantum bit ( 225091 ) on Tuesday May 23, 2006 @11:06PM (#15391374) Journal
    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.

  • The Prepare Command (Score:3, Insightful)

    by Qzukk ( 229616 ) on Tuesday May 23, 2006 @11:19PM (#15391423) Journal
    ... 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.
    • Dude, that's what toolkits are for.
    • ... 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 = $pdoInstance->prepare('INSERT INTO myTable VALUES ( :foo, :bar:, :val );');
      $query->bindParam(':foo', $foo);
      $query->bindParam(':bar', $bar);
      $query->bindParam(':val', $val);
      $query->execute();

      I don't see how that could be any less clear.

  • addslashes? (Score:4, Interesting)

    by Abstract ( 12510 ) on Wednesday May 24, 2006 @02:56AM (#15392196) Journal
    '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 [php.net] doesn't mention this?
  • Many developers write code like this:

    execute("SELECT ... WHERE NAME='"+name+"' ...

    Obviously, this is unsafe. I even wrote such code myself (baaaad). The problem is, many developers don't know how unsafe it is. Most know that they should use PreparedStatement, but don't do it for one reason (mostly laziness) or the other (preparing statements is slow in Oracle, index not used for 'LIKE ?' in some databases).

    There is a way to solve SQL injection problems: Disallow text literals. Or even, disallow literals

    • Nice idea. I wouldn't worry about getting too complicated though -- 0 and 2 would be fine. Allowing 1 would just encourage people to start using numeric encoding in places where it shouldn't be kept.
    • I took the opportuniy to check out H2 - looks nice, and we'll be checking it out more. I always wanted to write my own DBMS, but never got around to it (many years as a DBA/Developer who loved internals). But anyway. I did see that you had an interesting comment on your VARCHAR documentation: Unicode String. Use two single quotes ('') to create a quote.. Does that imply that this will happen even when using prepared statements? I'd like to think not, but...

Slashdot Top Deals

BLISS is ignorance.

Close