Re: Sanitize schema name

Поиск
Список
Период
Сортировка
От Ludovic Gasc
Тема Re: Sanitize schema name
Дата
Msg-id CAON-fpFpMzUsXCnrxtwmPGJZX6Js2j285Oo6knMXeiYXMMMFPw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Sanitize schema name  (Adrian Klaver <adrian.klaver@aklaver.com>)
Ответы Re: Sanitize schema name  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список 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)

2015-05-07 15:33 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.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.klaver@aklaver.com

В списке psycopg по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: Sanitize schema name
Следующее
От: Adrian Klaver
Дата:
Сообщение: Re: Sanitize schema name