Re: Sanitize schema name

2015-05-10 2:41 GMT+02:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:

> On 05/09/2015 01:03 PM, Ludovic Gasc wrote:
>
>> 2015-05-08 0:12 GMT+02:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)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.

Ok, at least to me, it's the ideal situation.

>
>
>
>>
>>
>> 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?

Exactly. With that, I can easily generate cross statistics between clients
for billing, because I've a hierarchy of clients.

>
>
> 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

Thanks for your help, but this suggestion doesn't fit with my need: If I do
that, I need to have a dedicated connection for each client.
The idea is to mutualize pgsql connections for all clients like an
applicative "virtualization": for some big clients, it will use several
connections from the aiopg's pool, for some others, it uses no connections.

>
>
>
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>