Thread: Re: [COMMITTERS] 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
[SECURITY] DoS attack on backend possible (was: Re: [COMMITTERS] pgsql-server/src)
From
Florian Weimer
Date:
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 whileprocessing 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 connectionson 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, Pennsylvania19073
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 On Sun, 2002-08-11 at 12:09, Tom Lane wrote: > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
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
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&cat=audi&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
alllight. --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.
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.