Safe interpolation of column names

[Changaco]

Why don't we have this? It seems to me that a column name can be checked with a simple regexp, and a placeholder different than %s (e.g. %c) could be used to insert a column name into a query.

This is probably more of a psycopg2 issue, but let's discuss it here first, especially since we're considering porting to asyncpg (#58).

[Changaco]

Found a related issue in psycopg2, quote psycopg/psycopg2#438 (comment):

We will probably add an "identifier" adapter to correctly quote table and column names in a future release (or at least expose a function to do the escaping).

[chadwhitacre]

Not a huge paint point in my life, but I'm open to this.

[Changaco]

It's not a huge pain point, but it would be nice to have, especially to avoid security debt.

(I'm not planning to work on this right now, but I've just realized that I can't actually push to this repo since I'm no longer part of the Gratipay org.)

[chadwhitacre]

Oops! Happy to add you back as a contributor on this repo if you like?

[Changaco]

Yes, that seems like a reasonable solution.

[chadwhitacre]

Invite sent! :)

[Changaco]

Invite accepted. ;-) I think it's the 3rd time that I'm joining the Gratipay org. :D

[chadwhitacre]

https://github.com/martijndeh/lego reminds me of this.

[Changaco]

AFAICT quoting an identifier is as simple as:

def quote_identifier(s):
    return '"%s"' % s.replace('"', '""')

https://www.postgresql.org/docs/9.6/static/sql-syntax-lexical.html

There is a second kind of identifier: the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). A delimited identifier is always an identifier, never a key word. So "select" could be used to refer to a column or table named "select", whereas an unquoted select would be taken as a key word and would therefore provoke a parse error when used where a table or column name is expected. The example can be written with quoted identifiers like this:

UPDATE "my_table" SET "a" = 5;

Quoted identifiers can contain any character, except the character with code zero. (To include a double quote, write two double quotes.) This allows constructing table or column names that would otherwise not be possible, such as ones containing spaces or ampersands. The length limitation still applies.

[Changaco]

psycopg 2.7, which was released less than two months after this issue was opened, introduced a module for "safe" SQL query composition: psycopg2.sql – SQL string composition.