flipturn.org - stuff. things. other stuff.
dvorak | pantera | hacks

DeSpamTrac

I foolishly didn't require that users register and login to my Trac instance in order to update bugs. Unfortunately, spammers didn't miss the opportunity, and while I was on vacation a lot of comment spam built up.

So, after closing that loophole in my Trac configuration, I set about to fix all the problems the spammers caused. Fortunately, the ticket change table in the mysql database maintained the history, so it was possible to undo all the changes. It would have been a pain to do it by hand, so I spent a few hours writing and testing a little script to undo all the changes.

I'm sure you'll have to modify this to suit you; for my purposes, I could guarantee that every ticket change after a certain date was spam. To fix spam before that date, I had to update the database by hand, but that was a very small number of cases.

Back up your database before you do this! The following script is without warranty and may cause untold destruction. But, on the other hand, it might be useful:

#!/usr/bin/env python

from MySQLdb import *

from sets import Set as set

class DeSpam(object):
    def __init__(self, host, user, passwd, db):
        self.db = connect(host=host, user=user, passwd=passwd, db=db)
        self.c = self.db.cursor()

    def get_record(self, id, date=None):
        self.c.execute("select * from ticket_change where ticket=%d and time>=%d" % (id, date))
        r = self.c.fetchall()
        orig = {}
        times = set()
        if len(r)>0:
            records = list(r)
            # reverse the order of the records so we can back out each change and get to the original data
            records.reverse()
            for record in records:
                #print record
                field = record[3]
                orig[field] = record[4]
                times.add(record[1])
            print "record %d: reset to %s" % (id, orig)
        return orig, list(times)

    def delete(self, id, times):
        for time in times:
            query = "delete from ticket_change where ticket=%d and time=%d" % (id, time)
            print query
            self.c.execute(query)
            r = self.c.fetchall()
            print r

    def revert_record(self, id, orig, times):
        query = 'update ticket set %s where id=%d'
        wheres = []
        values = []
        for field in ['component', 'severity', 'priority', 'owner',
                       'reporter', 'cc', 'version', 'milestone', 'status',
                       'resolution', 'summary', 'keywords', 'description']:
            if field in orig:
                wheres.append("%s=%%s" % field)
                values.append(orig[field])
        query = 'update ticket set %s where id=%d' % (', '.join(wheres), id)
        print query
        print values
        self.c.execute(query, values)
        r = self.c.fetchall()
        print r
        self.delete(id, times)
    
    def get_max(self):
        self.c.execute("select max(ticket) from ticket_change")
        r = self.c.fetchall()
        print r
        val = r[0][0]
        return int(val)

    def loop_all(self, badtime, start=1, end=-1):
        if end<start:
            end = self.get_max()
        for i in range(start, end+1):
            changes, times = self.get_record(i, badtime)
            if changes:
                print "SPAM in %d: revert to %s" % (i, changes)
                self.revert_record(i, changes, times)

d = DeSpam("your.host.name", "yourusername", "yourpassword", "yourdatabase")
badtime = 1178605395 # I cheated: I looked in my database for the first spammy timestamp.  Otherwise, use gmtime
d.loop_all(badtime)
erection pills california erection pills california erection pharmacy erection pharmacy
free sample pack levitra levitra over the counter free sample pack levitra cialis online