Hi guys,
I managed to do what I wanted. I am just writing back to report the
results of my endeavors ;-)
The problem we had - an installation of Trac 0.8.4 with world wide
ticket create & change permissions granted. We got a heavy spam attack -
many tickets generated pure spam, and many spam changes done to the
current database - more than 12000 of them.
With some programming in Python and sqlite's SQL dialect, I managed to
fix things. As mentioned previously in this thread - there is already a
plugin to prevent spam and another to undo spammers' changes. However
they demand a higher version of Trac + they are suited to the occasional
problem, not a 12000 spammers' changes disaster.
The solution given below works more or less fine for the purposes
outlined above.
Drawbacks:
1. Code quality sucks - sorry, I am very new to Python.
2. Things are very unoptimized. I suppose that some indexes can improve
the execution time. However - a faster disk might turn out to be a
better overall solution.
3. Trac tickets' change workflow is not kept. This means: there are
changes in the fields of a ticket that are impossible to be done via the
web interface together, but are done via the scripts. However - the
strictness of the current.new_value=future.old_value in the table
ticket_change is kept where necessary (i.e. it is not kept for comments
which always have old_value='').
4. The code depends on pysqlite2, there are no plans to make it work
with pysqlite 1.x.
OK. Here goes my solution:
1. First we determine the pure spam tickets - these are tickets that
started their lives as spam. They will be deleted and regenerated with
completely new history.
We do this with prepare.sql. The numbers in the where clauses in SQL are
the numbers of the spam tickets.
2. We generate a new table - called LARGE based on TICKET and
TICKET_CHANGE. It corresponds to all states of the tickets. We do this
with the script: expander.py
3. We start deleting those states of the tickets that we consider spam.
One example of this is given in deleter.sql. Naturally - your
deleter.sql can be very different.
4. Based on LARGE we generate NEW_TICKET & NEW_TICKET_CHANGE - which are
analogues of TICKET & TICKET_CHANGE with the script: shrinker.py
5. We fix TICKET & TICKET_CHANGE and the expansion of the database file
via the script collect.sql.
After all of this we get happy and reach nirvana. We hate the spammers
that stole our precious[1].
Kind regards:
al_shopov
[1] time
delete from large where author!='anonymous' and author in
(
select distinct author from large where
comment glob '*coachbrand*' or
comment glob '*diorbrand*' or
comment glob '*bizland*' or
comment glob '*balearweb*' or
comment glob '*furniture.int.pl*' or
comment glob '*virtualave*' or
comment glob '*lacomunitat*' or
comment glob '*freett*' or
comment glob '*blog.gala.net*' or
comment glob '*quick-adult-links.com*' or
comment glob '*ringtones*' or
comment glob '*myrolex*' or
comment glob '*bitacoras*' or
comment glob '*viagra*' or
comment glob '*xanax*'
);
delete from large where
comment glob '*coachbrand*' or
comment glob '*diorbrand*' or
comment glob '*bizland*' or
comment glob '*balearweb*' or
comment glob '*furniture.int.pl*' or
comment glob '*virtualave*' or
comment glob '*lacomunitat*' or
comment glob '*freett*' or
comment glob '*blog.gala.net*' or
comment glob '*quick-adult-links.com*' or
comment glob '*ringtones*' or
comment glob '*myrolex*' or
comment glob '*bitacoras*' or
comment glob '*viagra*' or
comment glob '*xanax*' or
comment glob '*hcy0515*' or
comment glob '*jipiao365*' or
comment glob '*LED*' or
comment glob '*World of Warcraft*' or
comment glob '*www.bogoo.da.ru*' or
comment glob '*insurance.mpage.jp*' or
comment glob '*www.hot.es.pn*' or
comment glob '*www.vipp2p.ch.gg*' or
comment glob '*www.divx.cn.pn*' or
comment glob '*p2p.au.ms*' or
comment glob '*www.joa.hk.ms*'
;
delete from large where id=319 and changetime=1149408700;
delete from large where id=321 and changetime=1148984261;
#!/usr/bin/python
# -*- coding: utf-8 -*-
from pysqlite2 import dbapi2 as sqlite
#import apis
#connect & get cursor
con = sqlite.connect("/home/ash/gtp-clean/db/trac.db")
cur = con.cursor()
# try to drop the tab, if not - pаss
try:
cur.execute ("drop table large")
except sqlite.OperationalError:
pass
try:
cur.execute ("drop table new_ticket_change")
except sqlite.OperationalError:
pass
try:
cur.execute ("drop table new_ticket")
except sqlite.OperationalError:
pass
# create da temp table
LARGE="""CREATE TABLE large (
id integer, -- PRIMARY KEY,
time integer, -- the time it was created
changetime integer,
component text,
severity text,
priority text,
owner text, -- who is this ticket assigned to
reporter text,
cc text, -- email addresses to notify
url text, -- url related to this ticket
version text, --
milestone text, --
status text,
resolution text,
summary text, -- one-line summary
description text, -- problem description (long)
keywords text,
comment text,
author text
);
"""
cur.execute (LARGE)
con.commit()
# create new_ticket_change
NTC="""CREATE TABLE new_ticket_change (
ticket integer,
time integer,
author text,
field text,
oldvalue text,
newvalue text
);
"""
cur.execute (NTC)
con.commit()
NT="""CREATE TABLE new_ticket (
id integer ,
time integer, -- the time it was created
changetime integer,
component text,
severity text,
priority text,
owner text, -- who is this ticket assigned to
reporter text,
cc text, -- email addresses to notify
url text, -- url related to this ticket
version text, --
milestone text, --
status text,
resolution text,
summary text, -- one-line summary
description text, -- problem description (long)
keywords text
);
"""
cur.execute (NT)
con.commit()
# initail populating with most current state of tickets
cur.execute ("insert into large select t.*, '', '' from ticket t")
#make all of the rows special
cur.execute ("update large set changetime=0")
con.commit()
#get the ticket numbers
cur.execute("select distinct ticket from ticket_change order by ticket")
tts=cur.fetchall()
for (ti,) in tts:
#get changes per ticket
cur.execute("select time, author, field, oldvalue, newvalue from ticket_change where ticket=? order by time", (ti,))
changes=cur.fetchall()
#get unique changetimes
times=list(set([x[0] for x in changes]))
times.sort()
maxtime=times[-1]
mintime=times[0]
times.reverse()
#go back in time
for time in times:
# get the chages
cur_changes=[x for x in changes if x[0]==time]
# get the author
author=cur_changes[0][1]
# if there is a special row with changetime=0 -> fix it
cur.execute("update large set changetime=?,author=?,comment=? where id=? and changetime=0",(time,author,u'',ti))
# check cur state, if error -> log
for change in cur_changes:
if change[2]=='comment':
cur.execute("update large set comment=? where id=? and changetime=?",(change[4],ti,time,))
else:
cur.execute("select " + change[2] + " from large where id=? and changetime=?", (ti,time,))
values=cur.fetchall()
if values[0][0]!=change[4]:
print "ERROR", change
#make a special row with changetime=0
cur.execute("insert into large ( id, time, changetime, component, severity, priority, owner,reporter, cc, url, version,milestone,status, resolution, summary, description, keywords, comment, author) select id, time, 0, component, severity, priority, owner,reporter, cc, url, version,milestone,status, resolution, summary, description, keywords, '', '' from large where id=? and changetime = (select min (l.changetime ) from large l where l.id=?)",(ti,ti,))
#do oldvalue changes
for change in cur_changes:
cur.execute("update large set "+change[2]+"=? where id=? and changetime=0",(change[3],ti))
# the last special row has the following as zero strings
cur.execute("update large set author='', comment='' where id=? and changetime=0",(ti,))
con.commit()
print ti
delete from ticket;
delete from ticket_change;
insert into ticket select * from new_ticket;
insert into ticket_change select * from new_ticket_change;
insert into ticket select id, time, time+1, 'spam', 'trivial', 'lowest', 'ash', 'spammer', '', '', '', '', 'closed', 'invalid', 'spam', 'spam', 'spam' from spam_ticket;
insert into ticket_change select id, time+1, 'ash', 'status', 'new', 'closed' from spam_ticket;
insert into ticket_change select id, time+1, 'ash', 'resolution', '' , 'invalid' from spam_ticket;
drop table new_ticket;
drop table spam_ticket;
drop table new_ticket_change;
drop table spam_ticket_change;
drop table large;
vacuum;
create table spam_ticket as select * from ticket where id in (
337, 343, 344, 345, 346, 350, 351, 353, 354, 355, 358, 359,
360, 361, 362, 363, 364, 365, 366, 367, 368, 369, 370, 371,
372, 373, 374, 375, 376, 377, 378, 381, 382, 383, 384, 385,
386, 387, 388, 389, 390, 391, 392, 393, 394, 395, 396, 397,
398, 399, 400, 401, 402, 403, 410, 411, 415, 416, 417, 418,
419, 420);
create table spam_ticket_change as select * from ticket_change where ticket in (
337, 343, 344, 345, 346, 350, 351, 353, 354, 355, 358, 359,
360, 361, 362, 363, 364, 365, 366, 367, 368, 369, 370, 371,
372, 373, 374, 375, 376, 377, 378, 381, 382, 383, 384, 385,
386, 387, 388, 389, 390, 391, 392, 393, 394, 395, 396, 397,
398, 399, 400, 401, 402, 403, 410, 411, 415, 416, 417, 418,
419, 420);
delete from ticket where id in (
337, 343, 344, 345, 346, 350, 351, 353, 354, 355, 358, 359,
360, 361, 362, 363, 364, 365, 366, 367, 368, 369, 370, 371,
372, 373, 374, 375, 376, 377, 378, 381, 382, 383, 384, 385,
386, 387, 388, 389, 390, 391, 392, 393, 394, 395, 396, 397,
398, 399, 400, 401, 402, 403, 410, 411, 415, 416, 417, 418,
419, 420);
delete from ticket_change where ticket in (
337, 343, 344, 345, 346, 350, 351, 353, 354, 355, 358, 359,
360, 361, 362, 363, 364, 365, 366, 367, 368, 369, 370, 371,
372, 373, 374, 375, 376, 377, 378, 381, 382, 383, 384, 385,
386, 387, 388, 389, 390, 391, 392, 393, 394, 395, 396, 397,
398, 399, 400, 401, 402, 403, 410, 411, 415, 416, 417, 418,
419, 420);
#!/usr/bin/python
# -*- coding: utf-8 -*-
from pysqlite2 import dbapi2 as sqlite
#import apis
#connect & get cursor
con = sqlite.connect("/home/ash/gtp-clean/db/trac.db")
cur = con.cursor()
#get the ticket numbers
cur.execute("select distinct id from large order by id")
tts=cur.fetchall()
names=('id', 'time', 'changetime', 'component', 'severity', 'priority', 'owner', 'reporter', 'cc', 'url', 'version', 'milestone', 'status', 'resolution', 'summary', 'description', 'keywords', 'comment', 'author', )
names_end=len(names)-2
for (ti,) in tts: #[(311,)]: # tts:
#get states per ticket
cur.execute("select * from large where id=? order by changetime", (ti,))
states=cur.fetchall()
#print "states", states
if len(states)==1:
print ti, "pass"
else:
for k in range(1, len(states)):
for ind in range(3, names_end):
if states[k][ind]!=states[k-1][ind]:
cur.execute("insert into new_ticket_change values (?, ?, ?, ?, ?, ?)",(ti,states[k][2],states[k][-1],names[ind],states[k-1][ind],states[k][ind]))
if states[k][-2]!=u'':
cur.execute("insert into new_ticket_change values (?, ?, ?, ?, ?, ?)",(ti,states[k][2],states[k][-1],'comment','',states[k][-2]))
con.commit()
con.commit()
print ti
for (ti,) in tts:
cur.execute("""insert into new_ticket
select l.id, l.time, l.changetime, l.component,
l.severity, l.priority, l.owner, l.reporter,
l.cc, l.url, l.version, l.milestone, l.status,
l.resolution, l.summary, l.description, l.keywords
from large l
where l.id=? and l.changetime= (
select max(g.changetime)
from large g
where g.id=?)
""", (ti,ti,))
cur.execute("update new_ticket set changetime=time where changetime=0")
con.commit()
_______________________________________________
Trac mailing list
Trac@lists.edgewall.com
http://lists.edgewall.com/mailman/listinfo/trac
