Re: Sanitize schema name

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
>>
>
>