Re: Sanitize schema name - Mailing list psycopg
From | Adrian Klaver |
---|---|
Subject | Re: Sanitize schema name |
Date | |
Msg-id | 554FF51A.8070803@aklaver.com Whole thread Raw |
In response to | Re: Sanitize schema name (Ludovic Gasc <gmludo@gmail.com>) |
Responses |
Re: Sanitize schema name
|
List | psycopg |
On 05/09/2015 11:55 PM, Ludovic Gasc wrote: > 2015-05-10 2:41 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>>: > > 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> > <mailto: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. > > > 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. You are braver then I, especially given this: https://docs.python.org/3.4/library/asyncio.html " Note The asyncio package has been included in the standard library on a provisional basis. Backwards incompatible changes (up to and including removal of the module) may occur if deemed necessary by the core developers. " -- Adrian Klaver adrian.klaver@aklaver.com