Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > psycopg2 / psycopg2.ProgrammingError: syntax error at or near"E'mytable'"

Reply
Thread Tools

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

 
 
mrdrew
Guest
Posts: n/a
 
      04-03-2010
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.
 
Reply With Quote
 
 
 
 
MRAB
Guest
Posts: n/a
 
      04-03-2010
mrdrew wrote:
> 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().
 
Reply With Quote
 
 
 
 
Tim Chase
Guest
Posts: n/a
 
      04-03-2010
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




 
Reply With Quote
 
Steve Holden
Guest
Posts: n/a
 
      04-03-2010
mrdrew wrote:
> 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
--
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/

 
Reply With Quote
 
mrdrew
Guest
Posts: n/a
 
      04-05-2010
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
 
Reply With Quote
 
Dennis Lee Bieber
Guest
Posts: n/a
 
      04-05-2010
On Mon, 5 Apr 2010 09:20:28 -0700 (PDT), mrdrew <(E-Mail Removed)>
declaimed the following in gmane.comp.python.general:

> 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 = %(id)d' % {"id": int(10)}

'SELECT * FROM mytable WHERE id = 10'

but rather the equivalent of

>>> 'SELECT * FROM mytable WHERE id = %(id)d' % {"id": "'" + str(int(10)) + "'"}

Traceback (most recent call last):
File "<interactive input>", line 1, in <module>
TypeError: int argument required
>>>


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.
--
Wulfraed Dennis Lee Bieber AF6VN
http://www.velocityreviews.com/forums/(E-Mail Removed) HTTP://wlfraed.home.netcom.com/

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Psycopg2 SyntaxError: invalid syntax on "INSERT INTO" database andydtaylor@gmail.com Python 9 01-10-2013 02:25 PM
psycopg2 / psycopg2.DataError: invalid input syntax for type ASh Python 10 03-31-2010 10:50 AM
"undefined symbol: TLSv1_method" error when import psycopg2 一首诗 Python 0 10-13-2009 12:58 PM
MemoryError c/vcompiler.h:745: Fatal Python error (Psycopg2) Luis P. Mendes Python 3 06-21-2009 01:50 PM
psycopg2 error Luis P. Mendes Python 2 05-09-2007 12:24 PM



Advertisments