Modify

Opened 17 months ago

Last modified 11 months ago

#11234 accepted defect

ProgrammingError: non-integer constant in ORDER BY

Reported by: roland.wells@… Owned by: rjollos
Priority: normal Component: ContactsPlugin
Severity: normal Keywords:
Cc: Trac Release:

Description (last modified by rjollos)

After installing and enabling the Contacts Plugin, I get the following error when navigating to the /contacts url.

Trac detected an internal error:
ProgrammingError: non-integer constant in ORDER BY

accompanied by the following error in the system log:

Jul 22 15:38:04 ledger postgres[36234]: [3-1] ERROR:  non-integer constant in ORDER BY
Jul 22 15:38:04 ledger postgres[36234]: [3-2] STATEMENT:  
Jul 22 15:38:04 ledger postgres[36234]: [3-3] SELECT id, first, last, position, email, phone
Jul 22 15:38:04 ledger postgres[36234]: [3-4] FROM contact ORDER BY E'last',E'first'

System Info:

FreeBSD 8.3
postgresql-server-8.3.22
Trac	1.0.1
Babel	0.9.6
Docutils	0.10
FullBlog	0.1.1
Genshi	0.7 (with speedups)
mod_wsgi	3.4 (WSGIProcessGroup WSGIApplicationGroup %{GLOBAL})
Pygments	1.6
Python	2.7.5 (default, Jul 22 2013, 00:04:17) [GCC 4.2.2 20070831 prerelease [FreeBSD]]
pytz	2013b
setuptools	0.6c11
Subversion	1.8.0 (r1490375)
jQuery	1.4.4

Enabled Plugins:

contacts	0.1	/home/wellsolives/trac/plugins/contacts-0.1-py2.7.egg
graphviz	0.7.4	/usr/local/lib/python2.7/site-packages/graphviz-0.7.4-py2.7.egg
TracFullBlogPlugin	0.1.1	/usr/local/lib/python2.7/site-packages/TracFullBlogPlugin-0.1.1-py2.7.egg
TracTags	0.7dev	/usr/local/lib/python2.7/site-packages/TracTags-0.7dev-py2.7.egg

Attachments (0)

Change History (9)

comment:1 Changed 17 months ago by rjollos

  • Owner changed from cmc to rjollos

It looks like I introduced a regression in [12247]. I'll fix it shortly.

comment:2 Changed 15 months ago by jerkmuffin

Oh. I am experiencing this also!

How to Reproduce

While doing a GET operation on /contacts, Trac issued an internal error.

(please provide additional details here)

Request parameters:

{}

User agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/29.0.1547.76 Safari/537.36

System Information

Trac 1.0
Genshi 0.6 (without speedups)
psycopg2 2.5
Pygments 0.9
Python 2.5.1 (r251:54863, May 2 2007, 16:27:44)
[GCC 4.1.2 (Ubuntu 4.1.2-0ubuntu4)]
setuptools 0.6c11
jQuery 1.7.2

Enabled Plugins

contacts 0.1

Python Traceback

