This project is archived and is in readonly mode.

#167 ✓resolved
Psycopg website

Problem with %, (, ) in names of parameters in cursor.execute

Reported by Psycopg website | July 11th, 2013 @ 05:39 AM

Submitted by: Basil Peace grv87@yandex.ru

Documentations says that 'in order to include a literal % in the query you can use the %% string'. However, this doesn't work for names of parameters when dictionary-style query string is used.

I propose to add escaping for names of parameters. '%' and ')' (and probably '(') should be escaped. For example, using '%' escape symbol, the following commands would be correct:

cursor.execute('INSERT INTO test1 ("50%%") VALUES (%(50%%)s)', {'50%': 123}) cursor.execute('INSERT INTO test3 ("max(price)") VALUES (%(max(price%))s)', {'max(price)': 123})

Currently %%-escaping for parameters doesn't work:

cursor.execute('INSERT INTO test1 ("50%%") VALUES (%(50%%)s)', {'50%': 123}) psycopg2.ProgrammingError: incomplete placeholder: '%(' without ')'

And with braces there is something wonderful:

cursor.execute('INSERT INTO test3("max(price)") VALUES (%(max(price))s)', {'max(price)': 123}) KeyError: 'max(price'
cursor.execute('INSERT INTO test3("max(price)") VALUES (%(max(price))s)', {'max(price': 123}) KeyError: 'max(price)'
cursor.execute('INSERT INTO test3("max(price)") VALUES (%(max(price))s)', {'max(price)': 123, 'max(price': 123}) KeyError: 'max(price)'

Comments and changes to this ticket

  • Daniele Varrazzo

    Daniele Varrazzo July 12th, 2013 @ 08:31 PM

    • State changed from “new” to “open”

    I don't know about %, but ( and ) don't work in Python either. % works in Python but not in psycopg: I'll have to check why.

    I think the only fix you may end up with is to just have % accepted inside the parenthesis, with no need to escape, while ( and `) won't be accepted.

    In [4]: print "foo %(50%)s baz" % {'50%': 'bar'}
foo bar baz

In [5]: cur.mogrify("foo %(50%)s baz", {'50%': 'bar'})
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
/home/piro/dev/psycopg2/build/lib.2.7/<ipython-input-5-71c53d1f4f43> in <module>()
----> 1 cur.mogrify("foo %(50%)s baz", {'50%': 'bar'})

ProgrammingError: incomplete placeholder: '%(' without ')'

    I don't want to diverge from what Python's % accepts and complicate something as easy as the name of the parameters with escaping rules.

  • Daniele Varrazzo

    Daniele Varrazzo October 16th, 2013 @ 02:37 PM

    • State changed from “open” to “resolved”

    Issue solved by declaring in the docs that %, (, ) are not supported.

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

<b>WARNING:</b> the informations in this tracker are archived. Please submit new tickets or comments to <a href="https://github.com/psycopg/psycopg2/issues">the new tracker</a>.
<br/>
Psycopg is the most used PostgreSQL adapter for the Python programming language. At the core it fully implements the Python DB API 2.0 specifications. Several extensions allow access to many of the features offered by PostgreSQL.

Shared Ticket Bins

People watching this ticket

Tags

Pages