Thread: a vulnerability in PostgreSQL
There is a report from a debian user about a vulnerability in PostgreSQL pre 7.2. Here is a possible attack scenario which allows to execute ANY SQL in PostgreSQL. A web application accepts an input as a part of SELECT qualification clause. With the user input, the web server program would build a query for example: SELECT * FROM t1 WHERE foo = 'input_string_from_user' Of course above method is too simple, since a user could input a string such as: foo'; DROP TABLE t1 To prevent the unwanted SQL statement being executed, the usual method most applications are taking is quoting ' by \. With this, above string would be turned into: foo\'; DROP TABLE t1 which would make it impossible to execute the DROP TABLE statement. For example in PHP, addslashes() function does the job. Now, suppose the database encoding is set to SQL_ASCII and the client encoding is, say, LATIN1 and "foo" in above string is a latin character which cannot be converted to ASCII. In this case, PostgreSQL would produce something like: (0x81a2)\'; DROP TABLE t1 Unfortunately there was a bug in pre 7.2's multibyte support that would eat the next character after the impossible-to-convert-character, and would produce: (0x81a2)'; DROP TABLE t1 (notice that \ before ' is disappeared) In this case actual query sent to the backend is: SELECT * FROM t1 WHERE foo = '(0x81a2)'; DROP TABLE t1' The last ' will casue SQL error which prevents the DROP TABLE statement from to be executed, except for 6.5.x. (correct me if I am wrong) Here are the precise conditions to trigger the scenario: (1) the backend is PostgreSQL 6.5.x (2) multibyte support is enabled (--enable-multibyte) (3) the database encoding is SQL_ASCII (other encodings are not affected by the bug). (4) the client encoding is set to other than SQL_ASCII I think I am responsible for this since I originally wrote the code. Sorry for this. I'm going to make back port patches to fix the problem for pre 7.2 versions. -- Tatsuo Ishii
Not tested: but how about the string being foo'; DROP TABLE T1; foo Would the last ' be eaten up then resulting in no error? Also normally a \ would be quoted by \\ right? Would a foo\ result in an unquoted \ ? An unquoted backslash may allow some possibilities. There could be other ways to get rid of the last ', comments etc, so it may not be just 6.5.x. Regards, Link. At 05:18 PM 5/2/02 +0900, Tatsuo Ishii wrote: >There is a report from a debian user about a vulnerability in >PostgreSQL pre 7.2. Here is a possible attack scenario which allows to >execute ANY SQL in PostgreSQL. > >A web application accepts an input as a part of SELECT qualification >clause. With the user input, the web server program would build a >query for example: > >SELECT * FROM t1 WHERE foo = 'input_string_from_user' > >Of course above method is too simple, since a user could input a >string such as: > >foo'; DROP TABLE t1 > >To prevent the unwanted SQL statement being executed, the usual method >most applications are taking is quoting ' by \. With this, above >string would be turned into: > >foo\'; DROP TABLE t1 > >which would make it impossible to execute the DROP TABLE statement. >For example in PHP, addslashes() function does the job. > >Now, suppose the database encoding is set to SQL_ASCII and the client >encoding is, say, LATIN1 and "foo" in above string is a latin >character which cannot be converted to ASCII. In this case, PostgreSQL >would produce something like: > >(0x81a2)\'; DROP TABLE t1 > >Unfortunately there was a bug in pre 7.2's multibyte support that >would eat the next character after the >impossible-to-convert-character, and would produce: > >(0x81a2)'; DROP TABLE t1 > >(notice that \ before ' is disappeared)
> Not tested: but how about the string being > foo'; DROP TABLE T1; foo > > Would the last ' be eaten up then resulting in no error? Even the last ' is eaten up, the remaining string is (81a2), which would cause parser errors since they are not valid SQL, I think. > Also normally a \ would be quoted by \\ right? Would a foo\ result in an > unquoted \ ? An unquoted backslash may allow some possibilities. > > There could be other ways to get rid of the last ', comments etc, so it may > not be just 6.5.x. Please provide concrete examples. I could not find such that case. -- Tatsuo Ishii
Oops. How about: foo'; DROP TABLE t1; -- foo The last ' gets removed, leaving -- (81a2). So you get: select ... '(0x81a2)'; DROP TABLE t1; -- (0x81a2) Would that work? Or do you need to put a semicolon after the --? Alternatively would select (0x81a2) be a syntax error? If it isn't then that's another way to terminate it properly. As for the backslash, how does postgresql treat \000 and other naughty codes? Too bad there are too many characters to backspace over - that is if backspacing (\b) over commands works in the first place ;)... I'll let you know if I think of other ways (I'm sure there are - I probably have to go through the postgresql syntax and commands more closely). Got to go :). Cheerio, Link. At 05:50 PM 5/2/02 +0900, Tatsuo Ishii wrote: > > Not tested: but how about the string being > > foo'; DROP TABLE T1; foo > > > > Would the last ' be eaten up then resulting in no error? > >Even the last ' is eaten up, the remaining string is (81a2), which >would cause parser errors since they are not valid SQL, I think. > > > Also normally a \ would be quoted by \\ right? Would a foo\ result in an > > unquoted \ ? An unquoted backslash may allow some possibilities. > > > > There could be other ways to get rid of the last ', comments etc, so it > may > > not be just 6.5.x. > >Please provide concrete examples. I could not find such that case. >-- >Tatsuo Ishii > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> Oops. How about: > > foo'; DROP TABLE t1; -- foo > > The last ' gets removed, leaving -- (81a2). > > So you get: > select ... '(0x81a2)'; DROP TABLE t1; -- (0x81a2) This surely works:-< Ok, you gave me an enough example that shows even 7.1.x and 7.0.x are not safe. Included are patches for 7.1.3. Patches for 7.0.3 and 6.5.3 will be posted soon.
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> Here are the precise conditions to trigger the scenario:
> (1) the backend is PostgreSQL 6.5.x
> (2) multibyte support is enabled (--enable-multibyte)
> (3) the database encoding is SQL_ASCII (other encodings are not
> affected by the bug).
> (4) the client encoding is set to other than SQL_ASCII
> I think I am responsible for this since I originally wrote the
> code. Sorry for this. I'm going to make back port patches to fix the
> problem for pre 7.2 versions.
It doesn't really seem worth the trouble to make patches for 6.5.x.
If someone hasn't upgraded yet, they aren't likely to install patches
either. (ISTR there are other known security risks in 6.5, anyway.)
If the problem is fixed in 7.0 and later, why not just tell people to
upgrade?
regards, tom lane
I hope you won't make this standard practice. Because there are quite significant differences that make upgrading from 7.1.x to 7.2 troublesome. I can't name them offhand but they've appeared on the list from time to time. For 6.5.x to 7.1.x I believe there are smaller differences, even so there might be people who would patch for security/bug issues but not upgrade. I'm still on Windows 95 for instance (Microsoft has stopped supporting it tho :( ). I think there are still lots of people on Oracle 7. Yes support of older software is a pain. But the silver lining is: it's open source they can feasibly patch it themselves if they are really hard pressed. If the bug report is descriptive enough DIY might not be so bad. And just think of it as people really liking your work :). Any idea which versions of Postgresql have been bundled with O/S CDs? Regards, Link. At 10:23 AM 5/2/02 -0400, Tom Lane wrote: >Tatsuo Ishii <t-ishii@sra.co.jp> writes: > > Here are the precise conditions to trigger the scenario: > > > (1) the backend is PostgreSQL 6.5.x > > (2) multibyte support is enabled (--enable-multibyte) > > (3) the database encoding is SQL_ASCII (other encodings are not > > affected by the bug). > > (4) the client encoding is set to other than SQL_ASCII > > > I think I am responsible for this since I originally wrote the > > code. Sorry for this. I'm going to make back port patches to fix the > > problem for pre 7.2 versions. > >It doesn't really seem worth the trouble to make patches for 6.5.x. >If someone hasn't upgraded yet, they aren't likely to install patches >either. (ISTR there are other known security risks in 6.5, anyway.) >If the problem is fixed in 7.0 and later, why not just tell people to >upgrade? > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster
Or else people in our situation where it takes forever to upgrade the software because of its heavy use and the risk involved in upgrading, not to mention the problems encountered when we did test-runs of the upgrade. Then there is always the thorny issue of loads of software that uses the databases, most of it under user control and any incompatibility between versions, no matter how small, could have horrible implications for our clients and therefore, us. You see, we are an ISP and a consultancy specialising in database-driven web sites and corporate infrastructure. We based nearly everything that we do on PostgreSQL and although we upgrade when we can, our hands are very tied. For us, patching is a necessity, not an option. To migrate a client means rebuilding an entire UAT (user acceptance test site) for them, extensively testing what we can ourselves, then asking the client to allocate time, money and people to test their systems and their own code as well. They will also need to allocate development time, money and people to fix any problems that they find in compatibility. Then there is the throny issue of both companies needing to synchronise their resources and schedules so that we can work together solving any problems that arise. Finally, because we have no control over the customer's quality control, and because customers very often don't have the inhouse expertise to even understand what a proper test is about (they most often have hired in expensive consultants or contracted other companies to do the work), we have no guarantees that when the client thinks that they have tested the site, they really have. Now most people will say "that's their problem", but you see, it isn't. Because these are business-critical systems that we're talking about and the change was initiated by us. So if the system fails, for whatever reason, on the new software, even if it isn't anything to do with the new software, we get the flack. And also in the clients' eyes, we are responsible because their system "was working fine until [we] forced and upgrade to new software". And that is customer relations being damanged, even if the customer is wrong. See the problem? I am only writing this to add to the pool of knowledge about how PG is used and the real-world implications of PG being used for business critical or customer-image systems. PG is extremely well suited for this and the benefits over closed-source systems is enormous, not to mention the fact that PG has email lists like this one with all the fine brains on this list pooled together. It shows in the code and it shows in the satisfaction level of those who use it (I have never once had a client who was dissatisfied with PG. Most clients were surprised that OpenSource existed, that it is free and that it is such great quality without any catches or got-yous that normally comes with "free" things from commercial companies.). Well, that's my hat in the ring. Hope that it helps someone out there or at least adds something to our pooled knowledge! Brad Kieser.net >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<< On 5/3/02, 4:43:31 AM, Lincoln Yeoh <lyeoh@pop.jaring.my> wrote regarding Re: [HACKERS] a vulnerability in PostgreSQL : > I hope you won't make this standard practice. Because there are quite > significant differences that make upgrading from 7.1.x to 7.2 troublesome. > I can't name them offhand but they've appeared on the list from time to time. > For 6.5.x to 7.1.x I believe there are smaller differences, even so there > might be people who would patch for security/bug issues but not upgrade. > I'm still on Windows 95 for instance (Microsoft has stopped supporting it > tho :( ). I think there are still lots of people on Oracle 7. > Yes support of older software is a pain. But the silver lining is: it's > open source they can feasibly patch it themselves if they are really hard > pressed. If the bug report is descriptive enough DIY might not be so bad. > And just think of it as people really liking your work :). > Any idea which versions of Postgresql have been bundled with O/S CDs? > Regards, > Link. > At 10:23 AM 5/2/02 -0400, Tom Lane wrote: > >Tatsuo Ishii <t-ishii@sra.co.jp> writes: > > > Here are the precise conditions to trigger the scenario: > > > > > (1) the backend is PostgreSQL 6.5.x > > > (2) multibyte support is enabled (--enable-multibyte) > > > (3) the database encoding is SQL_ASCII (other encodings are not > > > affected by the bug). > > > (4) the client encoding is set to other than SQL_ASCII > > > > > I think I am responsible for this since I originally wrote the > > > code. Sorry for this. I'm going to make back port patches to fix the > > > problem for pre 7.2 versions. > > > >It doesn't really seem worth the trouble to make patches for 6.5.x. > >If someone hasn't upgraded yet, they aren't likely to install patches > >either. (ISTR there are other known security risks in 6.5, anyway.) > >If the problem is fixed in 7.0 and later, why not just tell people to > >upgrade? > > > > regards, tom lane > > > >---------------------------(end of broadcast)--------------------------- > >TIP 4: Don't 'kill -9' the postmaster > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Thursday 02 May 2002 11:43 pm, Lincoln Yeoh wrote: > Any idea which versions of Postgresql have been bundled with O/S CDs? For RedHat: 5.0 -> PG6.2.1 5.1 -> PG6.3.2 5.2 -> PG6.3.2 6.0 -> PG6.4.2 6.1 -> PG6.5.2 (I think -- this was my first RPMset in Red Hat Linux, but I'm not 100% sure it was 6.5.2 -- it might have been 6.5.3) 6.2 -> PG6.5.3 7.0 -> PG7.0.2 7.1 -> PG7.0.3 7.2 -> PG7.1.3 7.2.93 > PG7.2.1 Red Hat 7.2 is the current official Red Hat, and _currently_ ships with 7.1.3. If this bug applies there, it should be backpatched, and I would be willing to roll another 7.1.3 RPM with the backpatch in it. Prior to that -- well, I don't have any machines running those versions any more. I stay pretty much on the frontline of things -- not the bleeding edge of RawHide, but close. I have had the 7.2.93 beta installed, for instance. I'm even going to get out of the Red Hat 6.2 on SPARC business at some point, by going to the Aurora version (current Red Hat version ported to SPARC). 6.2 is just old, and iptables on the 2.4 kernel is just too useful. I guess I _could_ reinstall an OS to provide a security patch -- but methinks Red Hat would do that as an errata instead. If a patch can be worked up, it should be passed through those channels. Unless we want to consider rolling 6.5.4, 7.0.4, and 7.1.4 security bugfix releases. Of course, this is open source, and there's nothing preventing a third party from forking off and releasing a 6.5.4 bugfix release. But I wouldn't count on getting core developers to interested in it -- the bug is fixed in the current version, and their time is far better spent on fixing bugs and developing new features in the current version. And I'm sure that if someone wanted to volunteer to provide a patchset for each affected version, Bruce might just apply them, and you might talk Marc into rolling them up. But good luck doing so. Then I'd be happy building RPMs out of them -- on the my current box. You would then have to rebuild the RPMs for your box from my src.rpm. 'Upgrade to the next version' is not a good answer, either, particularly since we don't have a true upgrade path, and the problems that dump/restore reinstalls have brought to light. In a similar vein, due to some baroque dependencies, I still have a client running RedHat 5.2 in production. Not pretty to support. Still at 6.5.3, too. We need a better upgrade path, but that's a different discussion. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
Tom Lane <tgl@sss.pgh.pa.us> writes: > Tatsuo Ishii <t-ishii@sra.co.jp> writes: > > Here are the precise conditions to trigger the scenario: > > > (1) the backend is PostgreSQL 6.5.x > > (2) multibyte support is enabled (--enable-multibyte) > > (3) the database encoding is SQL_ASCII (other encodings are not > > affected by the bug). > > (4) the client encoding is set to other than SQL_ASCII > > > I think I am responsible for this since I originally wrote the > > code. Sorry for this. I'm going to make back port patches to fix the > > problem for pre 7.2 versions. > > It doesn't really seem worth the trouble to make patches for 6.5.x. > If someone hasn't upgraded yet, they aren't likely to install patches > either. (ISTR there are other known security risks in 6.5, anyway.) > If the problem is fixed in 7.0 and later, why not just tell people to > upgrade? Postgresql doesn't support upgrades[1], so if we're going to release upgrades[2], we'd need the backported fixes for 6.5, 7.0 and 7.1 [1] Not the first time I mention this, is it? [2] We got lucky - 6.5.x is not compiled with multibyte support. -- Trond Eivind Glomsrød Red Hat, Inc.
> > Oops. How about: > > > > foo'; DROP TABLE t1; -- foo > > > > The last ' gets removed, leaving -- (81a2). > > > > So you get: > > select ... '(0x81a2)'; DROP TABLE t1; -- (0x81a2) > > This surely works:-< Ok, you gave me an enough example that shows even > 7.1.x and 7.0.x are not safe. > > Included are patches for 7.1.3. Patches for 7.0.3 and 6.5.3 will be > posted soon. Included are patches for 7.0.3 and 6.5.3 I promised. BTW, >I hope you won't make this standard practice. Because there are quite >significant differences that make upgrading from 7.1.x to 7.2 troublesome. >I can't name them offhand but they've appeared on the list from time to time. I tend to agree above but am not sure making backport patches are core's job. I have been providing patches for PostgreSQL for years in Japan, and people there seem to be welcome such kind of services. However, supporting previous versions is not a trivial job and I don't want core members to spend their valuable time for that kind of job, since making backport patches could be done by anyone who are familiar with PostgreSQL. -- Tatsuo Ishii
Tatsuo Ishii wrote: > >I hope you won't make this standard practice. Because there are quite > >significant differences that make upgrading from 7.1.x to 7.2 troublesome. > >I can't name them offhand but they've appeared on the list from time to time. > > I tend to agree above but am not sure making backport patches are > core's job. I have been providing patches for PostgreSQL for years in > Japan, and people there seem to be welcome such kind of > services. However, supporting previous versions is not a trivial job > and I don't want core members to spend their valuable time for that > kind of job, since making backport patches could be done by anyone who > are familiar with PostgreSQL. Yes, I know SRA and Red Hat provide extensive backpatches for older versions; it is a nice value-add for their support customers. Not sure about other PostgreSQL support companies. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Trond Eivind Glomsr�d wrote: > Postgresql doesn't support upgrades[1], so if we're going to release > upgrades[2], we'd need the backported fixes for 6.5, 7.0 and 7.1 > > [1] Not the first time I mention this, is it? There is now /contrib/pg_upgrade. It has all the things I can think of for upgrading. Hopefully it can be tested extensively for 7,3 and fully supported. If people don't like that it is a shell script, it can be rewritten in another language, but the basic steps it takes will have to be done no matter what language it is written in. However, as I have warned before, an major change from 7.2 to 7,3 could make it unusable. My point is that it isn't that I haven't tried to make an upgrade script --- the problem is that making one sometimes is impossible. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Do we need to do any more work to document this problem? --------------------------------------------------------------------------- Tatsuo Ishii wrote: > > Oops. How about: > > > > foo'; DROP TABLE t1; -- foo > > > > The last ' gets removed, leaving -- (81a2). > > > > So you get: > > select ... '(0x81a2)'; DROP TABLE t1; -- (0x81a2) > > This surely works:-< Ok, you gave me an enough example that shows even > 7.1.x and 7.0.x are not safe. > > Included are patches for 7.1.3. Patches for 7.0.3 and 6.5.3 will be > posted soon. [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> Do we need to do any more work to document this problem? Better documetation will be welcome. However which document? -- Tatsuo Ishii > --------------------------------------------------------------------------- > > Tatsuo Ishii wrote: > > > Oops. How about: > > > > > > foo'; DROP TABLE t1; -- foo > > > > > > The last ' gets removed, leaving -- (81a2). > > > > > > So you get: > > > select ... '(0x81a2)'; DROP TABLE t1; -- (0x81a2) > > > > This surely works:-< Ok, you gave me an enough example that shows even > > 7.1.x and 7.0.x are not safe. > > > > Included are patches for 7.1.3. Patches for 7.0.3 and 6.5.3 will be > > posted soon. > > [ Attachment, skipping... ] > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 >