OperationalError: near "references": syntax error

[mrelbe <mikael@…>]

Performing ticket queries involving a custom ticket field names "references" yields an error (see below).

Queries using other custom fields works well. (I have also seen the same error on a Trac 0.11.4 installation).

No plugins are installed except for accountmgr (which is not playing any role here).

---

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

Request parameters:

{'col': [u'id',
         u'summary',
         u'status',
         u'type',
         u'priority',
         u'milestone',
         u'component'],
 'order': u'priority',
 'references': u'~'}

User Agent was: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv:1.9.1.7) Gecko/20091221 Firefox/3.5.7

System Information

Trac	0.11.6
Python	2.6.4 (r264:75708, Oct 26 2009, 08:23:19) [MSC v.1500 32 bit (Intel)]
setuptools	0.6c11
SQLite	3.5.9
pysqlite	2.4.1
Genshi	0.6dev-r1092
Pygments	1.1.1
Subversion	1.6.6 (r40053)
jQuery:	1.2.6

Python Traceback

Traceback (most recent call last):
  File "C:\Python26\lib\site-packages\trac-0.11.6-py2.6.egg\trac\web\main.py", line 450, in _dispatch_request
    dispatcher.dispatch(req)
  File "C:\Python26\lib\site-packages\trac-0.11.6-py2.6.egg\trac\web\main.py", line 206, in dispatch
    resp = chosen_handler.process_request(req)
  File "C:\Python26\lib\site-packages\trac-0.11.6-py2.6.egg\trac\ticket\query.py", line 836, in process_request
    return self.display_html(req, query)
  File "C:\Python26\lib\site-packages\trac-0.11.6-py2.6.egg\trac\ticket\query.py", line 889, in display_html
    tickets = query.execute(req, db)
  File "C:\Python26\lib\site-packages\trac-0.11.6-py2.6.egg\trac\ticket\query.py", line 264, in execute
    self.num_items = self._count(sql, args, db)
  File "C:\Python26\lib\site-packages\trac-0.11.6-py2.6.egg\trac\ticket\query.py", line 249, in _count
    cursor.execute(count_sql, args);
  File "C:\Python26\lib\site-packages\trac-0.11.6-py2.6.egg\trac\db\util.py", line 65, in execute
    return self.cursor.execute(sql)
  File "C:\Python26\lib\site-packages\trac-0.11.6-py2.6.egg\trac\db\sqlite_backend.py", line 80, in execute
    PyFormatCursor.execute(self, *args)
  File "C:\Python26\lib\site-packages\trac-0.11.6-py2.6.egg\trac\db\sqlite_backend.py", line 59, in execute
    args or [])
  File "C:\Python26\lib\site-packages\trac-0.11.6-py2.6.egg\trac\db\sqlite_backend.py", line 51, in _rollback_on_error
    return function(self, *args, **kwargs)
OperationalError: near "references": syntax error

[Remy Blank]

I'll try to reproduce that. In the meantime, could you please try to log the actual SQL that is executed for that query? To do that, set the log level to DEBUG and set [trac] debug_sql = true in trac.ini.

[mrelbe <mikael@…>]

Replying to rblank:

I'll try to reproduce that. In the meantime, could you please try to log the actual SQL…

