SQL injection vulnerability/SQL compatibility

[anonymous]

The arguments to the SQL statements are not properly escaped. This results in a possibility of SQL injection, and also database compatibility issues.

Disclaimer - I'm not really python programmer so the attached patch may not be the optimal approach. However it does remove the % operator which is at the root of the SQL injection problem, and also removes the double quotes around the milestone value (which doesn't work with postgres 9.x).

[anonymous]

Fix

[Ryan J Ollos]

#3899 closed as a duplicate.

[Ryan J Ollos]

#3421 closed as a duplicate.

[Ryan J Ollos]

(In [13107]) Refs #8703, #7001: Optimize SQL queries and properly utilize the Trac database API to prevent SQL injection and improve cross-DB compatibility.

[Ryan J Ollos]

In 16701:

Tracticketstats 3.0.0dev: Make compatible with Trac 1.0+

The plugin no longer supports Trac < 1.0. Fixed SQL injection
issues.

Fixes #8703, #13231.

[Jun Omae]

SQL injection is still remained in query parameter of TicketStats macro.

01:03:40 PM Trac[formatter] DEBUG: Executing Wiki macro TicketStats by provider <ticketstats.macro.TicketStatsMacro object at 0x7fd174d30690>
01:03:40 PM Trac[util] DEBUG: SQL:
                SELECT t.type AS type, owner, status, time AS created
                FROM ticket t
                  INNER JOIN enum p ON p.name = t.priority
                WHERE p.type = 'priority' AND time <= %s AND t.id IN (SELECT t.id FROM ticket AS t
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
WHERE ((COALESCE(t.status,'')!=closed))
ORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS integer),t.id)

01:03:40 PM Trac[util] DEBUG: args: (1491365020926500L,)
01:03:40 PM Trac[util] DEBUG: execute exception: OperationalError('no such column: closed',)
01:03:40 PM Trac[formatter] ERROR: Macro TicketStats(query=status=!closed) failed for <Resource 'ticket'>:
Traceback (most recent call last):
  File "/venv/trac/1.0.15/lib/python2.5/site-packages/trac/wiki/formatter.py", line 795, in _macro_formatter
    return macro.ensure_inline(macro.process(args))
  File "/venv/trac/1.0.15/lib/python2.5/site-packages/trac/wiki/formatter.py", line 364, in process
    text = self.processor(text)
  File "/venv/trac/1.0.15/lib/python2.5/site-packages/trac/wiki/formatter.py", line 351, in _macro_processor
    text)
  File "/src/trac-hacks.org/tracticketstatsplugin/trunk/ticketstats/macro.py", line 248, in expand_macro
    last_num_open = self._get_num_open_tix(last_date, req, ticketFilter)
  File "/src/trac-hacks.org/tracticketstatsplugin/trunk/ticketstats/macro.py", line 195, in _get_num_open_tix
    """ % ticketFilter, (to_utimestamp(at_date),)):
  File "/venv/trac/1.0.15/lib/python2.5/site-packages/trac/db/api.py", line 124, in execute
    return db.execute(query, params)
  File "/venv/trac/1.0.15/lib/python2.5/site-packages/trac/db/util.py", line 128, in execute
    cursor.execute(query, params if params is not None else [])
  File "/venv/trac/1.0.15/lib/python2.5/site-packages/trac/db/util.py", line 61, in execute
    r = self.cursor.execute(sql_escape_percent(sql), args)
  File "/venv/trac/1.0.15/lib/python2.5/site-packages/trac/db/sqlite_backend.py", line 82, in execute
    result = PyFormatCursor.execute(self, *args)
  File "/venv/trac/1.0.15/lib/python2.5/site-packages/trac/db/sqlite_backend.py", line 60, in execute
    args or [])
  File "/venv/trac/1.0.15/lib/python2.5/site-packages/trac/db/sqlite_backend.py", line 52, in _rollback_on_error
    return function(self, *args, **kwargs)
OperationalError: no such column: closed