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