Here you go, a complete log with the interesting stuff in between dashes (I don't dare to hide anything…):

2010-01-21 18:51:14,888 Trac[main] DEBUG: Dispatching <Request "GET u'/query'">
2010-01-21 18:51:14,888 Trac[util] DEBUG: SQL: 'SELECT name FROM auth_cookie WHERE cookie=%s'
2010-01-21 18:51:14,888 Trac[util] DEBUG: args: ('7fcdbbf33d60c87517bec1a6f7ef3ef2',)
2010-01-21 18:51:14,888 Trac[util] DEBUG: prefetch: 1 rows
2010-01-21 18:51:14,888 Trac[web_ui] DEBUG: Updating session 7fcdbbf33d60c87517bec1a6f7ef3ef2 for user Mikael
2010-01-21 18:51:14,888 Trac[util] DEBUG: SQL: 'UPDATE auth_cookie SET time=%s WHERE cookie=%s'
2010-01-21 18:51:14,888 Trac[util] DEBUG: args: (1264096274, '7fcdbbf33d60c87517bec1a6f7ef3ef2')
2010-01-21 18:51:14,888 Trac[util] DEBUG: prefetch: 0 rows
2010-01-21 18:51:14,904 Trac[util] DEBUG: SQL: "SELECT name, value FROM system WHERE name IN ('repository_dir','youngest_rev')"
2010-01-21 18:51:14,904 Trac[util] DEBUG: prefetch: 2 rows
2010-01-21 18:51:14,904 Trac[util] DEBUG: SQL: 'SELECT name FROM auth_cookie WHERE cookie=%s'
2010-01-21 18:51:14,904 Trac[util] DEBUG: args: ('7fcdbbf33d60c87517bec1a6f7ef3ef2',)
2010-01-21 18:51:14,904 Trac[util] DEBUG: prefetch: 1 rows
2010-01-21 18:51:14,904 Trac[web_ui] DEBUG: Updating session 7fcdbbf33d60c87517bec1a6f7ef3ef2 for user Mikael
2010-01-21 18:51:14,904 Trac[util] DEBUG: SQL: 'UPDATE auth_cookie SET time=%s WHERE cookie=%s'
2010-01-21 18:51:14,904 Trac[util] DEBUG: args: (1264096274, '7fcdbbf33d60c87517bec1a6f7ef3ef2')
2010-01-21 18:51:14,920 Trac[util] DEBUG: prefetch: 0 rows
----
2010-01-21 18:51:14,920 Trac[chrome] DEBUG: Prepare chrome data for request
2010-01-21 18:51:14,936 Trac[util] DEBUG: SQL: u"SELECT COUNT(*) FROM (SELECT t.id AS id,t.summary AS summary,t.status AS status,t.type AS type,t.priority AS priority,t.milestone AS milestone,t.component AS component,t.reporter AS reporter,t.description AS description,t.time AS time,t.changetime AS changetime,priority.value AS priority_value,references.value AS references\nFROM ticket AS t\n  LEFT OUTER JOIN ticket_custom AS references ON (id=references.ticket AND references.name='references')\n  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)\nORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS int),t.id) AS foo"
2010-01-21 18:51:14,936 Trac[util] DEBUG: execute exception: OperationalError('near "references": syntax error',)
----
2010-01-21 18:51:14,936 Trac[session] DEBUG: Retrieving session for ID u'Mikael'
2010-01-21 18:51:14,936 Trac[util] DEBUG: SQL: 'SELECT last_visit FROM session WHERE sid=%s AND authenticated=%s'
2010-01-21 18:51:14,936 Trac[util] DEBUG: args: (u'Mikael', 1)
2010-01-21 18:51:14,936 Trac[util] DEBUG: prefetch: 1 rows
2010-01-21 18:51:14,936 Trac[util] DEBUG: SQL: 'SELECT name,value FROM session_attribute WHERE sid=%s and authenticated=%s'
2010-01-21 18:51:14,936 Trac[util] DEBUG: args: (u'Mikael', 1)
2010-01-21 18:51:14,936 Trac[util] DEBUG: prefetch: 16 rows
2010-01-21 18:51:14,936 Trac[main] ERROR: Internal Server Error: 
Traceback (most recent call last):
  File "C:\Python26\lib\site-packages\trac-0.11.6-py2.6.egg\trac\web\main.py", line 450, in _dispatch_request
    dispatcher.dispatch(req)
  File "C:\Python26\lib\site-packages\trac-0.11.6-py2.6.egg\trac\web\main.py", line 206, in dispatch
    resp = chosen_handler.process_request(req)
  File "C:\Python26\lib\site-packages\trac-0.11.6-py2.6.egg\trac\ticket\query.py", line 834, in process_request
    format, filename=filename)
  File "C:\Python26\lib\site-packages\trac-0.11.6-py2.6.egg\trac\mimeview\api.py", line 961, in send_converted
    content, selector)
  File "C:\Python26\lib\site-packages\trac-0.11.6-py2.6.egg\trac\mimeview\api.py", line 669, in convert_content
    output = converter.convert_content(req, mimetype, content, ck)
  File "C:\Python26\lib\site-packages\trac-0.11.6-py2.6.egg\trac\ticket\query.py", line 735, in convert_content
    return self.export_rss(req, query)
  File "C:\Python26\lib\site-packages\trac-0.11.6-py2.6.egg\trac\ticket\query.py", line 980, in export_rss
    results = query.execute(req, db)
  File "C:\Python26\lib\site-packages\trac-0.11.6-py2.6.egg\trac\ticket\query.py", line 264, in execute
    self.num_items = self._count(sql, args, db)
  File "C:\Python26\lib\site-packages\trac-0.11.6-py2.6.egg\trac\ticket\query.py", line 249, in _count
    cursor.execute(count_sql, args);
  File "C:\Python26\lib\site-packages\trac-0.11.6-py2.6.egg\trac\db\util.py", line 55, in execute
    r = self.cursor.execute(sql)
  File "C:\Python26\lib\site-packages\trac-0.11.6-py2.6.egg\trac\db\sqlite_backend.py", line 80, in execute
    PyFormatCursor.execute(self, *args)
  File "C:\Python26\lib\site-packages\trac-0.11.6-py2.6.egg\trac\db\sqlite_backend.py", line 59, in execute
    args or [])
  File "C:\Python26\lib\site-packages\trac-0.11.6-py2.6.egg\trac\db\sqlite_backend.py", line 51, in _rollback_on_error
    return function(self, *args, **kwargs)
