Opened 7 years ago

Closed 6 years ago

#6449 closed defect (fixed)

Apostrophe in milestone name caused OperationalError on /roadmap request

Reported by: Karpuscul@… Owned by: Ryan J Ollos
Priority: high Component: TracHoursPlugin
Severity: major Keywords: select sql injection web_ui filter_stream cursor
Cc: Karpuscul@…, ejucovy Trac Release: 0.11


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 module:

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

Code fragment:

  67. hours[] = dict(totalhours=0.,
  68. estimatedhours=0.,)
  70. db = self.env.get_db_cnx()
  71. cursor = db.cursor()
  72. cursor.execute("select id from ticket where milestone='%s'" %
  73. tickets = [i[0] for i in cursor.fetchall()]
  75. if tickets:
  76. hours[]['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\", line 50, in _rollback_on_error

Code fragment:

  45. sqlite_version_string = '%d.%d.%d' % (_ver[0], _ver[1], int(_ver[2]))
  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: 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\", line 444, in _dispatch_request
  File "C:\Python26\lib\site-packages\trac-0.11.5-py2.6-win32.egg\trac\web\", line 226, in dispatch
    data, content_type)
  File "C:\Python26\lib\site-packages\trac-0.11.5-py2.6-win32.egg\trac\web\", 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\", 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\", line 840, in inner
  File "C:\Python26\lib\site-packages\trachoursplugin-0.5.2-py2.6.egg\trachours\", line 72, in filter_stream
    cursor.execute("select id from ticket where milestone='%s'" %
  File "C:\Python26\lib\site-packages\trac-0.11.5-py2.6-win32.egg\trac\db\", 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\", line 58, in execute
    args or [])
  File "C:\Python26\lib\site-packages\trac-0.11.5-py2.6-win32.egg\trac\db\", line 50, in _rollback_on_error
    return function(self, *args, **kwargs)
OperationalError: near "s": syntax error

Attachments (0)

Change History (2)

comment:1 Changed 6 years ago by Ryan J Ollos

Cc: ejucovy added
Owner: changed from Jeff Hammel to 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.

comment:2 Changed 6 years ago by Ryan J Ollos

Resolution: fixed
Status: newclosed

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

Modify Ticket

as closed The owner will remain Ryan J Ollos.
The resolution will be deleted. Next status will be 'reopened'.

Add Comment

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

Note: See TracTickets for help on using tickets.