From: | Ludovic Gasc <gmludo(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | Dorian Hoxha <dorian(dot)hoxha(at)gmail(dot)com>, psycopg(at)postgresql(dot)org |
Subject: | Re: Sanitize schema name |
Date: | 2015-05-07 20:06:52 |
Message-ID: | CAON-fpFpMzUsXCnrxtwmPGJZX6Js2j285Oo6knMXeiYXMMMFPw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | psycopg |
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
An idea should be also to verify if "lg" schema exists, but it means I need
to do two SQL queries each type, or keep a cache of schema list.
--
Ludovic Gasc (GMLudo)
http://www.gmludo.eu/
2015-05-07 15:33 GMT+02:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:
> On 05/07/2015 04:35 AM, Dorian Hoxha wrote:
>
>> He wants to dynamically pass the table name:
>>
>> cursor.execute("SELECT * FROM %s", (table,)) # won't work
>>
>>
> How about using format()?:
>
> http://www.postgresql.org/docs/9.4/static/functions-string.html#FUNCTIONS-STRING-FORMAT
>
> Available 9.1+
>
> cur.execute("select format('select * from %I', 'student_info')")
>
> sql_str = cur.fetchone()[0]
>
> sql_str
> 'select * from student_info'
>
>
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2015-05-07 22:12:04 | Re: Sanitize schema name |
Previous Message | Adrian Klaver | 2015-05-07 13:33:29 | Re: Sanitize schema name |