a vulnerability in PostgreSQL
Tatsuo Ishii
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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly
Lincoln Yeoh
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.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majo...@postgresql.org
Tatsuo Ishii
> 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 majo...@postgresql.org)
Lincoln Yeoh
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.
---------------------------(end of broadcast)---------------------------
Tatsuo Ishii
Content-Type: Text/Plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
> 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.
----Next_Part(Thu_May__2_22:37:17_2002_179)--
Content-Type: application/octet-stream
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="ascii.patch.gz"
H4sICPjUzzwAA2FzY2lpLnBhdGNoAKWPPw+CMBDF5/IpbiNpLS2gQDAOxonB
ycHFRUqDRP5JwcQYvrtQYozGxXjD5b2Xy/vlMMZQV6pNG6kuOfUt23KZagSL
j+Isy4R1bZYrVsRMVOXVElbVZCnaywS2xxuADQ4PXS9ceIPgjkEp/anus8kP
HT414fcZPQTcnwX2HHQAgJDMldTijvQwDOpUdXkCsYT1bhNFgJk+QLgmBFYg
7KX2vUGHXWSCEB3048L1cGEeuDlGvX7miRw0/QNJRv+iTbhvzAdgAyO8kAEA
AA==
----Next_Part(Thu_May__2_22:37:17_2002_179)--
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly
----Next_Part(Thu_May__2_22:37:17_2002_179)----
Bradley Kieser
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 <ly...@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-i...@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 majo...@postgresql.org)
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Trond Eivind Glomsrød
> Tatsuo Ishii <t-i...@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.
Tatsuo Ishii
Content-Type: Text/Plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
> > 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
----Next_Part(Sat_May__4_08:56:29_2002_601)--
Content-Type: Text/Plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment; filename="conv.c-7.0.3.patch"
*** postgresql-7.0.3/src/backend/utils/mb/conv.c.orig Sat May 20 22:12:26 2000
--- postgresql-7.0.3/src/backend/utils/mb/conv.c Wed May 1 20:41:45 2002
***************
*** 1162,1169 ****
else
{ /* should be ASCII */
*p++ = c1;
}
- mic++;
}
*p = '\0';
}
--- 1162,1169 ----
else
{ /* should be ASCII */
*p++ = c1;
+ mic++;
}
}
*p = '\0';
}
----Next_Part(Sat_May__4_08:56:29_2002_601)--
Content-Type: Text/Plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment; filename="conv.c-6.5.3.patch"
*** postgresql-6.5.3/src/backend/utils/mb/conv.c.orig Mon Jul 12 07:47:20 1999
--- postgresql-6.5.3/src/backend/utils/mb/conv.c Wed May 1 20:39:34 2002
***************
*** 605,612 ****
else
{ /* should be ASCII */
*p++ = c1;
}
- mic++;
}
*p = '\0';
}
--- 605,612 ----
else
{ /* should be ASCII */
*p++ = c1;
+ mic++;
}
}
*p = '\0';
}
----Next_Part(Sat_May__4_08:56:29_2002_601)--
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majo...@postgresql.org
----Next_Part(Sat_May__4_08:56:29_2002_601)----