Re: Sql injection attacks

Hi folks

Very instructive thread. Thanks to everyone for the input, and
especial thanks to Lincoln Yeoh for his detailed explanation of his
approach: a standout post!

Sorry for the silence - it's not that I'm unappreciative, just that
I've been away from my desk.

Tom Lane wrote:

> I think you misunderstood. Escaping is perfectly safe (given a correct
> escaping function) if it's used on *every* untrustworthy input string.
> The argument for the "keep data separate from code" approach is
> essentially just that it's easier to be sure you haven't forgotten
> anyplace where you need to escape.

You are right: I was misunderstanding the nature of the problem. I now
appreciate that any effective approach must include both validation
and escaping of all untrusted data used in a query, both when it is
first passed into the application, and also when it is re-used from
the data store.

But I'd like to be sure I understand exactly what Tom means by "a
correct escaping function"? What are the potential breakout strings
you have to check for, and are they all caught by the standard
Postgres API escaping functions in PHP, Tcl etc?

For example, with SQL Server, it appears you can slip in a
single-quote using encodings that are cast to ASCII #39 by the server.
Also, what about pg functions like convert()? Could they be used to
smuggle in a breakout character?

Looked at like this, perhaps robust escaping is not so simple? Any
advice much appreciated.

------------------
Geoff Caplan
Vario Software Ltd
(+44) 121-515 1154