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]

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.

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]

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:
'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]

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.

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

 

[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

 

[Dennis Lee Bieber]

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

Pretty much ALL db-api compliant adapters require the schema
entities (table and field names) to be built into the SQL. Schema
entities should never be user-supplied data (the application should, at
best, present a list of the available entities from which a user may
select, but the user never /types/ the entity name itself; the
application converts the selection [checkbox, list index] into the
proper name internally).

ONLY /data/ items can (and should) be parameterized placeholders.

One reason being that the parameter replacement system ensures the
data item is syntactically safe (no SQL injection, special characters
escaped). Don't know about the PostgreSQL adapters, but MySQLdb
internally uses Python string interpolation to build the final SQL that
gets sent (Prior to MySQL 5.x, there was no low-level
prepared/parameterized interface).

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

This would appear to be similar to the MySQLdb behavior. The
parameter (int(10)) will have been stringified, escaped, and quoted
BEFORE being given to the placeholder. So what you have is NOT
'SELECT * FROM mytable WHERE id = 10'

but rather the equivalent of
Traceback (most recent call last):

where your int(10) gets converted to a string, and that string gets
surrounded by SQL quotes (I ignored the part where any quotes /in/ the
string are escaped first).

There is a reason "pyformat" typically means ONLY %s placeholders...
because ALL supplied parameters ARE strings by the time they get to the
placeholder.