psycopg2 / psycopg2.ProgrammingError: syntax error at or near "E'mytable'"

[mrdrew]

Hey all,

Right now I'm completely unable to pass parameters to queries under
any circumstances. I've got a fairly trivial query as a test...

c.execute('SELECT * FROM %(table_name)s LIMIT 1',
{'table_name':"mytable"})

It fails, giving the error message...

Traceback (most recent call last):
File "test.py", line 7, in <module>
c.execute('SELECT * FROM %(table_name)s LIMIT 1',
{'table_name':"mytable"})
psycopg2.ProgrammingError: syntax error at or near "E'mytable'"
LINE 1: SELECT * FROM E'mytable' LIMIT 1

This may be similar to the problem that ASh had (http://
groups.google.com/group/comp.lang.python/browse_thread/thread/
7463ded0971425f8/538e60ba0ccf2ad3?#538e60ba0ccf2ad3)

I'd really appreciate any ideas. At the moment, I'm stuck
concatenating strings and hoping for the best.

[MRAB]

I think that you're confusing Python's string formatting with SQL
placeholders.

The "%(table_name)s" works only with Python's '%' operator. You should
use only the "%s" form (or possibly "?", I'm not sure which!) in the
template string and pass the parameters in a tuple (maybe a list will
also work) when calling .execute().

[Tim Chase]

MRAB wrote:
> I think that you're confusing Python's string formatting with
> SQL placeholders.
>
> The "%(table_name)s" works only with Python's '%' operator.
> You should use only the "%s" form (or possibly "?", I'm not
> sure which!)

It varies depending on your DB driver. Check out the .paramstyle
property of your DB driver:

>>> import sqlite3
>>> sqlite3.paramstyle
'qmark'

(sqlite uses "?" as the placeholder). Annoying at times, but at
least documented and able to be automated which is more than I
can say for non-Python DB drivers.

> in the template string and pass the parameters in a tuple
> (maybe a list will also work) when calling .execute().

Additionally, the OP is passing in a *table-name*, not a
parameter value. Most DB interfaces only allow things like

# Okay:
cur.execute("select * from tbl where field=?", (value,))

not

# not okay
cur.execute("select * from ? where field=42", (tblname,))

For this, you really have to (1) use Python string-formatting
instead of DB parameters and (2) THOROUGHLY vet that the
table-name isn't something malicious -- either through
controlling that it never comes from the user, or enforcing a
fairly strict limit on what table-names can be used if they do
come from the user. Regardless of parameter placeholder style.

-tkc

[Steve Holden]

You've already been told about the syntactic errors you have made with
the psycopg2 paramstyle (use %s with a tuple, not %(name)s with a dict).

You should also understand that the purpose of parameterization is
twofold: firstly, to provide efficiency by allowing the database
back-end to avoid duplication of up-front query compilation work when
only the data differs; secondly to avoid any possibility of SQL
injection attacks by ensuring that data are properly escaped.

The first purpose relies on the tables being fixed at the time of
compilation, so you are probably going to have to use string
substitution to build at least that part of the query. Most database
drivers won't allow substitution of table names.

regards
Steve
--
Steve Holden +1 571 484 6266 +1 800 494 3119
See PyCon Talks from Atlanta 2010 http://pycon.blip.tv/
Holden Web LLC http://www.holdenweb.com/
UPCOMING EVENTS: http://holdenweb.eventbrite.com/

[mrdrew]

Thanks for the replies. The param style is pyformat. I've tried
using the '%s' style with a set and get exactly the same error.

c.execute('SELECT * FROM %s LIMIT 1',('mytable',))

psycopg2.ProgrammingError: syntax error at or near "E'mytable'"
LINE 1: SELECT * FROM E'mytable' LIMIT 1

MRAB and Steve Holden may be correct, but are at odds with the
psycopg2 documentation (http://initd.org/psycopg/docs/
usage.html#passing-parameters-to-sql-queries) which shows named
arguments being used with a dictionary.

It appears that the real problem is, as Steve mentioned, that the
device driver may not allow table name substitution. The following
query seems to work...

c.execute('SELECT * FROM mytable WHERE id = %(id)s',{'id':'10'})

(Oddly enough, this one doesn't)
c.execute('SELECT * FROM mytable WHERE id = %(id)d',{'id':int(10)})
TypeError: int argument required