Re: 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