ProgrammingError: non-integer constant in ORDER BY

[roland.wells@…]

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

[Ryan J Ollos]

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

[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

[Ryan J Ollos]

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)

[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)

[Ryan J Ollos]

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.

[Jun Omae]

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

@@ -74 +74 @@
         self.email = req.args.get('email')
         self.phone = req.args.get('phone')
 
+
+_COLS = set(('id', 'first', 'last', 'position', 'email', 'phone'))
+
+
 def ContactIterator(env, order_by = None):
     if not order_by:
         order_by = ('last', 'first')
+    for name in order_by:
+        if name not in _COLS:
+            raise ValueError("Must be one of %r: %r" % (_COLS, name))
+
     db = env.get_db_cnx()
+    if hasattr(db, 'quote'):
+        quote = db.quote  # Trac 0.12+
+    else:
+        quote = lambda name: name
     cursor = db.cursor()
-    #   Using the prepared db statement won't work if we have more than one entry in order_by
-    cursor.execute("""
-        SELECT id, first, last, position, email, phone
-        FROM contact ORDER BY %s""" % ','.join(['%s']*len(order_by)), order_by)
+    cursor.execute("""SELECT id, first, last, position, email, phone
+                      FROM contact ORDER BY %s""" %
+                   ','.join(quote(v) for v in order_by))
     for id, first, last, position, email, phone in cursor:
         contact = Contact(env)
         contact.id = id

[roland.wells@…]

Replying to rjollos:

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

[Ryan J Ollos]

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

[Ryan J Ollos]

In 14733:

0.2dev: Fixed incorrect quoting of column names. Fixes #11234.

This was a regression in [12247]. Patch by Jun Omae.