Apostrophe in milestone name caused OperationalError on /roadmap request

[Karpuscul@…]

1. Possible solution

To introduce escaping before injecting sql statements into cursor.execute() method

2. How to Reproduce

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

OperationalError: near "s": syntax error

Cause of the error is in the trachours web_ui.py module:

File "C:\Python26\lib\site-packages\trachoursplugin-0.5.2-py2.6.egg\trachours\web_ui.py", line 72, in filter_stream

Code fragment:

  67. hours[milestone.name] = dict(totalhours=0.,
  68. estimatedhours=0.,)
  69.
  70. db = self.env.get_db_cnx()
  71. cursor = db.cursor()
  72. cursor.execute("select id from ticket where milestone='%s'" % milestone.name)
  73. tickets = [i[0] for i in cursor.fetchall()]
  74.  
  75. if tickets:
  76. hours[milestone.name]['date'] = Ticket(self.env, tickets[0]).time_created
  77. for ticket in tickets:

Local variables:

Name	Value
cursor	<trac.db.util.IterableCursor object at 0x057A43D0>
data	{'abs_url_of': <function get_abs_url at 0x055F71B0>, 'all': <built-in ...
db	<trac.db.pool.PooledConnection object at 0x056DA788>
filename	'roadmap.html'
find_xpath	"li[@class='milestone']h2/a"
hours	{u"Somebody's milestone": {'estimatedhours': 0.0, 'totalhours': ...
method	'xhtml'
milestone	<trac.ticket.model.Milestone object at 0x05605CD0>
milestones	[<trac.ticket.model.Milestone object at 0x05605CD0>, ...
req	<Request "GET u'/roadmap'">
self	<trachours.web_ui.TracHoursRoadmapFilter object at 0x055EDDF0>
stream	<genshi.core.Stream object at 0x057A4430>
this_milestone	None
trachours	<trachours.hours.TracHoursPlugin object at 0x05594A90>
xpath	"li[@class='milestone']div[@class='info']"

The latest record in stack trace:

File "C:\Python26\lib\site-packages\trac-0.11.5-py2.6-win32.egg\trac\db\sqlite_backend.py", line 50, in _rollback_on_error

Code fragment:

  45. sqlite_version_string = '%d.%d.%d' % (_ver[0], _ver[1], int(_ver[2]))
  46.  
  47. class PyFormatCursor(sqlite.Cursor):
  48. def _rollback_on_error(self, function, *args, **kwargs):
  49. try:
  50. return function(self, *args, **kwargs)
  51. except sqlite.DatabaseError, e:
  52. self.cnx.rollback()
  53. raise
  54. def execute(self, sql, args=None):
  55. if args:

Local variables:

Name	Value
args	(u"select id from ticket where milestone='Somebody's milestone'", ...
e	OperationalError('near "s": syntax error',)
function	<method 'execute' of 'sqlite3.Cursor' objects>
kwargs	{}
self	<trac.db.sqlite_backend.PyFormatCursor object at 0x056EF4B0>

Runtime environment

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 (.NET CLR 3.5.30729)

System Information

Trac	0.11.5
Python	2.6.2 (r262:71605, Apr 14 2009, 22:40:02) [MSC v.1500 32 bit (Intel)]
setuptools	0.6c9
SQLite	3.5.9
pysqlite	2.4.1
Genshi	0.5.1
mod_python	3.3.2-dev-20080819
jQuery:	1.2.6

Python Traceback

Traceback (most recent call last):
  File "C:\Python26\lib\site-packages\trac-0.11.5-py2.6-win32.egg\trac\web\main.py", line 444, in _dispatch_request
    dispatcher.dispatch(req)
  File "C:\Python26\lib\site-packages\trac-0.11.5-py2.6-win32.egg\trac\web\main.py", line 226, in dispatch
    data, content_type)
  File "C:\Python26\lib\site-packages\trac-0.11.5-py2.6-win32.egg\trac\web\chrome.py", line 737, in render_template
    stream |= self._filter_stream(req, method, filename, stream, data)
  File "C:\Python26\lib\site-packages\genshi-0.5.1-py2.6-win32.egg\genshi\core.py", line 128, in __or__
    return Stream(_ensure(function(self)), serializer=self.serializer)
  File "C:\Python26\lib\site-packages\trac-0.11.5-py2.6-win32.egg\trac\web\chrome.py", line 840, in inner
    data)
  File "C:\Python26\lib\site-packages\trachoursplugin-0.5.2-py2.6.egg\trachours\web_ui.py", line 72, in filter_stream
    cursor.execute("select id from ticket where milestone='%s'" % milestone.name)
  File "C:\Python26\lib\site-packages\trac-0.11.5-py2.6-win32.egg\trac\db\util.py", line 60, in execute
    return self.cursor.execute(sql)
  File "C:\Python26\lib\site-packages\trac-0.11.5-py2.6-win32.egg\trac\db\sqlite_backend.py", line 58, in execute
    args or [])
  File "C:\Python26\lib\site-packages\trac-0.11.5-py2.6-win32.egg\trac\db\sqlite_backend.py", line 50, in _rollback_on_error
    return function(self, *args, **kwargs)
OperationalError: near "s": syntax error

[Ryan J Ollos]

Is this plugin being maintained? I'd like to offer to apply some patches if the maintainer is okay with that. I know that k0s is gone, and see some checkins in the timeline by ejucovy.

The fix described here is correct, you can read more about it at ​t:wiki:TracDev/DatabaseApi#RulesforDBAPIUsage.

I've reproduced the issue locally, and tested out a fix. The fix is simple and pretty clear cut as far as I can tell ... I hope I am not being too presumptuous, but I am just going to go ahead and commit this and hope the maintainer (if exists) is okay with that.

[Ryan J Ollos]

(In [9551]) Properly escape SQL string passed to cursor.execute. Fixes #6449.