Re: Sanitize schema name

On 05/09/2015 01:03 PM, Ludovic Gasc wrote:

2015-05-08 0:12 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>:

    On 05/07/2015 01:06 PM, Ludovic Gasc wrote:

        Thanks all for your answers, you understand well my need.

        About PQescapeIdentifier:
        1. An idea of release date for the next version of psycopg2 ?
        2. Are you sure it's enough to protect against SQL injections,
        because
        you can read in the documentation: *Tip:* As with string
        literals, to
        prevent SQL injection attacks, SQL identifiers must be escaped
        when they
        are received from an untrustworthy source.

        About format() it doesn't work for schema, example:
        SELECT format('SELECT * FROM %I WHERE id=1', 'lg.devices')
        => SELECT * FROM "lg.devices" WHERE id=1
        SELECT * FROM "lg.devices" WHERE id=1
        => ERROR:  relation "lg.devices" does not exist
        LIGNE 1 : SELECT * FROM "lg.devices" WHERE id=1
                                  ^

        ********** Error **********

        ERROR: relation "lg.devices" does not exist


    Try:

    SELECT format('SELECT * FROM %I.%I WHERE id=1', 'lg', 'devices')


Ok, now, it works, but, I need to launch the query two times: First time
with SELECT format(, a second time with the result of the first query.
It should be possible to execute that only in one pass ?

As far as I know, only in plpgsql:

http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE

Hence the previous suggestion about creating a psycopg2 function that you could use directly.

    Still not sure why you cannot use search_path and avoid the schema
    qualification altogether?


Because I use a pool of pgsql sockets where no connexions are dedicated
to one particular client.

So all the clients are connecting to a single database with many schemas, each schema unique to a client?

I could change that each time just before to execute each query, but it
shouldn't be very efficient.

So is the login role for each client unique, where you could use ALTER ROLE SET search_path to have it preset:

http://www.postgresql.org/docs/9.4/interactive/sql-alterrole.html

    --
    Adrian Klaver
    adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com