pgsql-server/src include/utils/timestamp.h bac ...

thomas@postgresql.org (Thomas Lockhart) writes:

Log message:
Add guard code to protect from buffer overruns on long date/time input
strings. Should go back in and look at doing this a bit more elegantly
and (hopefully) cheaper. Probably not too bad anyway, but it seems a
shame to scan the strings twice: once for length for this buffer overrun
protection, and once to parse the line.

Are these changes available for 7.2, too? There is at least a DoS
potential lurking here. :-(

--
Florian Weimer Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT fax +49-711-685-5898

Florian Weimer <Weimer@CERT.Uni-Stuttgart.DE> writes:

thomas@postgresql.org (Thomas Lockhart) writes:

Log message:
Add guard code to protect from buffer overruns on long date/time input
strings. Should go back in and look at doing this a bit more elegantly
and (hopefully) cheaper. Probably not too bad anyway, but it seems a
shame to scan the strings twice: once for length for this buffer overrun
protection, and once to parse the line.

Are these changes available for 7.2, too? There is at least a DoS
potential lurking here. :-(

Thomas can correct me if I'm mistaken, but I believe these changes apply
to the new integer datetime code Thomas wrote earlier in the 7.3
development cycle -- i.e. there's no bug present in 7.2, or earlier CVS
code when compiled without --enable-integer-datetimes.

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

...

Thomas can correct me if I'm mistaken, but I believe these changes apply
to the new integer datetime code Thomas wrote earlier in the 7.3
development cycle -- i.e. there's no bug present in 7.2, or earlier CVS
code when compiled without --enable-integer-datetimes.

Actually, it is probably an issue for the earlier stuff too, but the
integer value reading seems to have different sensitivities to really
long strings which is the symptom that was noticed just recently.

The same technique for guarding would work fine for 7.2 also.

- Thomas

Neil Conway <nconway@klamath.dyndns.org> writes:

Thomas can correct me if I'm mistaken, but I believe these changes apply
to the new integer datetime code

No, it's possible to crash the backend in 7.2, too.

--
Florian Weimer Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT fax +49-711-685-5898

Florian Weimer <Weimer@CERT.Uni-Stuttgart.DE> writes:

Neil Conway <nconway@klamath.dyndns.org> writes:

Thomas can correct me if I'm mistaken, but I believe these changes apply
to the new integer datetime code

No, it's possible to crash the backend in 7.2, too.

And 7.2.1, of course.

Let me ask again: Do you plan to address this in an update for 7.2.1?

--
Florian Weimer Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT fax +49-711-685-5898

Hi Florian,

Is it possible to crash a 7.2.1 backend without having an entry in the
pg_hba.conf file?

i.e. Is every PostgreSQL 7.2.1 installation around vulnerable to a
remote DoS (or worse) from any user anywhere, at this moment in time?

Regards and best wishes,

Justin Clift

Florian Weimer wrote:

Florian Weimer <Weimer@CERT.Uni-Stuttgart.DE> writes:

Neil Conway <nconway@klamath.dyndns.org> writes:

Thomas can correct me if I'm mistaken, but I believe these changes apply
to the new integer datetime code

No, it's possible to crash the backend in 7.2, too.

And 7.2.1, of course.

Let me ask again: Do you plan to address this in an update for 7.2.1?

--
Florian Weimer Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT fax +49-711-685-5898

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

Justin Clift <justin@postgresql.org> writes:

Is it possible to crash a 7.2.1 backend without having an entry in the
pg_hba.conf file?

No, but think of web applications and things like that. The web
frontend might pass in a date string which crashes the server backend.
Since the crash can be triggered by mere data, an attacker does not
have to be able to send specific SQL statements to the server.

--
Florian Weimer Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT fax +49-711-685-5898

Hi Florian,

Am I understanding this right:

- A PostgreSQL 7.2.1 server can be crashed if it gets passed certain
date values which would be accepted by standard "front end" parsing?
So, a web application layer can request a date from a user, do standard
integrity checks (like looking for weird characters and formatting
hacks) on the date given, then use the date as part of a SQL query, and
PostgreSQL will die?

?

Regards and best wishes,

Justin Clift

Florian Weimer wrote:

Justin Clift <justin@postgresql.org> writes:

Is it possible to crash a 7.2.1 backend without having an entry in the
pg_hba.conf file?

No, but think of web applications and things like that. The web
frontend might pass in a date string which crashes the server backend.
Since the crash can be triggered by mere data, an attacker does not
have to be able to send specific SQL statements to the server.

--
Florian Weimer Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT fax +49-711-685-5898

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

Justin Clift <justin@postgresql.org> writes:

Am I understanding this right:
- A PostgreSQL 7.2.1 server can be crashed if it gets passed certain
date values which would be accepted by standard "front end" parsing?

AFAIK it's a buffer overrun issue, so anything that looks like a
reasonable date would *not* cause the problem.

regards, tom lane

Justin Clift <justin@postgresql.org> writes:

- A PostgreSQL 7.2.1 server can be crashed if it gets passed certain
date values which would be accepted by standard "front end" parsing?
So, a web application layer can request a date from a user, do standard
integrity checks (like looking for weird characters and formatting
hacks) on the date given, then use the date as part of a SQL query, and
PostgreSQL will die?

It depends on the checking. If you just check that the date consists
of digits (and a few additional characters), it's possible to crash
the server.

--
Florian Weimer Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT fax +49-711-685-5898

Hi Florian,

Very hard call.

If this was even a "fringe case" whereby even only a few places that are
doing "the right thing" would be compromisable, then we should probably
go for a 7.2.2. Even if it's only 7.2.1 with this one bug fix.

However, it sounds like this bug is really only going to affect those
places which aren't correctly implementing *proper*, *decent* input
validation, and are then passing this not-properly-checked value
straight into a SQL string for execution by the server.

Doing that (not input checking properly) is a brain damaged concept all
by itself. :(

Is this scenario of not properly checking the input the only way
PostgreSQL could be crashed by this bug In Real Life?

Having said this, is this what 7.2.2 here would require doing:

- Create an archive of 7.2.1+bugfix, and call it 7.2.2, gzip, md5, etc,
as appropriate, put on site
- Update CVS appropriately
- Create a new press release for 7.2.2, spread that appropriately too
- Add an entry to the main website

I reckon the only reason for making a 7.2.2 for this would be to help
ensure newbie (or very tired) coders don't get their servers taken out
by clueful malicious types.

Regards and best wishes,

Justin Clift

Florian Weimer wrote:

Justin Clift <justin@postgresql.org> writes:

- A PostgreSQL 7.2.1 server can be crashed if it gets passed certain
date values which would be accepted by standard "front end" parsing?
So, a web application layer can request a date from a user, do standard
integrity checks (like looking for weird characters and formatting
hacks) on the date given, then use the date as part of a SQL query, and
PostgreSQL will die?

It depends on the checking. If you just check that the date consists
of digits (and a few additional characters), it's possible to crash
the server.

--
Florian Weimer Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT fax +49-711-685-5898

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

Justin Clift <justin@postgresql.org> writes:

Am I understanding this right:
- A PostgreSQL 7.2.1 server can be crashed if it gets passed certain
date values which would be accepted by standard "front end" parsing?

AFAIK it's a buffer overrun issue, so anything that looks like a
reasonable date would *not* cause the problem.

Still, I believe this should require a 7.2.2 release. Imagine a university
database server for a course for example - the students would just crash it
all the time.

Chris

Hi Chris,

Christopher Kings-Lynne wrote:

<snip>

Still, I believe this should require a 7.2.2 release. Imagine a university
database server for a course for example - the students would just crash it
all the time.

Hey yep, good point.

Is this the only way that we know of non postgresql-superusers to be
able to take out the server other than by extremely non-optimal,
resource wasting queries?

If we release a 7.2.2 because of this, can we be pretty sure we have a
"no known vulnerabilities" release, or are there other small holes which
should be fixed too?

:-)

Regards and best wishes,

Justin Clift

Chris

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

Hey yep, good point.

Is this the only way that we know of non postgresql-superusers to be
able to take out the server other than by extremely non-optimal,
resource wasting queries?

If we release a 7.2.2 because of this, can we be pretty sure we have a
"no known vulnerabilities" release, or are there other small holes which
should be fixed too?

What about that "select cash_out(2) crashes because of opaque" entry in the
TODO? That really needs to be fixed.

I was talking to a CS lecturer about switching to postgres from oracle when
7.3 comes out and all he said was "how easily is it hacked?". He says their
systems are the most constantly bombarded in universities. What could I
say? That any unprivileged user can just go 'select cash_out(2)' to DOS the
backend?

Chris

On Mon, 12 Aug 2002, Justin Clift wrote:

Hi Chris,

Christopher Kings-Lynne wrote:

<snip>

Still, I believe this should require a 7.2.2 release. Imagine a university
database server for a course for example - the students would just crash it
all the time.

Hey yep, good point.

Is this the only way that we know of non postgresql-superusers to be
able to take out the server other than by extremely non-optimal,
resource wasting queries?

Check the TODO:

You are now connected as new user s.
template1=> select cash_out(2);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!> \q
[swm@laptop a]$ bin/psql template1
psql: could not connect to server: Connection refused
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.3987"?
[swm@laptop a]$

---

Gavin

Yea, I added that TODO entry, and I am embarrased that a single cash_out
call could crash the backend. I thought about not making this public
knowledge, but making it public hasn't marshalled any forces to fix it
so maybe I was wrong to put it on TODO.

---------------------------------------------------------------------------

Gavin Sherry wrote:

On Mon, 12 Aug 2002, Justin Clift wrote:

Hi Chris,

Christopher Kings-Lynne wrote:

<snip>

Still, I believe this should require a 7.2.2 release. Imagine a university
database server for a course for example - the students would just crash it
all the time.

Hey yep, good point.

Is this the only way that we know of non postgresql-superusers to be
able to take out the server other than by extremely non-optimal,
resource wasting queries?

Check the TODO:

You are now connected as new user s.
template1=> select cash_out(2);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!> \q
[swm@laptop a]$ bin/psql template1
psql: could not connect to server: Connection refused
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.3987"?
[swm@laptop a]$

---

Gavin

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Christopher Kings-Lynne wrote:

Hey yep, good point.

Is this the only way that we know of non postgresql-superusers to be
able to take out the server other than by extremely non-optimal,
resource wasting queries?

If we release a 7.2.2 because of this, can we be pretty sure we have a
"no known vulnerabilities" release, or are there other small holes which
should be fixed too?

What about that "select cash_out(2) crashes because of opaque" entry in the
TODO? That really needs to be fixed.

I was talking to a CS lecturer about switching to postgres from oracle when
7.3 comes out and all he said was "how easily is it hacked?". He says their
systems are the most constantly bombarded in universities. What could I
say? That any unprivileged user can just go 'select cash_out(2)' to DOS the
backend?

If he's using Oracle already, he ought to check out:

http://www.cert.org/advisories/CA-2002-08.html

I'd still think it would be a good policy to make a security release.
However, without user resource limits in PostgreSQL, anyone can make a
machine useless with a query like:

SELECT *
FROM pg_class a, pg_class b, pg_class c, pg_class d, pg_class e, ... ;

Mike Mascari
mascarm@mascari.com

Tom Lane <tgl@sss.pgh.pa.us> writes:

Justin Clift <justin@postgresql.org> writes:

Am I understanding this right:
- A PostgreSQL 7.2.1 server can be crashed if it gets passed certain
date values which would be accepted by standard "front end" parsing?

AFAIK it's a buffer overrun issue, so anything that looks like a
reasonable date would *not* cause the problem.

Yes, but if you just check that the date given by the user matches the
regular expression "[0-9]+-[0-9]+-[0-9]+", it's still possible to
crash the backend.

--
Florian Weimer Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT fax +49-711-685-5898

Mike Mascari <mascarm@mascari.com> writes:

I'd still think it would be a good policy to make a security release.
However, without user resource limits in PostgreSQL, anyone can make a
machine useless with a query like:

SELECT *
FROM pg_class a, pg_class b, pg_class c, pg_class d, pg_class e, ... ;

But this requires to be able to send arbitrary SQL commands; just
feeding a specially crafted date string usually does not.

--
Florian Weimer Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT fax +49-711-685-5898

On Mon, 12 Aug 2002, Florian Weimer wrote:

Tom Lane <tgl@sss.pgh.pa.us> writes:

Justin Clift <justin@postgresql.org> writes:

Am I understanding this right:
- A PostgreSQL 7.2.1 server can be crashed if it gets passed certain
date values which would be accepted by standard "front end" parsing?

AFAIK it's a buffer overrun issue, so anything that looks like a
reasonable date would *not* cause the problem.

Yes, but if you just check that the date given by the user matches the
regular expression "[0-9]+-[0-9]+-[0-9]+", it's still possible to
crash the backend.

Florian,

Anyone who is using that regular expression in an attempt to validate a
user supplied date is already in trouble.

Gavin

Well, if it's a buffer overrun, there is certainly potential for risks
well beyond that of simply crashing the "be". It's certainly possible
that a simple bug in one cgi script or web site could allow someone to
execute code on the database host because of this bug. Assuming they
are running the "be" as "postgres" or some other seemingly harmless
user, it's still possible that complete destruction of any and all
databases which are hosted and accessible by this user can be utterly
destroyed or miscellaneously corrupted.

Buffer over runs should be treated with the up most urgency and
respect. IMO, any known buffer overrun is worthy of an emergency fix
and corresponding advisory.

Greg Copeland

Greg Copeland <greg@CopelandConsulting.Net> writes:

Well, if it's a buffer overrun, there is certainly potential for risks
well beyond that of simply crashing the "be".

It's a buffer overrun, but the data has to pass through the date/time
parser in the backend, so it's not entirely obvious how you can
exploit this to run arbitrary code.

--
Florian Weimer Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT fax +49-711-685-5898

Gavin Sherry <swm@linuxworld.com.au> writes:

Yes, but if you just check that the date given by the user matches the
regular expression "[0-9]+-[0-9]+-[0-9]+", it's still possible to
crash the backend.

Anyone who is using that regular expression in an attempt to validate a
user supplied date is already in trouble.

I don't understand why extremely strict syntax checks are necessary.
The database has to parse it again anyway, and if you can't rely on
the database to get this simple parsing right, will it store your
data? Such a reasoning doesn't seem to be too far-fetched to me

I would probably impose a length limit in the frontend that uses the
database, but the PostgreSQL documentation does not state that this is
a requirement (because the parsers in the backend are so fragile).

--
Florian Weimer Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT fax +49-711-685-5898

On Mon, 12 Aug 2002, Florian Weimer wrote:

Gavin Sherry <swm@linuxworld.com.au> writes:

Yes, but if you just check that the date given by the user matches the
regular expression "[0-9]+-[0-9]+-[0-9]+", it's still possible to
crash the backend.

Anyone who is using that regular expression in an attempt to validate a
user supplied date is already in trouble.

I don't understand why extremely strict syntax checks are necessary.
The database has to parse it again anyway, and if you can't rely on
the database to get this simple parsing right, will it store your
data? Such a reasoning doesn't seem to be too far-fetched to me

Why attempt to validate the user data at all if you're going to do a bad
job of it? Moreover, 'rely on the database to get this ... right': what
kind of security principle is that? For someone interested in security,
you've just broken the most important principle.

As to your other point -- that this bug in the data/time code actually
*reflects* the quality and reliability of the database itself -- you've
really gone too far. The best software has bugs. The reason that no one is
jumping up and down making releases and giving you a medal is that (1) it
is still questionable as to whether or not this bug exists in 7.2.1 (2) it
does not appear to be exploitable (3) it could only be used to cause a
denial of service by an authorised user (4) it is common practise for
database application developers to validate user input and if they don't
they have bigger problems than a potential DoS on their hands.

Gavin

-----Original Message-----
From: Gavin Sherry [mailto:swm@linuxworld.com.au]
Sent: 12 August 2002 15:15
To: Florian Weimer
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] [SECURITY] DoS attack on backend
possible (was: Re:

On Mon, 12 Aug 2002, Florian Weimer wrote:

Gavin Sherry <swm@linuxworld.com.au> writes:

Yes, but if you just check that the date given by the

user matches

the regular expression "[0-9]+-[0-9]+-[0-9]+", it's

still possible

to crash the backend.

Anyone who is using that regular expression in an attempt to
validate a user supplied date is already in trouble.

I don't understand why extremely strict syntax checks are

necessary.

The database has to parse it again anyway, and if you can't rely on
the database to get this simple parsing right, will it store your
data? Such a reasoning doesn't seem to be too far-fetched to me

Why attempt to validate the user data at all if you're going
to do a bad job of it? Moreover, 'rely on the database to get
this ... right': what kind of security principle is that? For
someone interested in security, you've just broken the most
important principle.

If I write code in a Microsoft product such as VB it will happily accept
timestamps such as '2001-12-23 22.15.01' which is a perfectly valid date
in some parts of the world. PostgreSQL will barf on the .'s - is it
expected then that I write my own validation/parsing code to replace
Microsoft's in this and every other area that may need checking just
because PostgreSQL doesn't understand a particular format? I would
rather let PostgreSQL alone know about it's oddities and just throw me
an error I can deal with in such cases.

Regards, Dave.

PS (Gavin). Thanks for the CREATE OR REPLACE's you recently submitted!

Gavin Sherry wrote:

As to your other point -- that this bug in the data/time code actually
*reflects* the quality and reliability of the database itself -- you've
really gone too far. The best software has bugs.

For example, in the current version of Oracle 9i, if a client (say
SQL*Plus) is running on a linux box and talking to Oracle running on a
Solaris box, executes the following:

create table foo(i integer primary key, bar blob);

... then later does ...

update foo set bar=empty_blob() where i = <some key value>

The Oracle server on Solaris crashes. *the whole thing* BANG!
Shot-to-the-head-dead. Not the user's client - the server.

This means that any user with the right to update a single table with a
blob can crash Oracle at will.

What does this say about Oracle's overall reliability?

As Gavin says all software has bugs. Most of PG's bugs are far less
spectacular than the Oracle bug I mention here.

Overall I rate PG and Oracle as being about equivalent in terms of bugs.

--
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org

Weimer@CERT.Uni-Stuttgart.DE (Florian Weimer) wrote in
news:8765yg2niw.fsf@CERT.Uni-Stuttgart.DE:

Gavin Sherry <swm@linuxworld.com.au> writes:

Yes, but if you just check that the date given by the user matches the
regular expression "[0-9]+-[0-9]+-[0-9]+", it's still possible to
crash the backend.

Anyone who is using that regular expression in an attempt to validate a
user supplied date is already in trouble.

I don't understand why extremely strict syntax checks are necessary.
The database has to parse it again anyway, and if you can't rely on
the database to get this simple parsing right, will it store your
data? Such a reasoning doesn't seem to be too far-fetched to me

I believe this is often referred to as the layered onion approach of
security, besides that what constitutes extremely strict syntax checking is
somewhat subjective. What about checking the input for backslash, quote,
and double quote (\'")? If you are not taking care of those in input then
crashing the backend is going to be the least of your worries. I think
there needs to be some level of checking before the input is blindly passed
to the backend for parsing. Typically the input for an individual field
wouldnt be more than ~255 characters, unless you are dealing with TEXT or
lo's. I dont consider adding a length check to the usual \'" check to be
extreme... but perhaps just as necssary?

Hi,

-- ngpg@grymmjack.com wrote:

What about checking the input for backslash, quote,
and double quote (\'")? If you are not taking care of those in input
then crashing the backend is going to be the least of your worries.

with Perl and *using placeholders and bind values*, the application
developer has not to worry about this. So, usually I don't check the
values in my applications (e.g. if only values between 1 and 5 are
allowed and under normal circumstances only these are possible), it's the
task of the database (check constraint).

Ciao
Alvar

--
** ODEM ist für den poldi Award nominiert! http://www.poldiaward.de/
** http://www.poldiaward.de/index.php?display=detail&amp;cat=audi&amp;item=24
** http://odem.org/
** Mehr Projekte: http://alvar.a-blast.org/

Alvar Freude <alvar@a-blast.org> writes:

What about checking the input for backslash, quote,
and double quote (\'")? If you are not taking care of those in input
then crashing the backend is going to be the least of your worries.

with Perl and *using placeholders and bind values*, the application
developer has not to worry about this. So, usually I don't check the
values in my applications (e.g. if only values between 1 and 5 are
allowed and under normal circumstances only these are possible), it's the
task of the database (check constraint).

That's the idea. It's the job of the database to guarantee data
integrety.

Obviously, the PostgreSQL developers disagree. If I've got to do all
checking in the application anyway, I can almost use MySQL
instead. ;-)

--
Florian Weimer Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT fax +49-711-685-5898

Hi Florian,

You guys *definitely* write scarey code.

:-(

Regards and best wishes,

Justin Clift

Florian Weimer wrote:

Alvar Freude <alvar@a-blast.org> writes:

What about checking the input for backslash, quote,
and double quote (\'")? If you are not taking care of those in input
then crashing the backend is going to be the least of your worries.

with Perl and *using placeholders and bind values*, the application
developer has not to worry about this. So, usually I don't check the
values in my applications (e.g. if only values between 1 and 5 are
allowed and under normal circumstances only these are possible), it's the
task of the database (check constraint).

That's the idea. It's the job of the database to guarantee data
integrety.

Obviously, the PostgreSQL developers disagree. If I've got to do all
checking in the application anyway, I can almost use MySQL
instead. ;-)

--
Florian Weimer Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT fax +49-711-685-5898

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

Justin Clift <justin@postgresql.org> writes:

You guys *definitely* write scarey code.

Yes, indeed. My code has a lot of unnecessary and error-prone input
validation checks because I don't trust the PostgreSQL parser.

That's scary. You don't trust your database that it processes a
simple text string, yet you still believe that it keeps all the data
you store, although this involves much more complex data structures
and algorithms.

What a strange asymmetry!

--
Florian Weimer Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT fax +49-711-685-5898

On Mon, 2002-08-19 at 13:14, Florian Weimer wrote:

Justin Clift <justin@postgresql.org> writes:

You guys *definitely* write scarey code.

Yes, indeed. My code has a lot of unnecessary and error-prone input
validation checks because I don't trust the PostgreSQL parser.

Bah.. Check the datatype is close and send it in.

Would be much easier to capture database errors if you didn't have to
base all error matches on regular expressions (error codes will be
nice).

Florian Weimer <Weimer@CERT.Uni-Stuttgart.DE> writes:

That's the idea. It's the job of the database to guarantee data
integrety.

Obviously, the PostgreSQL developers disagree.

Look: it's an acknowledged bug and it's fixed in current sources.
The disagreement is over whether this single bug is sufficient reason
to force issuance of a 7.2.2 release. Given that we are within a couple
of weeks of going beta for 7.3, the previous decision not to issue a
7.2.2 release will stand, unless something *much* worse than this pops
up.

Saying or implying that the developers don't care about data integrity
does not enhance your credibility.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

Saying or implying that the developers don't care about data integrity
does not enhance your credibility.

Sorry, my fault. Indeed, I didn't check carefully whether the people
who go a bit too far in downplaying the problem at hand are in fact
PostgreSQL developers.

--
Florian Weimer Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT fax +49-711-685-5898

Weimer@CERT.Uni-Stuttgart.DE (Florian Weimer) wrote

Alvar Freude <alvar@a-blast.org> writes:

What about checking the input for backslash, quote,
and double quote (\'")? If you are not taking care of those in
input then crashing the backend is going to be the least of your
worries.

with Perl and *using placeholders and bind values*, the application
developer has not to worry about this. So, usually I don't check the
values in my applications (e.g. if only values between 1 and 5 are
allowed and under normal circumstances only these are possible), it's
the task of the database (check constraint).

That's the idea. It's the job of the database to guarantee data
integrety.

Obviously, the PostgreSQL developers disagree. If I've got to do all
checking in the application anyway, I can almost use MySQL
instead. ;-)

perhaps I did not express myself very well.
if you are going to be passing any user input to the database, you
must/should validate in some manner before blindly passing it to the db.
The db can and should guarantee data integrity, but the database cannot
read your mind when it comes to how you structure your queries.

$input = "user'name";
INSERT INTO db (name) VALUES ('$input');

will fail because the ' in the input needs to be escaped with a
backslash. at some point this has to happen, because

INSERT INTO db (name) VALUES ('user'name');

is not a valid query.

The other thing is i think you are stretching the "db integrity
checking" argument a little too far. Its the db's responsibility to make
sure only valid data is stored, but its not the db's responsibility to
directly interact with your end users -- this is the job of your
application and interface. If you insert a new record and there is a
constraint violation, how is your application supposed to know what
illegal value(s) is/are causing it? How are you supposed to convey the
proper information to your user to get the input you are looking for?

Besides all that, and i dont mean to insult you, but your just plain
stupid if you blindly pass user inputted data to your db. For that
matter, your stupid if you blindly accept user input in any programming
without checking it at some level.

On Mon, 19 Aug 2002 ngpg@grymmjack.com wrote:

$input = "user'name";
INSERT INTO db (name) VALUES ('$input');

will fail because the ' in the input needs to be escaped with a
backslash.

It will fail because you're doing this a very, very, very bad way.
Why rewrite this kind of stuff when the vendor has already made
correct code available?

PreparedStatement stmt = connection.prepareStatement(
"INSERT INTO db (name) VALUES (?)");
stmt.setString("user'name");
stmt.execute();

cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

cjs@cynic.net (Curt Sampson) wrote in

On Mon, 19 Aug 2002 ngpg@grymmjack.com wrote:

$input = "user'name";
INSERT INTO db (name) VALUES ('$input');

will fail because the ' in the input needs to be escaped with a
backslash.

It will fail because you're doing this a very, very, very bad way.
Why rewrite this kind of stuff when the vendor has already made
correct code available?

PreparedStatement stmt = connection.prepareStatement(
"INSERT INTO db (name) VALUES (?)");
stmt.setString("user'name");
stmt.execute();

cjs

Curt:
I am not doing it this way, I am trying to point out that doing it without
"doing something" (whether it be using preparedstatement or WHATEVER), is,
as you say, very very very bad (I am agreeing with you). I am further
saying that whatever it is you do, you should also be doing some other
simple validation, like the length of the inputs, because most inputs wont
be over 255 chars before being prepared. This is just an example, but you
should do whatever validation would apply to you (and this is probably true
coding for any user input whether it involves a db or not). I am just
saying this is good practice in my opinion and had these people that
brought up the issue in the first place were doing it, then pgsql's
shortcomings would not have been as severe a problem. Things I am not
saying are: its ok for pgsql to have this DoS problem; its the frontends
responsibility to maintain data integrity not the backend.

with Perl and *using placeholders and bind values*, the application
developer has not to worry about this. So, usually I don't check the
values in my applications (e.g. if only values between 1 and 5 are
allowed and under normal circumstances only these are possible), it's the
task of the database (check constraint).

That's the idea. It's the job of the database to guarantee data
integrety.

Yes, but what is currently missing is a protocol to the backend
where a statement is prepared with placeholders and then executed
(multiple times) with given values. Then there is no doubt what is a
value, and what a part of the SQL.

I think that this would be a wanted feature of the next
protocol version. iirc the backend side part is currently beeing
implemented.

Andreas

"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:

Yes, but what is currently missing is a protocol to the backend
where a statement is prepared with placeholders and then executed
(multiple times) with given values. Then there is no doubt what is a
value, and what a part of the SQL.

This wouldn't have helped in the current case. The bug is in the
datetime parser which translates strings to an external
representation, not in the SQL parser.

--
Florian Weimer Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT fax +49-711-685-5898

ngpg@grymmjack.com writes:

if you are going to be passing any user input to the database, you
must/should validate in some manner before blindly passing it to the db.
The db can and should guarantee data integrity, but the database cannot
read your mind when it comes to how you structure your queries.

[example of SQL injection attack deleted]

This is not the problem at hand. SQL injection attacks can be avoided
easily. Bugs in the conversion of strings to internal PostgreSQL
objects are a different matter, though, and usually, devastating
effects cannot be avoided by (reasonably complex) checks in the
frontend.

--
Florian Weimer Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT fax +49-711-685-5898

Weimer@CERT.Uni-Stuttgart.DE (Florian Weimer) wrote

ngpg@grymmjack.com writes:

if you are going to be passing any user input to the database, you
must/should validate in some manner before blindly passing it to the db.
The db can and should guarantee data integrity, but the database cannot
read your mind when it comes to how you structure your queries.

[example of SQL injection attack deleted]

This is not the problem at hand. SQL injection attacks can be avoided
easily. Bugs in the conversion of strings to internal PostgreSQL
objects are a different matter, though, and usually, devastating
effects cannot be avoided by (reasonably complex) checks in the
frontend.

yeah i wasnt aware that adding a if(strlen($input) > SOME_REASONABLE_MAX)
was complex. the sql injection attack was just an(other) example of why
you do not simply forward user input to the backend. all i was trying to
point out is that most of these buffer overflows in the backend can be
avoided just as easily as the sql injection attack.