TurboMail Trac flooded with spam.

[amcgregor]

The TurboMail Trac has been flooded by spam. To prevent this in future please see the "Using Trac" section of the WikiStart page.

[amcgregor]

Removed unwanted tickets using the following SQL over Trac's SQLite database::

delete from ticket where id not in();
delete from ticket_custom where ticket not in();
delete from ticket_change where ticket not in();

Inserting a comma-separated list of valid ticket IDs.

[amcgregor]

Removed unwanted comment spam, after correcting modified properties, using the following. I wrote a list of spammed ticket IDs, one per line, and added to each line a space-separated list of comment numbers. For example:

12 1 2 3 4
13 1 2 3
15 1 2 3
20 1 2 3 4

Then I searched for:

([0-9]+)(( ([0-9]+))+)

Replaced it with the regular expression:

delete from ticket_change where ticket=$1 and time in(select time from ticket_change where ticket=$1 and field='comment' and oldvalue in ($2));

Which turns the above sample input into:

delete from ticket_change where ticket=12 and time in(select time from ticket_change where ticket=12 and field='comment' and oldvalue in ( 1 2 3 4));
delete from ticket_change where ticket=13 and time in(select time from ticket_change where ticket=13 and field='comment' and oldvalue in ( 1 2 3));
delete from ticket_change where ticket=15 and time in(select time from ticket_change where ticket=15 and field='comment' and oldvalue in ( 1 2 3));
delete from ticket_change where ticket=20 and time in(select time from ticket_change where ticket=20 and field='comment' and oldvalue in ( 1 2 3 4));

Adding commas to separate the comment numbers, I ran the SQL through Trac's SQLite database.

[amcgregor]

Forgot to include hidden (completed and ignored) tickets in the ticket culling. Backing up this version, restoring pre-culling backup, and re-culling for the hidden tickets. I'll then export the SQL from the three ticket tables, restore to this version, and re-import the tickets. D'oh! (I hope ticket #62 isn't one of the hidden ones!)

[amcgregor]

Correction: I mean ticket #61. This ticket.

[amcgregor]

Spam correction complete.