Re: Escaping strings for inclusion into SQL queries

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