Re: Sanitize schema name

David, I like your suggestion, we will use that on production, while we're
waiting the new release of pyscopg2.

Regards.

--
Ludovic Gasc (GMLudo)
http://www.gmludo.eu/

2015-05-11 19:05 GMT+02:00 David Niergarth <dn(at)hddesign(dot)com>:

> Lack of something like PQescapeIdentifier has always felt like a hole in
> the API. When I need to dynamically add a schema- or table name to a query *from
> a trusted source*, I'll do something like
>
> sql = """
> SELECT id
> FROM {schema}.{table}
> WHERE name = %(spam)s
> """.format(schema=my_schema, table=my_table)
>
> cursor.execute(sql, dict(spam=user_spam)
>
>
> to make it clear where I'm deliberately inserting an identifier. Having a
> %t or similar would be much simpler and would handle the untrusted case.
>
> David
>
> On Sun, May 10, 2015 at 7:07 AM, Ludovic Gasc <gmludo(at)gmail(dot)com> wrote:
>
>> 2015-05-10 11:00 GMT+02:00 P. Christeas <xrg(at)linux(dot)gr>:
>>
>>> On Thursday 07 of May 2015, Daniele Varrazzo wrote:
>>> > Looks like there is more and more the need of exposing a function like
>>> > libpq's PQescapeIdentifier [1]. Too bad psycopg 2.6 has been released
>>> > so recently, I'm reluctant to add such a function to 2.6.1.
>>> >
>>> > Maybe releasing a small Python module exposing just that function,
>>> > then add the functionality to psycopg 2.7?
>>>
>>> I vote for a pre-release of 2.7, with this feature. Modifying the API,
>>> even if
>>> the new function wouldn't interfere with any existing ones, calls for a
>>> version bump.
>>>
>>
>> I'm in to be one of a beta-tester.
>>
>>
>>>
>>> Just another idea, would it make sense to abuse the semantics of string
>>> formatting[1] and introduce another type, say "%t" [2] for implicit
>>> identifier
>>> escaping?
>>>
>>> This would make our queries look like:
>>> cr.execute("SELECT id FROM %t WHERE name = %s", ('some.tbl', 'spam'))
>>>
>>
>> Sincerely, it should be awesome, because it means it's more end-developer
>> friendly.
>> If you also support %(key)t syntax it should be wonderful, because we use
>> dict to fill query values, easier to write.
>>
>>
>>>
>>>
>>>
>>> [1] https://docs.python.org/2/library/stdtypes.html#string-formatting
>>> [2] I notice that "t" isn't used for anything else, so far.
>>>
>>>
>>>
>>> --
>>> Sent via psycopg mailing list (psycopg(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/psycopg
>>>
>>
>>
>