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

Discussion in 'Python' started by mrdrew, Apr 3, 2010.

  1. mrdrew

    mrdrew Guest

    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.
    mrdrew, Apr 3, 2010
    #1
    1. Advertising

  2. mrdrew

    MRAB Guest

    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().
    MRAB, Apr 3, 2010
    #2
    1. Advertising

  3. mrdrew

    Tim Chase Guest

    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
    Tim Chase, Apr 3, 2010
    #3
  4. mrdrew

    Steve Holden Guest

    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/
    Steve Holden, Apr 3, 2010
    #4
  5. mrdrew

    mrdrew Guest

    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
    mrdrew, Apr 5, 2010
    #5
  6. On Mon, 5 Apr 2010 09:20:28 -0700 (PDT), mrdrew <>
    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://wlfraed.home.netcom.com/
    Dennis Lee Bieber, Apr 5, 2010
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.

Share This Page