despam tickets

[pinsard]

Saxo tickets have been spamed due to ticket creation and modification possible for anonymous users. (This is not anymore possible !).

With sql commands I managed to get rid of some of these spams.

• First type of spam : a whole ticket (for example 55)

   $ ssh saxo@forge.ipsl.jussieu.fr
   [saxo@forge ~]$ psql saxo_trac
    saxo_trac=> begin;
   saxo_trac=> delete from ticket where id=55;
   saxo_trac=> commit;
  

• Second type of spam : a comment

I don't know I to select a comment according to the text but I know users name who are not involved in SAXO (for example 'Buy valtrex') :

 [saxo@forge ~]$ psql saxo_trac
 saxo_trac=> begin;
 saxo_trac=> delete from ticket_change where author ~* 'Buy valtrex';
 saxo_trac=> commit

Some comments do not have authors :

09/10/06 20:15:38 changed by
    *  cc changed from leonard@blader.com to margrett@blader.com.

http://kinky-mature-sluts.startspot.nl/ http://ladies-mature-and-sexy.startspot.nl/ http://ladies-shagging-mature.startspot.nl/ http://lesbian-mature-mpg.startspot.nl/

I scan all ticket_change with no author with tis command that show me the time element of the table :

select * from  ticket_change where author='';

for exemple

      3 | 1157912142 |        | cc      | dorothee@blader.com | susan@blader.com

So know I can remove this comment with

delete from ticket_change where time='1157912142';

I made several of these delete until the output of the select command was empty.