Opened 5 years ago

Closed 4 years ago

#6449 closed defect (fixed)

Apostrophe in milestone name caused OperationalError on /roadmap request

Reported by: Karpuscul@… Owned by: rjollos
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 4 years ago by rjollos

  • Cc ejucovy added
  • Owner changed from k0s to rjollos

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 4 years ago by rjollos

  • Resolution set to fixed
  • Status changed from new to closed

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

Add Comment

Modify Ticket

as closed .
as The resolution will be set. Next status will be 'closed'.
to The owner will be changed from rjollos. Next status will be 'closed'.
The resolution will be deleted. Next status will be 'reopened'.

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

Note: See TracTickets for help on using tickets.