Re: how to preserve \n in select statement - Mailing list pgsql-sql

Hi Richard..

If your users are required to fire only SELECT and no DML, you can do the
following:

BEGIN;
execute the statements given by user
ROLLBACK;

This will not affect your SELECT and also if any malicious user gives DELETE
statement, that will not have any impact too..

HTH

Thanx

Denis

----- Original Message -----
From: "Richard Huxton" <dev@archonet.com>
To: "Matt Van Mater" <nutter_@hotmail.com>; <tgl@sss.pgh.pa.us>
Cc: <pgsql-sql@postgresql.org>
Sent: Monday, December 22, 2003 2:34 PM
Subject: Re: [SQL] how to preserve \n in select statement


> On Saturday 20 December 2003 17:58, Matt Van Mater wrote:
> >
> > I readily admit that I didn't read much into it, but I think you are
> > mistaken.  If you look at the following links you will see that
postgresql
> > definitely had a vulnerability that allowed sql injection through the
use
> > of escape characters.  I was simply saying that this behavior might be a
> > way of preventing that from happening again.
> > http://www.cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2002-0802
> > http://cert.uni-stuttgart.de/doc/postgresql/escape/
>
> Ah - this "vulnerability" is still there - and, in fact is in every
database
> ever produced. The issue is that applications using untrusted data to
build a
> query string can be duped by a malicious user.
>
> So - say you have a query template:
>   SELECT * FROM recent_news WHERE topic='?'
>
> If a malicious user supplies the string: '; DELETE FROM user_logins;
SELECT '
> then you will have the resulting query string:
> SELECT * FROM recent_news WHERE topic=''; DELETE FROM user_logins; SELECT
''
>
> If you allow multiple queries in one string, there is no way to prevent
this.
> If you disallow multiple queries, there are still attacks that are
possible.
>
> This is why it is vital to parse and validate user input. If you are
asking
> for an integer, check that it is. If you are asking for a string, quotes
etc.
> need to be escaped (AFAICT the stuttgart url describes a function that
lets
> you do this - you still need to call it).
>
> The first URL seems to deal with an old bug that meant a cunning attacker
> could bypass your escaping. It is still vital that the application (or
some
> middle layer) validates and checks all untrusted (and preferably all
trusted)
> input. All standard database layers (Perl/PHP/Java, whatever) supply tools
> for this.
>
> Your particular issue with \n is just down to PG's standard string
parsing -
> not really related.
> --
>   Richard Huxton
>   Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html