Edgewall Software
Modify

Opened 13 years ago

Closed 13 years ago

#8987 closed defect (fixed)

OperationalError: near "references": syntax error

Reported by: mrelbe <mikael@…> Owned by: Christian Boos
Priority: normal Milestone: 0.12
Component: database backend Version: 0.11.6
Severity: normal Keywords:
Cc: Branch:
Release Notes:
API Changes:
Internal Changes:

Description

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

Attachments (1)

t8987-quote-custom-fields.diff (8.4 KB ) - added by Christian Boos 13 years ago.
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.

Download all attachments as: .zip

Change History (14)

comment:1 by Remy Blank, 13 years ago

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.

in reply to:  1 comment:2 by mrelbe <mikael@…>, 13 years ago

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

comment:3 by mrelbe <mikael@…>, 13 years ago

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

comment:4 by Remy Blank, 13 years ago

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.

comment:5 by Remy Blank, 13 years ago

Component: ticket systemdatabase backend
Milestone: 0.12
Owner: set to Remy Blank

comment:6 by Christian Boos, 13 years ago

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

comment:7 by Christian Boos, 13 years ago

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 ...
                                                             ^

comment:8 by Remy Blank, 13 years ago

Owner: changed from Remy Blank to Christian Boos

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

comment:9 by Christian Boos, 13 years ago

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…

Last edited 13 years ago by Christian Boos (previous) (diff)

by Christian Boos, 13 years ago

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.

comment:10 by mrelbe <mikael@…>, 13 years ago

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…

in reply to:  10 ; comment:11 by Christian Boos, 13 years ago

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?

in reply to:  11 comment:12 by mrelbe <mikael@…>, 13 years ago

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!

comment:13 by Christian Boos, 13 years ago

Resolution: fixed
Status: newclosed

Thanks for the feedback, patch applied in r9062.

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Christian Boos.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from Christian Boos to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.