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@gmail.com> wrote:
2015-05-10 11:00 GMT+02:00 P. Christeas <xrg@linux.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.