Beta

Slashdot: News for Nerds

×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

PostgreSQL 8.1.4 Released to Plug Injection Hole

ScuttleMonkey posted more than 8 years ago | from the good-little-dutch-boy dept.

162

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.'"

cancel ×

162 comments

David Blaine Fails, GNAA claims responsibility (-1, Troll)

Anonymous Coward | more than 8 years ago | (#15391043)

David Blaine Fails, GNAA claims responsibility

Dessimat0r - Trollcore, NYC

It was revealed today that three minutes before his 'Drowned Alive' was due to end, David Blaine was forced out of his water-filled glass bubble early with an unknown cause.

The Gay Nigger Assocation of America is proud to announce that this was due to the heroic actions of GNAA member 'trogg', a recent recruit to the proud legion of Internet niggers. During the last few minutes of his stunt, the GNAA can reveal that images of famous internet celebrities 'goatse' and 'tubgirl' were taped to the outside of his bubble, where Blaine could see them in all their glory.

As Blaine turned to look at this explicit imagery, he began to have convulsions of the anus as his poop began to flow out of his rectum. This caused the water to turn a muddy-brown colour. Blaine then attempted to take off his oxygen mask, possibly hoping to ingest the diseased water in order to get a real taste of rectal prolapse.

The organisers of the stunt then feared for his safety as Blaine reached for his erect penis, as the palms of his hands were suffering from myosis. With this, two divers jumped into the water to save Blaine before he had a chance to touch his throbbing rod, and succeeded in pulling him out in time. He was out of breath as he was rushed to hospital, suffering from the effects of the stunt upon his body.

When Blaine was interviewed in hospital by the Gay Nigger Association of the America, he had this to say: "JEWS DID WTC".


About David Blaine:

Kike magician.


About GNAA:
GNAA (GAY NIGGER ASSOCIATION OF AMERICA) is the first organization which gathers GAY NIGGERS from all over America and abroad for one common goal - being GAY NIGGERS.

Are you GAY [klerck.org] ?
Are you a NIGGER [mugshots.org] ?
Are you a GAY NIGGER [gay-sex-access.com] ?

If you answered "Yes" to all of the above questions, then GNAA (GAY NIGGER ASSOCIATION OF AMERICA) might be exactly what you've been looking for!
Join GNAA (GAY NIGGER ASSOCIATION OF AMERICA) today, and enjoy all the benefits of being a full-time GNAA member.
GNAA (GAY NIGGER ASSOCIATION OF AMERICA) is the fastest-growing GAY NIGGER community with THOUSANDS of members all over United States of America and the World! You, too, can be a part of GNAA if you join today!

Why not? It's quick and easy - only 3 simple steps!
  • First, you have to obtain a copy of GAYNIGGERS FROM OUTER SPACE THE MOVIE [imdb.com] and watch it. You can download the movie [idge.net] (~130mb) using BitTorrent.
  • Second, you need to succeed in posting a GNAA First Post [wikipedia.org] on slashdot.org [slashdot.org] , a popular "news for trolls" website.
  • Third, you need to join the official GNAA irc channel #GNAA on irc.gnaa.us, and apply for membership.
Talk to one of the ops or any of the other members in the channel to sign up today! Upon submitting your application, you will be required to submit links to your successful First Post, and you will be tested on your knowledge of GAYNIGGERS FROM OUTER SPACE.

If you are having trouble locating #GNAA, the official GAY NIGGER ASSOCIATION OF AMERICA irc channel, you might be on a wrong irc network. The correct network is NiggerNET, and you can connect to irc.gnaa.us as our official server. Follow this link [irc] if you are using an irc client such as mIRC.

If you have mod points and would like to support GNAA, please moderate this post up.

.________________________________________________.
| ______________________________________._a,____ | Press contact:
| _______a_._______a_______aj#0s_____aWY!400.___ | Gary Niger
| __ad#7!!*P____a.d#0a____#!-_#0i___.#!__W#0#___ | gary_niger@gnaa.us [mailto]
| _j#'_.00#,___4#dP_"#,__j#,__0#Wi___*00P!_"#L,_ | GNAA Corporate Headquarters
| _"#ga#9!01___"#01__40,_"4Lj#!_4#g_________"01_ | 143 Rolloffle Avenue
| ________"#,___*@`__-N#____`___-!^_____________ | Tarzana, California 91356
| _________#1__________?________________________ |
| _________j1___________________________________ | All other inquiries:
| ____a,___jk_GAY_NIGGER_ASSOCIATION_OF_AMERICA_ | Enid Al-Punjabi
| ____!4yaa#l___________________________________ | enid_indian@gnaa.us [mailto]
| ______-"!^____________________________________ | GNAA World Headquarters
` _______________________________________________' 160-0023 Japan Tokyo-to Shinjuku-ku Nishi-Shinjuku 3-20-2

Copyright (c) 2003-2006 Gay Nigger Association of America [www.gnaa.us]

Re:David Blaine Fails, GNAA claims responsibility (-1)

Anonymous Coward | more than 8 years ago | (#15391129)

This is why I take the more professional approach and use MySQL. It has a knowledgeable and friendly comunity and is generally more robust and full-featured.

(

This is why... (2, Interesting)

ArchAngelQ (35053) | more than 8 years ago | (#15391054)

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... (5, Informative)

jrockway (229604) | more than 8 years ago | (#15391080)

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... (2, Informative)

onlyjoking (536550) | more than 8 years ago | (#15391147)

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

Anonymous Coward | more than 8 years ago | (#15391168)

The postgres library for php does have a prepare statement.

Preparation fails colossally in certain edge cases. (for instance perl's db handler at one point years ago didn't realize that dates of the form 2006-05-01 needed quotes. The database happily did the math and stored the beginning of 2000) In the end, it's merely pawning the problem off to someone else, in hopes that they did it right (which in the case of this postgresql upgrade, means your hopes failed you).

Re:This is why... (4, Insightful)

jrockway (229604) | more than 8 years ago | (#15391274)

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

DrSkwid (118965) | more than 8 years ago | (#15392371)

The PostgreSQL extended query protocol [postgresql.org] doesn't require escaping of any value because it won't parsed as part of an sql statement, making injection a non issue. The only sql that is parsed is supplied by the source code.

People often only get as far as writing the simple query part of the protocol which takes a stream of potentially multiple commands mixed in with data supplied by potentially hostile third parties.

I've witnessed it these past two weeks while sniffing postgresql clients while writing my own as an exercise in Limbo [vitanuova.com]

Postgresql also allows the use of named queries, who stay prepared for as long as the connection is open (unless deleted deliberately).

I don't know how long the parse & prepare phase takes but I know it is not zero so there could be some speed improvement too.

I'm not convinced libpq uses it to full advantage but I didn't do in depth research, just a nod to say that if you do serious postgresql programming you could well do with looking at writing your own protcol client.

Re:This is why... (1)

moro_666 (414422) | more than 8 years ago | (#15392187)

you didn't give the db a 'date', but a string. the api itself is flawed if the language can't differ strings and dates (because they are different types and dates can have special effects a'la runtime translation to other timezones etc.)

the mysql `standard` until 5.x was to have crappy unescaped parameters all over the place, let's hope this will improve now. i'm running my own wrappers anyway which emulate the DBI or java.sql like syntax and db specific escaping, so i couldn't care less.

Re:This is why... (4, Informative)

Dwonis (52652) | more than 8 years ago | (#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.

Re:This is why... (1)

jsoderba (105512) | more than 8 years ago | (#15392509)

What's wrong with PEAR_DB?

Re:This is why... (4, Informative)

Slashcrunch (626325) | more than 8 years ago | (#15391405)

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... (2, Informative)

OnyxRaven (9906) | more than 8 years ago | (#15391509)

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]

Re:This is why... (0)

Anonymous Coward | more than 8 years ago | (#15392249)

Is there a way to use names instead?

Can you have prepared statements that are something like "select name from t1 where id=%id%" and use an associative array so that you don't have to count the question marks? I could see myself inserting a new clause in the WHERE and then everything would be off by one. It seems like a giant leap back in computing to make it dependent on the position in the string.

Re:This is why... (1)

dkf (304284) | more than 8 years ago | (#15392556)

Is there a way to use names instead?
Sure there is in SQL. "SELECT a,b,c FROM foo WHERE bar=:bar and baz=:baz;" is a piece of valid SQL that obtains the values to match against the bar and baz fields from some named context. What varies according to different drivers is what that named context is; apparently when using Zend_Db you bind using an associative array, and if you were using the Tcl bindings to SQLite you'd be using context variables. Check your documentation for how to do this with your code (and then wave goodbye to injection attacks on the database. :-)

Re:This is why... (1)

Badanov (518690) | more than 8 years ago | (#15392303)

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, could it?

Re:This is why... (1)

Colde (307840) | more than 8 years ago | (#15392586)

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.

PDO / PEAR::DB (1)

GingerDog (907579) | more than 8 years ago | (#15392701)

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.

Re:This is why... (1)

kjart (941720) | more than 8 years ago | (#15392721)

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.

Re:This is why... (-1, Troll)

Anonymous Coward | more than 8 years ago | (#15391258)

I want to put my cock in your mouth.

Validate, Validate AND Validate (4, Insightful)

Joe U (443617) | more than 8 years ago | (#15391375)

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... (2, Informative)

a.d.trick (894813) | more than 8 years ago | (#15391760)

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... (2, Informative)

ByTor-2112 (313205) | more than 8 years ago | (#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.

Re:Widespread problems... (1)

DivineOmega (975982) | more than 8 years ago | (#15391111)

It's a lazy coding practice really.

Re:Widespread problems... (0)

Anonymous Coward | more than 8 years ago | (#15391230)

I was surprised to see the newsforge article tell me that addslashes() was deprecated, so I checked out www.php.net/addslashes and discovered no mention of this deprecation at all. Not even a mention of what it was deprecated BY. At least the HTML specs let you know when to stop using something, even if you have to buy $200 worth of "learn html in 30 days" books to figure out what you're supposed to use instead.

What the bloody fuck to you expect? (-1, Flamebait)

Anonymous Coward | more than 8 years ago | (#15391372)

QUALITY "journalism" from this shitty website? Some people actually PAY MONEY to this site, that's the fucking sad thing.

Re:Widespread problems... (1)

mortonda (5175) | more than 8 years ago | (#15391251)

PEAR::DB does this.

Re:Widespread problems... (-1, Troll)

Anonymous Coward | more than 8 years ago | (#15391396)

Your mom also escaped my string of semen pearls around her bruised neck last night. Loads of fun that worthless whore is.

Re:Widespread problems... (2, Informative)

ultranova (717540) | more than 8 years ago | (#15392577)

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

Re:Widespread problems... (2, Informative)

Jac_no_k (5957) | more than 8 years ago | (#15392161)

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.

Prepared Queries (0)

Anonymous Coward | more than 8 years ago | (#15391066)

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

Re:Prepared Queries (3, Interesting)

Tablizer (95088) | more than 8 years ago | (#15391403)

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:Prepared Queries (0)

Anonymous Coward | more than 8 years ago | (#15391833)

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.

Well, DUH. That's security basics really. If you don't want write access on a table then don't give it to the user! Postgres allows for such granularity.

Re:Prepared Queries (1)

Tablizer (95088) | more than 8 years ago | (#15391844)

I really meant at the API level (specific queries), not necessarily at the user level.

The API would have to tell the database that a given query is to be read-only so that if a hacker sticks a DELETE in there, it will be rejected. But, there is no semi-standard mechanisms for such that I know of.
     

Re:Prepared Queries (1)

ComaVN (325750) | more than 8 years ago | (#15391936)

I think he means per-query permissions, so he doesn't have to use a separate user for select and insert/update queries.

Re:Prepared Queries (1)

gullevek (174152) | more than 8 years ago | (#15392081)

so, I even prepare those queries. Whats wrong with that. You are the programmer, you have to know what can come in and how to protect the system from wrong input.

Weak excuses for not doing it right.

Re:Prepared Queries (1)

TheLink (130905) | more than 8 years ago | (#15392234)

Security is a low priority.

Years ago I proposed to the HTML and browser people that there be a "no-active" html tag, that marks enclosed content as nonactive.

Example:

<safetyon lock="randomstring" allowed="keyword1,keyword2,keyword3" />
potentially evil content from uncontrolled party - e.g. comments
<safetyoff lock="randomstring"/>

keywords could be "textonly" "basic-html" "java" etc.

It seems everyone is more interested in "GO" tags. And nobody wants a "STOP" tag.

It's like having a thousand accelerator pedals and no brake pedal. The only way to stop is to make sure none of the accelerator pedals are pressed ;).

Maybe I should try again :).

Re:Prepared Queries (0)

Anonymous Coward | more than 8 years ago | (#15392492)

that's a stupid idea.

Re:Prepared Queries (1)

jsoderba (105512) | more than 8 years ago | (#15392540)

CDATA sections already do that. But you can't rely on CDATA, because not all UAs support it.

Re:Prepared Queries (0)

Anonymous Coward | more than 8 years ago | (#15392624)

Even if they did, I could still use
]]>
<script type="text/evil">
bad things here
</script>
<![CDATA[
in my payload. The only fix is to prevent untrusted content from containing STAGO, but that's so easy (just replace one character with a general entity) I don't have much sympathy for anyone who can't be bothered.

Re:Prepared Queries (1)

oncebitten (893231) | more than 8 years ago | (#15392259)

It's been a while since I've done database stuff (pretty much Oracle), but, how about:

set transaction read only

or

selecting off a database view. we used to do this when we wanted to enforce read only. i know nowadays there's updatable views, but i imagine there's a way to use the old behavior (i'm just too lazy to look it up).

Re:Prepared Queries (0)

Anonymous Coward | more than 8 years ago | (#15392373)

Use two database handles; one for reading and one for writing. Coincides nicely with postgres clustering b.t.w. The reading handle is connected to a user who can see the same namespace as the writing handle, except the reading handle user can only read data. Cumbersome, but currently possible with postgres, oracle (using private aliases), and mysql (which has only one namespace per db anyway). But I agree that the dbh->begin_work() call could come equipped with some parameter indicative of my intended use of the transaction.

Guess its time (-1, Troll)

Anonymous Coward | more than 8 years ago | (#15391068)

To convert to mysql... the faster, more reliable, more featureful, more powerful, and now more secure database. This is as bad as an oracle vulnerability.

This is not specific to Postgres (2, Interesting)

Ayanami Rei (621112) | more than 8 years ago | (#15391199)

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:This is not specific to Postgres (0)

Anonymous Coward | more than 8 years ago | (#15391244)

Ugh... there are not a lot of good reasons to use UTF-16 (frontend or back) unless you have an app that doesn't understand anything else. It's usually wasteful, has a lot of embedded null bytes, slaughters 7-bit ASCII compatibility...

As an aside... (1)

Ayanami Rei (621112) | more than 8 years ago | (#15391516)

You can always use an 8-bit database charset and then use nchar or nclob columns when you want UTF-32 or UTF-16 support. So then your web-app or whatever has to be consciencious where non-ASCII is allowed so it gets converted/stored properly. Then it's only wasteful if the majority of the database content is localized/user submitted latin text. But uh, the case they were going on about concerned far-east locales where multi-byte is a must so I don't think you'd have much issue using a fixed-width encoding if that's your audience.

Re:This is not specific to Postgres (1)

NutscrapeSucks (446616) | more than 8 years ago | (#15391739)

> there are not a lot of good reasons to use UTF-16

Unless you are using Java or Win32, in which case strings are UTF-16 and it's not worth trying to change them.

Re:Guess its time (4, Interesting)

jaredmauch (633928) | more than 8 years ago | (#15391201)

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.

Re:Guess its time (1)

ZeekWatson (188017) | more than 8 years ago | (#15391390)

Pure BS. There is no way in hell postgres can come close to 35k inserts/s. To get 35k inserts/s you would need a high end cluster system ... running something other than postgres.

Anyway this is /. mod parent UP!!!

Re:Guess its time (1)

einhverfr (238914) | more than 8 years ago | (#15391535)

Pure BS. There is no way in hell postgres can come close to 35k inserts/s. To get 35k inserts/s you would need a high end cluster system ... running something other than postgres.

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?

Not that I would want to run 35k/s inserts with fsync off...

Re:Guess its time (2, Interesting)

jadavis (473492) | more than 8 years ago | (#15392064)

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.

Re:Guess its time (0)

Anonymous Coward | more than 8 years ago | (#15391543)

> Pure BS. There is no way in hell postgres can come close to 35k inserts/s. To get 35k inserts/s you would
> need a high end cluster system ... running something other than postgres.

Not necessarily - if it supports a high-performance non-logging load utility, and the poster was talking rows and not actual sql statements.

I commonly hit 100,000 rows/second with DB2 on a simple four-way SMP with its load utility. That's with a seriously slow table type too, I could probably hit 2-4x that speed on a simple table.

Re:Guess its time (0)

Anonymous Coward | more than 8 years ago | (#15391574)

Perhaps:

1- he's using some kind of loader
2- he's running a netezza server

Does mysql have a loader?

Re:Guess its time (1)

Circuit Breaker (114482) | more than 8 years ago | (#15392261)

Dude, you're most probably using the wrong database. Financial data or billing info (or otherwise telecomm industry?) I presume.

Consider kdb+ from Kx systems. Properly used, it can do ~1mil rows/second, with multiple live hot failovers. It's pricey (several hundreds of K$), especially compared to MySQL/PGsql, but might be worth your buck.

Character encodings yet again (2, Informative)

Bogtha (906264) | more than 8 years ago | (#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 (0)

Anonymous Coward | more than 8 years ago | (#15391108)

Actually, the problem is 'concatenating' strings into SQL in the first place - some escaping mechanisms are better than others, but ALL of them are going at it the wrong way. Programs should use bound parameters, so that it doesn't matter WHAT the string is - it gets treated as a string, not 'evaluated' after being (hopefully) made "safe".

Re:Character encodings yet again (0)

Anonymous Coward | more than 8 years ago | (#15391193)

Wait a moment - wasn't Ruby authored by a Japanese person?
I'd be absolutely stupefied if the language itself doesn't have good built-in multibyte character support. Of course, the database libraries may be a different matter, if you're relying on bad query parameterization functions.

And anyone hand-crafting SQL out of user-touchable strings deserves what they get...

Re:Character encodings yet again (1)

Estanislao Martnez (203477) | more than 8 years ago | (#15391395)

Wait a moment - wasn't Ruby authored by a Japanese person? I'd be absolutely stupefied if the language itself doesn't have good built-in multibyte character support.

There's a difference between having "multibyte character support" and what the GP wants, which is clean Unicode support in the language. Ruby does have support for at least Japanese multibyte encodings, but that's different.

The model in question is one that Java comes pretty close to: all of your strings in your language are represented internally as Unicode, with the details of the representation hidden from you. Whenever you do character I/O, then, you specify what external encoding to use, and the language's libraries handle all the conversions for you. You never need to do any explicit character set conversions.

Ruby doesn't do any of this for you. IIRC, there's a command-line switch for making it internally represent strings in multibyte for using Japanese-specific encodings, and if you want to try Unicode, you've got to use UTF-8 internally, and jump some strange hoops...

Finally, the fact that Ruby's author is Japanese should actually make you unsurprised that it doesn't have good Unicode support. A lot of Japanese people are allergic to Unicode, and in particular, to the Han Unification [wikipedia.org] in Unicode.

Re:Character encodings yet again (4, Informative)

edwdig (47888) | more than 8 years ago | (#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.

Re:Character encodings yet again (1)

Estanislao Martnez (203477) | more than 8 years ago | (#15391408)

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 character I/O stream.

This is one of the things that, e.g., Java, does almost perfectly right. (The one dumb flaw is the primitive 16-bit char type.)

Plug Injection Hole (5, Funny)

fudgefactor7 (581449) | more than 8 years ago | (#15391099)

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

Hey Leisure Suit Larry... (0)

Anonymous Coward | more than 8 years ago | (#15391220)

I just don't want to know what you would do at Mr. Lube. [mrlube.com]

Josh Berkus (1)

Russ Nelson (33911) | more than 8 years ago | (#15391126)

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

Re:Josh Berkus (2, Funny)

SaDan (81097) | more than 8 years ago | (#15391283)

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

Re:Josh Berkus (2, Funny)

LearnToSpell (694184) | more than 8 years ago | (#15391645)

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

Someone needs to read the PHP manual... (2, Informative)

chibi.nowheregirl (974380) | more than 8 years ago | (#15391154)

As long as you set the right multibyte string encoding in PHP via the multibyte string functions [php.net] (specifically, the mb_internal_encoding [php.net] function), the parser will catch the invalid multibyte sequence and fix it.

Move along, folks. No need to panic.

Binaries for Suse (1)

electroniceric (468976) | more than 8 years ago | (#15391158)

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.

Re:Binaries for Suse (0)

Anonymous Coward | more than 8 years ago | (#15391185)

Read the article:
Source packages of PostgreSQL 8.1.4, 8.0.8, 7.4.13, and 7.3.15 are available now, and Berkus says that binary packages should be available within 48 hours for "most platforms" either through the PostgreSQL project or through vendor updates.

The jokes, they write themselves! (4, Funny)

Kha Na Set (976591) | more than 8 years ago | (#15391161)

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

Damn, too late.

=\

Re:The jokes, they write themselves! (1)

fireman sam (662213) | more than 8 years ago | (#15391248)

Don't worry, you haven't made a joke yet. (At least one that was funny)

Re:The jokes, they write themselves! (1)

Kha Na Set (976591) | more than 8 years ago | (#15391492)

Wasn't referring to me ... look a few posts up and you'll see a joke has already been made.

I find it amazing. (1)

minitual (966089) | more than 8 years ago | (#15391173)

I really find it amazing how many sites are vulnerable to SQL injection. Even sites I've seen featured on the frontpage of Slashdot have been vulnerable.
As a PHP and SQL programmer, I've found that it is best not to try to keep out all the bad data, but rather only allow the good data.
Also, for those of you who don't know, SQL Injection is when extra information is tagged onto an SQL query by a user. Like adding ') to the end of an insert query (a registration form) to throw off the SQL query. This lets the cracker (not hacker) manipulate the query anyway he/she wants to and can result in information such as names and passwords to be displayed.

Test of vulnerability... (0)

Anonymous Coward | more than 8 years ago | (#15391176)


ok.. here goes.. unicode


hmmm... nope.. didn't work.

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

Anonymous Coward | more than 8 years ago | (#15391183)

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

Re:Why is everybody still using this toy DB? (0)

Anonymous Coward | more than 8 years ago | (#15391252)

Postgres: We're better than MySQL!

Use placeholders! (4, Informative)

mortonda (5175) | more than 8 years ago | (#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.

Re:Use placeholders! (1)

TheLink (130905) | more than 8 years ago | (#15392182)

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 official way is to use PEAR.

Re:Use placeholders! (0)

Anonymous Coward | more than 8 years ago | (#15392518)

PHP does a lot of stupid things by default, but they're sort of starting to fix it. PDO should have been built in from the start, but it wasn't, which is why PEAR::DB exists. PHP5/PDO don't have the widespread adoption/availability that PEAR::DB does.

Re:Use placeholders! (1)

jsoderba (105512) | more than 8 years ago | (#15392524)

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.

I dont see how UTF-8 is vulnerable (3, Interesting)

Srin Tuar (147269) | more than 8 years ago | (#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.

Re:I dont see how UTF-8 is vulnerable (0)

Anonymous Coward | more than 8 years ago | (#15391400)

Correct me if I'm wrong, but UTF-8 uses the regular ASCII code (0-128), then use certain bytes with high bits to indicate a multi byte character. What would hapen if you had one of those multi-byte markers followed by a single quote (no one said the input had to be valid UTF-8).
Your replace, if it's not unicode aware, or if the unicode character + 0x27 combination is invalid, might decide the ' needs escaing and escape it. The database might then read it and interpret differently: that the multibyte character now is (semi-)valid and ends with the inserted escape.
Your protection has now ben defeated.

The solution is to use prepared queries, not to endlessly escape.

Re:I dont see how UTF-8 is vulnerable (1)

quantum bit (225091) | more than 8 years ago | (#15391435)

Well, in theory UTF-8 shouldn't be vulnerable since the backslash is 128 and all UTF-8 encoded characters have the high bit set. So the backslash can't be part of a valid multibyte sequence. Whether it's an issue or not depends how PostgreSQL handles invalid UTF-8 sequences.

Re:I dont see how UTF-8 is vulnerable (1)

quantum bit (225091) | more than 8 years ago | (#15391441)

Bah, stupid pseudo-html. Backslash is < 128 (0x5c)

Re:I dont see how UTF-8 is vulnerable (0)

Anonymous Coward | more than 8 years ago | (#15391504)

then use certain bytes with high bits to indicate a multi byte character

Thats the problem. REAL utf8 uses a high bit in every byte of their multibyte characters. Postgres, however, apparently assumes that if byte 1 has the high bit set, that byte 2 must be part of the same character and doesnt process byte 2 at all. At least thats my guess, with server encoding set to SQL_ASCII, it doesn't do any multibyte processing at all and just treats each byte alone.

Furthermore, using prepared queries merely moves the magic somewhere else. Postgres's SQL for preparing queries is:
PREPARE plan_name (text,int,int) as insert into sometable (foo,bar,baz) values ($1,$2,$3);

Simple enough, right? OK, now let's execute that:
EXECUTE plan_name ('It\'s A Trap!!!',4,5); ... oh shit, the quote still had to be escaped. Whether you choose to do it by a backslash (more visible) or the more acceptable '', the problem is still the same, somebody (whether its you and your addslashes, or the libpg escape function) has to take the user input and make it "nice". If addslashes() is broken, then the answer is to fix it, or at least document that its broken and provide an alternative. Calling people stupid or lazy for using the obvious documented method is not the solution.

Re:I dont see how UTF-8 is vulnerableg (3, Informative)

RuneB (170521) | more than 8 years ago | (#15391420)

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 [postgresql.org] for the details.

NEEDED: 8.1.4 Torrent(s) & bigger filecollect (1)

ivi (126837) | more than 8 years ago | (#15391242)

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" :-/

Would like some clarification. (2, Interesting)

JLeslie (710921) | more than 8 years ago | (#15391277)

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

Anonymous Coward | more than 8 years ago | (#15391311)

That would be up to the JDBC driver implementation. JDBC drivers have to do a lot... you say "I would like to set this javax.sql.Date value" and the driver has to turn it into a protocol database-speak -- so too with strings and escaping and so forth. Example, some databases might like to escape special characters with backslashes, others with a mess of escaping single-quotes. But you don't have to worry about that in a PreparedStatement.

Re:Would like some clarification. (0)

Anonymous Coward | more than 8 years ago | (#15391386)

You're safe. From the faq: [postgresql.org]
# If application always sends untrusted strings as out-of-line parameters, instead of embedding them into SQL commands, it is not vulnerable. This is only available in PostgreSQL 7.4 or later.

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

I don't think the JDBC driver automatically uses server-side prepared statements. From this: [postgresql.org]

"There are a number of ways to enable server side prepared statements depending on your application's needs. The general method is to set a threshold for a PreparedStatement. An internal counter keeps track of how many times the statement has been executed and when it reaches the threshold it will start to use server side prepared statements."

Hope that helps.

captcha: injects

Re:Would like some clarification. (0)

Anonymous Coward | more than 8 years ago | (#15391965)

The JDBC driver shouldn't be vulnerable as

(a) it sends parameters passed to PreparedStatement out-of-line from the query (by default, anyway); and

(b) query string handling is done as Java Strings (i.e. UTF16) up until just before the query is actually sent to the server, when it is translated to the connection's encoding (by default UTF8). So even if the application constructs queries by hand, it's doing so as a Java String, and it does not need to be aware of the server encoding in use to get the quoting of parameter values correct. (But you should be using PreparedStatement anyway!)

Re:Would like some clarification. (1)

jadavis (473492) | more than 8 years ago | (#15392150)

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 quote) sequence in unsafe situations that involve multibyte, client-only encodings.

Read more at the release notes:
http://www.postgresql.org/docs/8.1/interactive/rel ease.html#RELEASE-8-1-4 [postgresql.org]

Everything the postgresql people did seemed quite reasonable and only breaks code that was broken to begin with. The announcement was a little cryptic and short on the technical details, but the release notes fill in the blanks. The reason they probably worded the announcement that way was to call attention to poor use of addslashes in php, or other naive escaping tricks. Just use the built-in client library escaping functions, and everything is fine.

Re:Would like some clarification. (1)

dodobh (65811) | more than 8 years ago | (#15392201)

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... (5, Interesting)

quantum bit (225091) | more than 8 years ago | (#15391374)

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.

Re:On the bright side... (0)

Anonymous Coward | more than 8 years ago | (#15391954)

this really isn't a vulnerability in the database server itself

Then, where exactly the vulnerability is? Does the server, itself, missbehave with UTF-8, creating remote exploitable security hole?

Please! There is no need to show PestgreSQL in too good a light.

The Prepare Command (2, Insightful)

Qzukk (229616) | more than 8 years ago | (#15391423)

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

The power of 'GRANT' (0, Offtopic)

KodeJockey (928302) | more than 8 years ago | (#15391436)

Here's a good example of a security flaw: people who extract the database to a flat file and leaves it their hard drive. 26 million veterans can't be wrong. No, seriously, a 10 minute seminar on user permissions should be required of anyone running a DB server. Like a driver's license.

So here's my question... (0)

Anonymous Coward | more than 8 years ago | (#15391697)

What's SQL?

addslashes? (4, Interesting)

Abstract (12510) | more than 8 years ago | (#15392196)

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

How to make SQL injection impossible (1)

hypersql (954649) | more than 8 years ago | (#15392306)

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 (including numbers) at all. This could be a setting in the database that is on by default, and only off for certain applications (ad hoc query tools) or users (admins). What do you think about that?

I'm thinking about implementing this feature in the database I write (http://www.h2database.com/ [h2database.com] ):

SET ALLOW_LITERALS 0 (no literals allowed)
SET ALLOW_LITERALS 1 (only numbers, text not)
SET ALLOW_LITERALS 2 (everything allowed)
This would be a persistent setting, and only an admin can change it. But, maybe this is the wrong place to ask for comments on this?

(Of course there are other security risks, like using 'customer id' in URL or hidden fields in a web application. Or relying on Javascript data validation. But I don't know what to do about those problems.)

Load More Comments
Slashdot Account

Need an Account?

Forgot your password?

Don't worry, we never post anything without your permission.

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>
Create a Slashdot Account

Loading...