Re: Sanitize schema name

On 11 May 2015 02:17, "Adrian Klaver" <adrian.klaver@aklaver.com> wrote:
>
> 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. "

Brave certainly not, early adopter, yes.
In the same time, before to use AsyncIO, I had a mixin between Twisted for WebSockets and telephony + flask for http.
Now, I have all-in-one daemons for all protocols, aiohttp for http and WebSockets, panoramisk for telephony and API-Hour as a glue between that.

Even if I have the "risk" for a big change, but after my discussions with AsyncIO developers I don't think so, the time I save with a very simpler async implementation compare to Twisted but also now I can share easily my business logic source code between protocols justifies clearly this choice I've made one year ago.
My Dev team speed has clearly changed positively after this decision.

The critical part was to debug libraries because they are pretty young, but for a while, at least the AsyncIO libraries I use, are now enough stable and bugs free to build on top of.

>
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com