Thread: BUG #15474: Special character escape sequences need betterdocumentation, or more easily found documentation

The following bug has been logged on the website:

Bug reference:      15474
Logged by:          Micheal Taylor
Email address:      bubthegreat@gmail.com
PostgreSQL version: 11.0
Operating system:   Any
Description:

When looking for postgresql documentation on characters that need to be
escaped, and how to escape them, I consistently get to this page:

https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-SPECIAL-CHARS

That page goes over high levels of escaping numerous things, but in the
special characters and operators, it doesn't clearly show how to escape the
operators or special characters within a more complicated query structure.
For example, if I have the following table:

            CREATE TABLE IF NOT EXISTS {tablename}
            (
                time        TIMESTAMP,
                case_number VARCHAR(25),
                jira        VARCHAR(25),
                status      VARCHAR(25),
                fqdn        VARCHAR(255),
                subject     TEXT,
                description TEXT,
                comment     TEXT
            )

And the following insertion information:

    INSERT INTO orphans(time, case_number, fqdn, status, subject,
description)
    SELECT '{timestamp}', '{case_number}', '{fqdn}', 'new', '{subject}',
'{description}'
    WHERE
        NOT EXISTS (
            SELECT case_number, status FROM orphans
            WHERE case_number = '{case_number}'
        )

Where all columns inputs are strings, if any of those inputs have special
characters like % or ', it is not clear by quick inspection of the
documentation how to escape those characters.  A simple table elaborating on
the escapes for each special character would be incredibly helpful at
determining how to translate those escapes for cleaning strings prior to
insertion so those of us using postgresql can quickly write cleaning
functions for data.

Where all columns inputs are strings, if any of those inputs have special
characters like % or '

A simple table elaborating on
the escapes for each special character would be incredibly helpful at
determining how to translate those escapes for cleaning strings prior to
insertion so those of us using postgresql can quickly write cleaning
functions for data.

>>>>> "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).

-- 
Andrew (irc:RhodiumToad)

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