Traceback (most recent call last):
  File "build/bdist.linux-x86_64/egg/trac/web/main.py", line 497, in _dispatch_request
    dispatcher.dispatch(req)
  File "build/bdist.linux-x86_64/egg/trac/web/main.py", line 233, in dispatch
    content_type)
  File "build/bdist.linux-x86_64/egg/trac/web/chrome.py", line 1000, in render_template
    encoding='utf-8')
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/core.py", line 183, in render
    return encode(generator, method=method, encoding=encoding, out=out)
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/output.py", line 58, in encode
    for chunk in iterator:
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/output.py", line 339, in __call__
    for kind, data, pos in stream:
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/output.py", line 826, in __call__
    for kind, data, pos in stream:
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/output.py", line 670, in __call__
    for kind, data, pos in stream:
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/output.py", line 771, in __call__
    for kind, data, pos in chain(stream, [(None, None, None)]):
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/output.py", line 586, in __call__
    for ev in stream:
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/core.py", line 288, in _ensure
    for event in stream:
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/core.py", line 288, in _ensure
    for event in stream:
  File "build/bdist.linux-x86_64/egg/trac/web/chrome.py", line 1137, in _strip_accesskeys
    for kind, data, pos in stream:
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/core.py", line 288, in _ensure
    for event in stream:
  File "build/bdist.linux-x86_64/egg/trac/web/chrome.py", line 1126, in _generate
    for kind, data, pos in stream:
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/template/base.py", line 605, in _include
    for event in stream:
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/template/markup.py", line 378, in _match
    ctxt, start=idx + 1, **vars):
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/template/markup.py", line 378, in _match
    ctxt, start=idx + 1, **vars):
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/template/markup.py", line 327, in _match
    for event in stream:
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/template/base.py", line 545, in _flatten
    for kind, data, pos in stream:
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/core.py", line 288, in _ensure
    for event in stream:
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/path.py", line 588, in _generate
    subevent = next()
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/template/base.py", line 605, in _include
    for event in stream:
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/template/markup.py", line 316, in _strip
    event = next()
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/template/base.py", line 545, in _flatten
    for kind, data, pos in stream:
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/core.py", line 288, in _ensure
    for event in stream:
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/path.py", line 588, in _generate
    subevent = next()
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/template/base.py", line 605, in _include
    for event in stream:
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/template/markup.py", line 316, in _strip
    event = next()
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/template/base.py", line 545, in _flatten
    for kind, data, pos in stream:
  File "/usr/lib/python2.5/site-packages/Genshi-0.6-py2.5.egg/genshi/template/directives.py", line 366, in __call__
    for item in iterable:
  File "build/bdist.linux-x86_64/egg/contacts/model.py", line 85, in ContactIterator
    FROM contact ORDER BY %s""" % ','.join(['%s']*len(order_by)), order_by)
  File "build/bdist.linux-x86_64/egg/trac/db/util.py", line 65, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
ProgrammingError: non-integer constant in ORDER BY

comment:3 Changed 15 months ago by rjollos

I won't have time to look at it until this weekend, but I suppose maybe we just need single-quotes around the parameters?:

-        FROM contact ORDER BY %s""" % ','.join(['%s']*len(order_by)), order_by)
+        FROM contact ORDER BY %s""" % ','.join(["'%s'"]*len(order_by)), order_by)

comment:4 Changed 15 months ago by jerkmuffin

That line is missing a string replace... this fixed my problem

-        FROM contact ORDER BY %s""" % ','.join(['%s']*len(order_by)), order_by)
+        FROM contact ORDER BY %s""" % ','.join(['%s']*len(order_by))% order_by)

comment:5 follow-up: Changed 15 months ago by rjollos

That might work, and in particular it might work for one type of database, but we should not use string replace, but rather pass the parameter as an argument to the DB API. So the real problem lies somewhere in how the SQL string is formed.

comment:6 in reply to: ↑ 5 Changed 15 months ago by jun66j5

Replying to rjollos:

That might work, and in particular it might work for one type of database, but we should not use string replace, but rather pass the parameter as an argument to the DB API. So the real problem lies somewhere in how the SQL string is formed.

We cannot use the DB API in this case because of specifying column names of contact table for the order_by. Instead, use db.quote. However the method is available since Trac 0.12, see trac:r9062.

Untested patch.

  • contacts/model.py

     
    7474        self.email = req.args.get('email')
    7575        self.phone = req.args.get('phone')
    7676
     77
     78_COLS = set(('id', 'first', 'last', 'position', 'email', 'phone'))
     79
     80
    7781def ContactIterator(env, order_by = None):
    7882    if not order_by:
    7983        order_by = ('last', 'first')
     84    for name in order_by:
     85        if name not in _COLS:
     86            raise ValueError("Must be one of %r: %r" % (_COLS, name))
     87
    8088    db = env.get_db_cnx()
     89    if hasattr(db, 'quote'):
     90        quote = db.quote  # Trac 0.12+
     91    else:
     92        quote = lambda name: name
    8193    cursor = db.cursor()
    82     #   Using the prepared db statement won't work if we have more than one entry in order_by
    83     cursor.execute("""
    84         SELECT id, first, last, position, email, phone
    85         FROM contact ORDER BY %s""" % ','.join(['%s']*len(order_by)), order_by)
     94    cursor.execute("""SELECT id, first, last, position, email, phone
     95                      FROM contact ORDER BY %s""" %
     96                   ','.join(quote(v) for v in order_by))
    8697    for id, first, last, position, email, phone in cursor:
    8798        contact = Contact(env)
    8899        contact.id = id

comment:7 follow-up: Changed 12 months ago by rjollos

  • Description modified (diff)
  • Status changed from new to accepted

comment:8 in reply to: ↑ 7 Changed 11 months ago by roland.wells@…

Replying to rjollos:

Patched with this diff and is working on my installation. Thanks.

comment:9 Changed 11 months ago by rjollos

Sorry for the delay. I'll try to get the change committed this weekend.

Add Comment

Modify Ticket

Action
as accepted The owner will remain rjollos.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.