Re: Correct escaping of untrusted data

Geoff Caplan <geoff(at)variosoft(dot)com> writes:
> Are the standard escaping functions found in the PHP, Tcl etc APIs to
> Postgres bombproof?

I dunno; you'd probably want to look at the source for each one you
planned to use, anyway, if you're being paranoid. As long as they
escape ' and \ they should be okay. If your source language allows
embedded nulls (\0) in strings you might want to reject those as well.

> Are there any encodings that might slip through
> and be cast to malicious strings inside Postgres?

All the supported encodings are supersets of ASCII, so I don't think
there is any such risk. There is a risk in the opposite direction
I think: if the escaping function doesn't know the encoding being used
it might think that one byte of a multibyte character is ' or \ and
try to escape it, thereby breaking the data. This could not happen in
"sane" encodings like UTF-8, however, just in the one or two Far Eastern
encodings that allow multibyte characters to contain bytes <= 0x7F.

Since you as the application programmer can control what client-side
encoding is used, the simplest answer here is just to be sure you're
using a sane encoding, or at least that the escaping function knows
the encoding you're using.

> What about functions like convert(): could they be used to slip
> something through the escaping function?

Don't see how. The issue is to be sure that the query string traveling
to the backend will be interpreted the way you expected. By the time
any server-side function executes it is far too late to change that
interpretation.

regards, tom lane