On 31/10/2018 18:51, Andrew Gierth wrote:
>>>>>> "PG" == PG Bug reporting form <noreply@postgresql.org> writes:
>
> PG> A simple table elaborating on the escapes for each special
> PG> character would be incredibly helpful at determining how to
> PG> translate those escapes for cleaning strings prior to insertion so
> PG> those of us using postgresql can quickly write cleaning functions
> PG> for data.
>
> If you're "writing cleaning functions" you're already making a serious
> mistake, because you should be passing data values as parameters (which
> do not require escapes) rather than interpolating into the query string.
>
> If you actually do need to interpolate into the query string for some
> reason (like doing COPY or other utility statement that doesn't support
> parameters), then you should be using the quote/escape functions
> provided by the driver for your client language (e.g. in libpq there is
> PQescapeStringConn).
Agreed. As pointed out in this report, though, the documentation doesn't
say that. This section is part of the "Lexical structure" chapter, so
it's perhaps more aimed at people writing drivers or SQL code
generators, than general application authors. But when someone like the
OP lands on that page, how can he tell?
It might be a good idea to add a note somewhere in there along the lines of:
"NOTE: All popular client libraries have functions for correctly quoting
and escaping user input, for use in string literals or SQL identifiers.
Most applications should use those, or use out-of-band query parameters,
instead of trying to follow the rules explained here directly. Please
refer to the documentation of your programming language or driver on how
to do that. The libpq quoting/escaping functions are explained in
https://www.postgresql.org/docs/current/static/libpq-exec.html#LIBPQ-EXEC-ESCAPE-STRING".
- Heikki
