Re: Escaping strings for inclusion into SQL queries

I agree with Hannu, that:

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

is an important feature to expose out to the client. My primary reason
is a perfomance one. Allowing the client to parse a SQL statement once
and then supplying bind values for arguments and executing it multiple
times can save a significant amount of server CPU, since the parsing and
planning of the statement is only done once, even though multiple
executions occur. This functionality is available in the backend
(through SPI) and plpgsql uses it, but there isn't anyway to take
advantage of this SPI functionality on the client (i.e. jdbc, odbc, etc.)

I could see this implemented in different ways. One, by adding new SQL
commands to bind or execute an already open statement, or two, by
changing the FE/BE protocol to allow the client to open, parse,
describe, bind, execute and close a statement as separate actions that
can be sent to the server in one or more requests. (The latter is how
Oracle does it).

I also would like to see this added to the todo list.

thanks,
--Barry

Hannu Krosing wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>
>