From: Richard Huxton <dev@archonet.com>
To: "Matt Van Mater" <nutter_@hotmail.com>, tgl@sss.pgh.pa.us
Subject: Re: how to preserve \n in select statement
Date: Mon, 22 Dec 2003 09:04:11 +0000
User-Agent: KMail/1.5
Cc: pgsql-sql@postgresql.org
References: <BAY9-F42t0CI76rOJhK00098f93@hotmail.com>
In-Reply-To: <BAY9-F42t0CI76rOJhK00098f93@hotmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200312220904.11730.dev@archonet.com>

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