PostgreSQL 8.1.4 Released to Plug Injection Hole 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.'"
This is why... (Score:2, Interesting)
Re:This is why... (Score:5, Informative)
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)
PHP5's mysqli extension enables you to use prepared queries.
Re:This is why... (Score:5, Informative)
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:
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... (Score:2)
Re:This is why... (Score:2)
Re:This is why... (Score:2)
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)
$db = getConnection();
$sql = 'select * from Foo where id =
$params = array('id' => $id);
$result = $db->query($sql, $params);
Re:This is why... (Score:3, Informative)
$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... (Score:1)
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
Re:This is why... (Score:1)
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 (Score:1)
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... (Score:1)
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... (Score:2)
Re:This is why... (Score:2)
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 :(
Re:This is why... (Score:2)
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
Re:This is why... (Score:2)
$res = $db->Execute('select name,age from people where sex=? and city=?',array($sex,$city));
Re:This is why... (Score:5, Insightful)
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... (Score:2)
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
Re:This is why... (Score:2)
PQprepare & friends
Even php offers them.
Re:This is why... (Score:2)
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... (Score:2)
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 (an
Re:This is why... (Score:2)
Validate, Validate AND Validate (Score:5, Insightful)
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:Validate, Validate AND Validate (Score:2)
Sure, the procedures could have bugs, the idea is to minimise the risks.
Besides, if you lock out slelect, drop and delete (etc...) access to the app server, it makes it much harder to do damage on the DB server.
Re:This is why... (Score:2, Informative)
Widespread problems... (Score:3, Informative)
Re:Widespread problems... (Score:1)
Re:Widespread problems... (Score:2)
Re:Widespread problems... (Score:3, Informative)
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... (Score:2, Informative)
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)
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)
Re:Character encodings yet again (Score:1)
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
Re:Character encodings yet again (Score:1)
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 in
Plug Injection Hole (Score:5, Funny)
Josh Berkus (Score:2)
Re:Josh Berkus (Score:2, Funny)
Re:Josh Berkus (Score:3, Funny)
Re:Josh Berkus (Score:3, Funny)
Binaries for Suse (Score:2)
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.
The jokes, they write themselves! (Score:4, Funny)
Damn, too late.
=\
Re:The jokes, they write themselves! (Score:2)
Re:The jokes, they write themselves! (Score:1)
Why is everybody still using this toy DB? (Score:2, Funny)
Use placeholders! (Score:5, Informative)
Re:Use placeholders! (Score:2)
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
Re:Use placeholders! (Score:2)
Re:Use placeholders! (Score:2)
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
Re:Use placeholders! (Score:2)
Well, no argument there. But if you are going to use php, at least use placeholders!
Re:Use placeholders! (Score:3, Interesting)
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.
I dont see how UTF-8 is vulnerable (Score:4, Interesting)
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 vulnerableg (Score:3, Informative)
See http://www.postgresql.org/docs/techdocs.50 [postgresql.org] for the details.
Re:I dont see how UTF-8 is vulnerable (Score:2)
Re:I dont see how UTF-8 is vulnerable (Score:1)
NEEDED: 8.1.4 Torrent(s) & bigger filecollect (Score:2)
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. (Score:2, Interesting)
I guess I see "affect
Re:Would like some clarification. (Score:2)
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
Re:Would like some clarification. (Score:2)
Re:Would like some clarification. (Score:2)
It isn't really a postgresql hole, it's just that if the client sent an invalid multibyte string than postgresql would accept it in the past. Now it throws an error, which is better behavior. It has the potential to break backwards compatibility though, so anytime that is the case the PostgreSQL team takes it very seriously.
Maybe someone can point out a case where parameterized PreparedStatement style SQL
Re:Would like some clarification. (Score:2)
On the bright side... (Score:5, Interesting)
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... (Score:2)
From what I understood (but I'm not an expert, so I could well be wrong), if you quote a string that's inserted into an SQL statement with certain naive functions (of which PHP's addslashes() is one), and that string contained malformed multibyte characters, the resulting SQL statement will have parts of that string considered as part of the SQL command, not data.
It's a c
Re:On the bright side... (Score:2)
How could that be? The point of mysql_real_escape_string() vs. mysql_escape_string() (or any other PHP quoting function) is that it uses the settings from the DB connection to know the correct encoding.
Assuming it just thunks down to the MySQL client library like the docs imply, that means one or more MySQL programmers need to be taken behind the shed and beaten severely.
The Prepare Command (Score:3, Insightful)
Re:The Prepare Command (Score:2)
Why choose either of those? (Score:3, Interesting)
In the Python DB-API, SQL strings look like:
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
Re:The Prepare Command - Use PDO (Score:2)
$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.
Re:The Prepare Command - Use PDO... More clear (Score:2)
addslashes? (Score:4, Interesting)
How come the php documentation [php.net] doesn't mention this?
Re:addslashes? (Score:2)
How to make SQL injection impossible (Score:1)
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
Re:How to make SQL injection impossible (Score:2)
Re:How to make SQL injection impossible (Score:2)
This is not specific to Postgres (Score:3, Interesting)
As an aside... (Score:2)
Re:This is not specific to Postgres (Score:1)
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 (Score:5, Interesting)
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 (Score:2)
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.
Re:Guess its time (Score:2)
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 (Score:3, Interesting)
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;"' |
Gives me about 7-8 seconds with write caching off, fsync on. Yes, I turned off write caching wit
Re:Guess its time (Score:2)
However, this would be limited to bulk imports of data that didn't have constraints. For example, bulk imports of numbers from experimental measurements into a temporary table so that they could later (at one's convenience) be loaded into proper tables.
Yes, I can imagine real-world applications for such rapid small inserts, but in general, they are
Re:Guess its time (Score:2)
I was trying to show that it's not out of the question. I don't have particularly good I/O on my machine, and I have a slow processor. The disk does not need to be written until COMMIT time, and it can be written sequentially (thanks to the write-ahead log). If there are several processors and a connection per processor, I could imagine that this type of benchmark could approach the write speed of the disk.
INSERTs aren't expensive in
Re:Guess its time (Score:2)
Re:Guess its time (Score:2)
The way I understand it, the tuple is written first at the end of the file containing that portion of the table in question. But it is written asynchronously, meaning that it may stay in the OS Buffer Cache.
When the COMMIT comes, PostgreSQL writes sequentially to the WAL, but this time it writes synchronously.
That way, if there is a power failure, the data pages in the buffer cache holding the tuples may be lost, but the WAL contains enough information to recon
Re:Guess its time (Score:2)
You're wrong. My company has a huge legacy FoxPro database, but for performance reasons we copy most of its tables to PostgreSQL as an hourly cron job. One of those tables is just over 7 million rows and I can copy it into PostgreSQL is slightly less than 5 minutes. 7071473 rows in 287 seconds yields 24639 inserts/s, and this is on a production server answering other queries at the same time.
PostgreSQL 8.1.3, FreeBSD 6.1-STABLE, single
Re:Guess its time (Score:2)
Imagine if I started throwing out numbers for selects with bitmap scans - their little heads would explode. :-)
Seriously, "PostgreSQL is slow" is about as current as "Slowaris" and the "17 minutes to copy a file" Mac troll. None of them have been true for years, if they ever were.
Re:Guess its time (Score:2)
Here's the keys to it:
Increase src/include/pg_config_manual.h BLCKSZ to the max (32768)
Use COPY not INSERT (faster than transactions for the inserts too)
Postgres isn't threaded so even though it's a dual proc dual core 252, I am still cpu bound. I really need some sort of lazy index/index chunker that can run in parallel to utilize the multiple proc
Re:Guess its time (Score:1)
> need some sort of lazy index/index chunker that can run in parallel to utilize the multiple processors more
> efficently so I can have more than a single index on the table.
Actually, if this is a critical part of your database, you're probably using the wrong product: postgresql is a great product, but this is definitely its weak spot.
For something like this you're far better off with db2 or ora
Re:Guess its time (Score:2)
Greenplum is doing something similar using Post
Re:Prepared Queries (Score:3, Interesting)
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
Re:Prepared Queries (Score:2)
Weak excuses for not doing it right.
Re:Prepared Queries (Score:2)
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 acceler
Re:Prepared Queries (Score:2)
Re:Prepared Queries (Score:2)
Given a suitable "randomstring" the odds of an attacker successfully closing the safety tag would be exceedingly low, even with future likely tags and browser features.
The current situation is: if someone adds a new tag or browser feature, it is less likely that you'd be blocking that by default, and that is _expected_ behaviour.
Where
Re:Prepared Queries (Score:1)
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 (Score:2)
Re:Prepared Queries (Score:2)
This is easy to do. Create a schema with SELECT-only privileges on the DB objects. Use that schema when connecting to the DB via your API for read-only actions.
If there's other circumstances where you need other DML commands to work, create separate schemae for them. If your data integrity is that important that you need every possible defense against SQL injection attacks,
Re:Prepared Queries (Score:1)
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 (Score:1)