Re: Escaping strings for inclusion into SQL queries

From: Hannu Krosing <hannu(at)tm(dot)ee>
To: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: Florian Weimer <Florian(dot)Weimer(at)RUS(dot)Uni-Stuttgart(dot)DE>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Escaping strings for inclusion into SQL queries
Date: 2001-08-31 03:52:35
Message-ID: 3B8F0A03.C68EEA2B@tm.ee
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Bruce Momjian wrote:
>
> Your patch has been added to the PostgreSQL unapplied patches list at:
>
> http://candle.pha.pa.us/cgi-bin/pgpatches
>
> I will try to apply it within the next 48 hours.
>
> > It has come to our attention that many applications which use libpq
> > are vulnerable to code insertion attacks in strings and identifiers
> > passed to these applications. We have collected some evidence which
> > suggests that this is related to the fact that libpq does not provide
> > a function to escape strings and identifiers properly. (Both the
> > Oracle and MySQL client libraries include such a function, and the
> > vast majority of applications we examined are not vulnerable to code
> > insertion attacks because they use this function.)

I think the real difference is what I complained in another mail to this
list -
in postgresql you can't do PREPARE / EXECUTE which could _automatically_
detect
where string escaping is needed or just eliminate the need for escaping.
In postgreSQL you have to construct all queries yourself by inserting
your
parameters inside your query strings in right places and escaping them
when
needed. That is unless you use an interface like ODBC/JDBS that fakes
the
PREPARE/EXECUTE on the client side and thus does the auto-escaping for
you .

I think that this should be added to TODO

* make portable BINARY representation for frontend-backend protocol by
using
typsend/typreceive functions for binary and typinput typoutput for
ASCII
(as currently typinput==typreceive and typoutput==typsend is suspect
the
usage to be inconsistent).

* make SQL changes to allow PREPARE/EXECUTE in main session, not only in
SPI

* make changes to client libraries to support marshalling arguments to
EXECUTE
using BINARY wire protocol or correctly escaped ASCII. The binary
protocol
would be very helpful for BYTEA and other big binary types.

> > We therefore suggest that a string escaping function is included in a
> > future version of PostgreSQL and libpq. A sample implementation is
> > provided below, along with documentation.

While you are at it you could also supply a standard query delimiter
function
as this is also a thing that seems to vary from db to db.

------------------
Hannu

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2001-08-31 08:51:09 Re: Multiple semicolon separated statements and autocommit
Previous Message Tatsuo Ishii 2001-08-31 02:06:00 Re: Majordomo being upgraded ...