OperationalError: near "references": syntax error

[mrelbe <mikael@…>]

When I perform a query using another custom ticket field, the SQL statement becomes identical except for the term "references". And those queries works… (I am not an SQL hero thus have no clue to this).

[Remy Blank]

It's amazing how many reserved words SQL defines, and coincidentally "references" is one of them: ​http://www.sqlite.org/lang_keywords.html

That page mentions double-quoting for identifiers, I wonder if this is portable across databases. We could use that for custom field names.

[Christian Boos]

Hm, I'm just working on a patch using backquotes quoting… in the middle of testing it.

[Christian Boos]

No, backquotes work well for SQLite, but with PostgreSQL I get:

ProgrammingError: syntax error at or near "."
LINE 1: ... changetime,priority.value AS priority_value,`foo`.value AS ...
                                                             ^

[Remy Blank]

From the page above, double quotes seem to be standard (or at least aren't mentioned as "non-standard").

[Christian Boos]

Double quotes are standard … except for MySQL. So we have:

DB	`...` quoting	"…" quoting
SQLite	yes	yes
MySQL	yes	no
PostgreSQL	no	yes

This is becoming painful, looks like we need another db helper…

[Christian Boos]

Add db.quote(ident) method to overcome differences in quoting syntax and use it for quoting custom field names used as identifiers in SQL queries for custom query module. Patch on r9055.

[mrelbe <mikael@…>]

Perhaps a less painful solution could be to define set of invalid names for custom-ticket attributes, and warn a Trac administrator in case such are defined. These names are not displayed to the user but merely an internal affair…

[Christian Boos]

Replying to mrelbe <mikael@…>:

Perhaps a less painful solution could be to define set of invalid names for custom-ticket attributes

Taking the whole list given above or even more comprehensive ones (e.g. ​List of SQL reserved words)? No, I don't think this is the way to go, if only because "references" sounds like a perfectly fine custom ticket field name to me ;-)

Any feedback on the patch?

[mrelbe <mikael@…>]

Replying to cboos:

No, I don't think this is the way to go, if only because "references" sounds like a perfectly fine custom ticket field name to me ;-)

I totally agree, however I felt the question had to be stated.

Any feedback on the patch?

I just tested the patch (by doing a temporary upgrade to 0.12dev r9055) and it looks good, thank you!

[Christian Boos]

Thanks for the feedback, patch applied in r9062.