Combatre el spam en una mediawiki

De wikijoan
Salta a la navegació Salta a la cerca

Introducció

Tinc spam en algunes pàgines. Per localitzar-les, puc anar a Pàgines especials > pàgines amb més canvis. Pot ser pàgines que ja existeixen, o pàgines que s'han creat de nou (n'hi ha dues). A més, els spammers creen usuaris: 

mysql> select user_name, user_real_name from user;
+-------------+----------------+
| user_name   | user_real_name |
+-------------+----------------+
| Joan        | Joan Quintana  | 
| Polygamists |                | 
| Sergi       | Sergi Pérez   | 
| Newacct     |                | 
| Robin       | rb             | 
+-------------+----------------+
5 rows in set (0.00 sec)

Llegir amb calma:

Ara ja en tinc 96 d'usuaris.

En el llistat d'usuaris (Pàgines Especials) tinc fins a 96 usuaris

L'extensió ConfirmEdit necessita la versió 1.11 de la Mediawiki, i jo tinc instal.lada la versió 1.9. Per tant, ho deixaré per a TODO del nou servidor.

Netejar wikijoan (embrutat pel spam, octubre 2011

Coses que hauré de fer quan migri al nou servidor:

  • netejar la base de dades mysql i eliminar el rastre d'activitat d'intrusos
    • eliminar usuaris; select user_id,user_name, user_real_name from user;
    • eliminar revisions que no siguin les meves:
 select count(*) from revision;
236732
 select count(*) from revision where rev_user=1;
6050

per tant, només la quarta part de les revisions són meves!! 

select rev_user, count(*) from revision group by rev_user;

+----------+----------+
| rev_user | count(*) |
+----------+----------+
|        0 |   230604 | 
|        1 |     6050 | 
|        3 |        5 | 
|        4 |        2 | 
|        5 |        1 |

La majoria de les revisions les ha fet l'usuari 0, l'anònim. Són les pàgines que s'han modificat sense estar protegides. Moltes d'aquestes modificacions són de fet meves, sempre que es modifica un article sense haver d'estar logat. (s'executa un script que bloca les pàgines). Per tant, el principal problema que tinc és que es pot editar la wiki sense estar registrat.

Per exemple, si jo vull veure les revisions que ha tingut la wiki el dia 12-oct-2011: 

select rev_id, rev_page, rev_user, rev_user_text, rev_timestamp from revision where rev_timestamp like '20111012%';

i aquest dia hi ha hagut 3900 revisions, de les quals les meves són 

select rev_id, rev_page, rev_user,rev_user_text, rev_timestamp from revision where rev_timestamp like '20111012%' and rev_user=1;
1!!

Per tant, cada dia estic tenint de l'ordre de 3500 revisions!! i era de l'ordre de 200 revisions ara fa un any!! I les revisions són de l'usuari anònim.

Això fa que el tamany de la base de dades creix molt, i que a més quan he volgut executar els procediments per actualitzar les estadístiques se m'hagi penjat el mysql. Això fa també que la gràfica d'estadístiques és incorrecta.

per saber el text que s'ha afegit: 

select rev_id, rev_page, rev_user, rev_timestamp, rev_text_id from revision where rev_timestamp like '20111012%';
select * from text where old_id=253126;
+--------+---------------------------------------------------------------------------------------------------+-----------+
| old_id | old_text                                                                                          | old_flags |
+--------+---------------------------------------------------------------------------------------------------+-----------+
| 253126 | ydcgyxjljkpbo, http://carrentalshedder.com/SunnyvaleCarRental.html Sunnyvale Car Rental, RzlbYRj. | utf-8     | 
+--------+---------------------------------------------------------------------------------------------------+-----------+

select old_text  from revision r, text t where r.rev_text_id= t.old_id and r.rev_timestamp like '20111012%' and r.rev_user=0 limit 5;
select old_text  from revision r, text t where r.rev_text_id= t.old_id and r.rev_timestamp like '20111012%' and r.rev_user=1 limit 5;

per tant, en teoria puc localitzar les pàgines que s'han tocat i esborrar les revisions. Ara bé, compte!! perquè jo mateix puc editar una pàgina sense autenticar-me (si em deixa). 

select rev_id, rev_page, rev_user, rev_user_text, rev_timestamp from revision where rev_user=0 and rev_user_text='Joan'; -> no n'hi ha, però per exemple:

select rev_id, rev_page, rev_user, rev_user_text, rev_timestamp from revision where rev_user=0 and rev_user_text like '127.0%';

puc mirar quin és el patró que segueix quan edito una pàgina sense autenticar-me, quina IP... Una cosa que es pot fer és Pàgines Especials > Totes les pàgines, i anar una per una... però és un curro.

L'objectiu és que estigui a 0 files el valor de: 

select rev_id, rev_page, rev_user, rev_user_text, rev_timestamp from revision where rev_timestamp like '20111012%' and rev_user=0;

Per saber per exemple les pàgines que tinc malament, veig que en l'anterior select s'ha tocat la rev_page=998: 

mysql> select page_title from page where page_id=998;
+-----------------------------------+
| page_title                        |
+-----------------------------------+
| Alternatives_al_SQL*Plus:_gqlplus | 
+-----------------------------------+

però compte! perquè veig que aquesta pàgina està bé i que des del 2009 no s'ha tocat...

Potser és millor mirar la taula recentchanges -> no!! la taula recentchanges es va buidant periòdicament 

select * from recentchanges where rc_timestamp like '20111012%' and rc_user=0;

i el que veig és que en una pàgina com l'anterior el que s'ha fet és ficar un rc_comment. Com que jo mai fico rc_comments, aquesta podria ser una manera de detectar les pàgines que s'han tocat (encara que ara veig que la majoria de les vegades no canvia el contingut...) 

select rc_title, rc_comment from recentchanges limit 10;

Però no veia el canvi perquè en realitat el que ha canviat és la pestanya Discussió. El problema és que es pot editar Discussió sense estar registrat.

Llocs on veure els canvis que han fet els intrusos:

  • Pàgines especials > canvis recents
  • Pàgines especials > Llista adreces IP i noms d'usuari blocats
  • Pàgines especials > Llistat d'usuaris
  • Pàgines especials > Logs
  • Pàgines especials > Totes les pàgines

canvis de l'usuari 0 en un dia concret: 

select rev_id, rev_page, rev_user,rev_user_text, rev_timestamp from revision where rev_timestamp like '20111013%' and rev_user=0;

select rc_id, rc_timestamp, rc_user, rc_cur_id, rc_this_oldid, rc_last_oldid, rc_title, rc_type, rc_comment from recentchanges where rc_timestamp like '20111013%' and rc_user=0 limit 5;

Amb rc_this_oldid, rc_last_oldid podem seguir la pista dels canvis

Llista de totes les revisions d'una pàgina (per exemple, la page_id=998) 

select p.page_id, p.page_title, r.rev_id, r.rev_text_id,r.rev_user, r.rev_timestamp from page p, revision r where p.page_id=r.rev_page and p.page_id=998;

Em dóna la burrada de 78000 revisions

Si ara vull saber el text que s'ha introduït en aquestes revisions: 

select p.page_id, p.page_title, r.rev_id, r.rev_text_id,r.rev_user, r.rev_timestamp, t.old_text from page p, revision r, text t where p.page_id=r.rev_page and r.rev_text_id=t.old_id and p.page_id=998 limit 5;

i la última revisió que s'ha fet sobre la pàgina 998: 

select p.page_id, p.page_title, r.rev_id, r.rev_text_id,r.rev_user, r.rev_timestamp, t.old_text from page p, revision r, text t where p.page_id=r.rev_page and r.rev_text_id=t.old_id and p.page_id=998 order by r.rev_timestamp desc limit 1;

i efectivament el text que m'apareix és el que hi ha a la pàgina (però no en l'edició de la pàgina, sinó en Discussió). Jo sé que la última revisió està malament, però jo vull saber quina és la última revisió bona. Però curiosament per a la page_id=998 em mostra que l'usuari 1 no ha fet revisions, quan l'historial em diu que n'ha fet 4. 

select distinct r.rev_user from revision r where r.rev_page=998; -> de fet només l'usuari 0 ha fet canvis sobre la pàgina 998

Potser la informació està a recentchanges, però com diu el seu nom, recentchanges és una taula que es va netejant i per tant no acumula tot l'historial.

Per eliminar el tab Discussió de totes les pàgines, faig 

$ joe ~/wiki/skins/monobook$ joe main.css

i fico: 

        /* offsets to distinguish the tab groups */
        li#ca-talk {
                /* margin-right: 1.6em; */
                display:none !important;      
        }

Efectivament, desapareix el tab Discussion, però això no vol dir que la funcionalitat Discussion hagi desaparegut i que els motors de cerca no puguin accedir-hi:

Important! jo estic utilitzant la versió 1.9, on en la taula page hi ha el camp page_restrictions. Però en les versions 1.10 i superiors hi ha la taula page_restrictions:

i és el que s'haurà de tenir en compte per tal d'impedir que la gent editi o faci discussions (talk) sobre una pàgina.

Per tal d'impedir que la gent pugui editar, a part del mètode que utilitzi (update), hi ha un mètode més oficial explicat a:

The recommended method is by changing the value of the $wgGroupPermissions configuration option. Edit LocalSettings.php and add the line: 

$wgGroupPermissions['*']['edit'] = false;

i tal com està explicat a http://assela.pathirana.net/Mediawiki_for_personal_webs, la solució per tal que ningú, ni els usuaris registrats, puguin fer discussions seria: 

$wgGroupPermissions['user']['talk'] = false;

Per tant, afegeixo aquestes dues línies a Localsettings.php.

(si em registro com a usuari joan continuo podent editar la pàgina http://wiki.joanillo.org/index.php?title=Discussi%C3%B3:Alternatives_al_SQL*Plus:_gqlplus&action=edit).

Solució: Però ja veig el que passa! la pàgina 998 és la de Discussió, i la pàgina 233 és la d'Edició, i es poden separar mirant el page_namespace: 

select page_id, page_namespace,page_title,page_touched from page where page_title='Alternatives_al_SQL*Plus:_gqlplus';

mysql> select page_id, page_namespace,page_title,page_touched from page where page_title='Alternatives_al_SQL*Plus:_gqlplus';
+---------+----------------+-----------------------------------+----------------+
| page_id | page_namespace | page_title                        | page_touched   |
+---------+----------------+-----------------------------------+----------------+
|     233 |              0 | Alternatives_al_SQL*Plus:_gqlplus | 20111013010908 | 
|     998 |              1 | Alternatives_al_SQL*Plus:_gqlplus | 20111013012038 | 
+---------+----------------+-----------------------------------+----------------+

Per tant, si vull veure l'historial de revisions de la pàgina 233: 

select p.page_id, p.page_title, r.rev_id, r.rev_text_id,r.rev_user, r.rev_timestamp from page p, revision r where p.page_id=r.rev_page and p.page_id=233;

i ara sí!, està d'acord amb el que veig. La pàgina en si no ha estat piratejada, només la Discussió de la pàgina.

Per tant, em puc carregar les revisions amb page_namespace=1 (Talk), i també puc mirar qui ha fet revisions sobre les meves pàgines, i qui ha fet la última revisió de les pàgines (per detectar les que estan malament). També puc detectar el número de revisions que té cada pàgina, i així detectar les pàgines que s'han tocat. 

select p.page_id, p.page_title, r.rev_id, r.rev_text_id,r.rev_user, r.rev_timestamp from page p, revision r where p.page_id=r.rev_page  and p.page_namespace=0 and r.rev_user>1;

hi ha 60 pàgines que han tocat usuaris amb user_id>1. Efectivament, la majoria de les pàgines s'han d'eliminar 

select p.page_id, p.page_title, r.rev_id, r.rev_text_id,r.rev_user, r.rev_timestamp from page p, revision r where p.page_id=r.rev_page  and p.page_namespace=0 and r.rev_user=0;

2630 pàgines que ha tocat l'usuari anònim. Estic parlant de pàgines amb p.page_namespace=0 (les que m'interessen). Les que tenen p.page_namespace=1 les hauré d'eliminar.

Aquestes pàgines que s'han hackejat són: 

select distinct p.page_id, p.page_title from page p, revision r where p.page_id=r.rev_page  and p.page_namespace=0 and r.rev_user=0;
select distinct p.page_id, p.page_title from page p, revision r where p.page_id=r.rev_page  and p.page_namespace=0 and r.rev_user>1;

i ho puc fer manualment. Estem parlant de en total unes 300 pàgines. 

mysql> select distinct(page_namespace) from page;
+----------------+
| page_namespace |
+----------------+
|              0 | 
|              1 | 
|              2 | 
|              3 | 
|              4 | 
|              5 | 
|              6 | 
|              8 | 
|              9 | 
|             10 | 
|             12 | 
|             14 | 
|             15 | 
+----------------+

Pages on a MediaWiki wiki are grouped into collections called "namespaces", which differentiate between the purpose of the pages at a high level. Pages in certain namespaces can also have special properties or behave differently when they interact with other pages.

Aquestes 16 categories es poden veure aquí: 

mysql> select distinct p.page_id, p.page_title from page p, revision r where p.page_id=r.rev_page and p.page_namespace=0 and (r.rev_user>1 and rev_user<>3);

Per exemple, agafem 

| page_id | page_title                                         |
+---------+----------------------------------------------------+
|     308 | MidiShare:_codi_llenguatge_C                       |
432 | Yamaha_WX-5_Wind_Controller

i mirem per a page_id=308 l'historial. Veiem que l'últim canvi no és de Joan, sinó d'un usuari que es diu Newacct, i que no ha canviat el contingut. Aquest usuari ha fet dues contribucions. El NewAcct és el id=4, i sembla ser que no és spam. En canvi el page_id=432 sí que és un canvi.

nota. Bona notícia: sembla ser que ja s'han aturat els canvis de l'usuari 0, després dels canvis que havia fet a LocalSettings.php 

mysql> select rev_id, rev_page, rev_user, rev_user_text, rev_timestamp from revision where rev_timestamp like '20111013%' and rev_user=0 order by rev_timestamp desc limit 5;
+--------+----------+----------+-----------------+----------------+
| rev_id | rev_page | rev_user | rev_user_text   | rev_timestamp  |
+--------+----------+----------+-----------------+----------------+
| 253419 |     1240 |        0 | 193.124.164.253 | 20111013014434 |
| 253417 |     1239 |        0 | 213.5.66.88     | 20111013013612 |
| 253415 |      998 |        0 | 178.238.232.13  | 20111013012038 |
| 253414 |     1168 |        0 | 190.254.22.34   | 20111013012019 |
| 253413 |     1086 |        0 | 178.238.232.244 | 20111013011916 |
+--------+----------+----------+-----------------+----------------+

Estic a la 17h i els atacs es van aturar a les 1h 44min.

Miro les revisions que hi ha hagut en la page_id=432: 

select p.page_id, p.page_title, r.rev_id, r.rev_text_id,r.rev_user, r.rev_timestamp from page p, revision r where p.page_id=r.rev_page  and p.page_namespace=0 and (r.rev_user>1 and rev_user<>3) and p.page_id=432;

+---------+-----------------------------+--------+-------------+----------+----------------+
| page_id | page_title                  | rev_id | rev_text_id | rev_user | rev_timestamp  |
+---------+-----------------------------+--------+-------------+----------+----------------+
|     432 | Yamaha_WX-5_Wind_Controller |  31987 |       31984 |        5 | 20101103165128 |
+---------+-----------------------------+--------+-------------+----------+----------------+

Elimino aquesta revisió: 

delete from revision where rev_id=31987;

ja no troba la pàgina. La pàgina bona, l'anterior, és: 

mysql> select p.page_id, p.page_title, r.rev_id, r.rev_text_id,r.rev_user, r.rev_timestamp from page p, revision r where p.page_id=r.rev_page  and p.page_namespace=0 and p.page_id=432;
+---------+-----------------------------+--------+-------------+----------+----------------+
| page_id | page_title                  | rev_id | rev_text_id | rev_user | rev_timestamp  |
+---------+-----------------------------+--------+-------------+----------+----------------+
|     432 | Yamaha_WX-5_Wind_Controller |   2841 |        2838 |        1 | 20090505154500 |
+---------+-----------------------------+--------+-------------+----------+----------------+

mysql> select * from page where page_id=432;
+---------+----------------+-----------------------------+---------------------------------------+--------------+------------------+-------------+----------------+----------------+-------------+----------+
| page_id | page_namespace | page_title                  | page_restrictions                     | page_counter | page_is_redirect | page_is_new | page_random    | page_touched   | page_latest | page_len |
+---------+----------------+-----------------------------+---------------------------------------+--------------+------------------+-------------+----------------+----------------+-------------+----------+
|     432 |              0 | Yamaha_WX-5_Wind_Controller | edit=autoconfirmed:move=autoconfirmed |          270 |                0 |           0 | 0.691224059464 | 20101103165128 |       31987 |       92 |
+---------+----------------+-----------------------------+---------------------------------------+--------------+------------------+-------------+----------------+----------------+-------------+----------+

Així doncs, el que s'ha de fer és que el camp page.page_latest apunti a la última revisió bona, que és la 2841. 

update page set page_latest=2841 where page_id=432;

Correcte, i ara es tracta de fer això de forma més o menys automàtica. En aquest cas no s'ha d'eliminar la pàgina, perquè és vàlida, però en altres casos sí que s'ha d'eliminar la pàgina.

Ara ja no apareix aquesta revisió a: 

mysql> select distinct p.page_id, p.page_title from page p, revision r where p.page_id=r.rev_page and p.page_namespace=0 and (r.rev_user>1 and rev_user<>3);

Anem a fer-ne una altra: 

|    1109 | Instal.lació_i_configuració_d                    |

Com que és una pàgina que s'ha d'esborrar, he d'eliminar totes les revisions d'aquesta pàgina i he d'eliminar la pàgina: 

delete from revision where rev_page=1109;
delete from page where page_id=1109;

Això és el que s'ha de fer per totes les pàgines que són de nova creació.

Resum

ja estic netejant la wiki amb èxit. Primer de tot esborro les pàgines que han estat piratejades (namespace=0), i més tard, més fàcil, esborraré les Discussions piratejades (totes, doncs no ho faig servir, namespace=1). Hi ha la possibilitat de què altres namespaces també hagin estat piratejats. (Efectivament, aquesta és la part que es dicuteix en el següent punt)

Les consultes i sentències que es fan servir són: 

select distinct p.page_id, p.page_title from page p, revision r where p.page_id=r.rev_page and p.page_namespace=0 and (r.rev_user>1 and rev_user<>3);
select distinct p.page_id, p.page_title from page p, revision r where p.page_id=r.rev_page and p.page_namespace=0 and r.rev_user=0;

select p.page_id, p.page_title, r.rev_id, r.rev_text_id,r.rev_user, r.rev_timestamp from page p, revision r where p.page_id=r.rev_page  and p.page_namespace=0 and p.page_id=646;
select r.rev_id, r.rev_text_id,r.rev_user from page p, revision r where p.page_id=r.rev_page  and p.page_namespace=0 and p.page_id=878 and r.rev_user=0;

select CONCAT(r.rev_id,',') from page p, revision r where p.page_id=r.rev_page  and p.page_namespace=0 and p.page_id=878 and r.rev_user=0;
select CONCAT(r.rev_text_id,',') from page p, revision r where p.page_id=r.rev_page  and p.page_namespace=0 and p.page_id=878 and r.rev_user=0;

select CONCAT(r.rev_id,',') from page p, revision r where p.page_id=r.rev_page  and p.page_namespace=0 and p.page_id=958;
select CONCAT(r.rev_text_id,',') from page p, revision r where p.page_id=r.rev_page  and p.page_namespace=0 and p.page_id=958;

delete from revision where rev_id IN (
4279,
4280,
4282,
4284
);

delete from text where old_id IN (
4276,
4277,
4278,
4279
);


delete from revision where rev_id=94580;
delete from text where old_id=94570;

delete from page where page_id=646;
update page set page_latest=8772 where page_id=839;

Ara anem a solucionar el namespace=1 (Talk, discussions). Això és més fàcil, doncs es pot fer en bloc.

Jo no he fet discussions de cap mena, per tant totes les discussions s'han d'esborrar. select distinct p.page_id, p.page_title from page p, revision r where p.page_id=r.rev_page and p.page_namespace=1 order by p.page_id; 

delete from text where old_id IN(
select r.rev_text_id from page p, revision r where p.page_id=r.rev_page and p.page_namespace=1 and p.page_id=687
);
delete from revision where rev_page=687;
delete from page where page_id=687;

Ara bé, la consulta del delete from text pot durar molt, degut a què la subconsulta pot retornar molts valors.

Per acabar, també s'eliminen entrades del namespace=2 (en aquest cas poques), i s'eliminen els usuaris que s'han generat durant aquest temps, i només es deixa Joan.

Finalment, miro d'optimitzar l'espai de les taules:

Actualització Novembre 2011: esborrar spam que encara havia quedat

Estic estudiant el wireshark i veig que encara tinc intents de penetració a la wiki. Per sort, veig que només són intents, que el spam a la wiki es va aturar l'octubre de 2011. Tanmateix, tornant a mirar la bd, veig que encara hi ha spam que s'hauria d'eliminar el espais de nom diferents del 0. 

select count(*) from page p where p.page_namespace=0;
select count(*) from page p where p.page_namespace=1;
select count(*) from page p where p.page_namespace=2;
select count(*) from page p where p.page_namespace=3;
select count(*) from page p where p.page_namespace=4;
select count(*) from page p where p.page_namespace=5;
select count(*) from page p where p.page_namespace=6;
select count(*) from page p where p.page_namespace=7;
select count(*) from page p where p.page_namespace=8;
select count(*) from page p where p.page_namespace=9;
select count(*) from page p where p.page_namespace=10;
select count(*) from page p where p.page_namespace=11;
select count(*) from page p where p.page_namespace=12;
select count(*) from page p where p.page_namespace=13;
select count(*) from page p where p.page_namespace=14;
select count(*) from page p where p.page_namespace=15;
select count(*) from page p where p.page_namespace=-1;
select count(*) from page p where p.page_namespace=-2;

namespace=0 Main 713
namespace=1 Talk (Discussió) 0 -> correcte
namespace=2 User 5 -> correcte
namespace=3 User Talk 4 -> spam
namespace=4 Project 4 -> spam?
namespace=5 Project Talk 2 ->  correcte
namespace=6 File 247 -> correcte
namespace=7 File Talk 0 -> correcte
namespace=8 Mediawiki 3 -> correcte
namespace=9 Mediawiki Talk 1 -> ?
namespace=10 Template 1 -> correcte
namespace=11 Template Talk 0 -> correcte
namespace=12 Help 1 -> correcte
namespace=13 Help Talk 0 -> correcte
namespace=14 Category 52  -> correcte
namespace=15 Category Talk 13 -> spam
namespace=-1 Special 0
namespace=-2 Media 0

Per veure quines són les pàgines que tinc en els namespaces, i determinar si hi ha hagut spam o no:

En principi tots els Talk haurien d'estar a 0.

  • Començo a mirar en el namespace=3:
select page_id,page_title from page p where p.page_namespace=3 order by page_id;

|     782 | 213.176.161.200 | -> spam
|     817 | Joan            | -> spam
|     994 | 127.0.0.1       | -> spam
|    1010 | HeleneMallon    | -> spam

i com que es tracta de l'espai de noms User Talk, puc accedir a la informació d'aquesta pàgina:

i ja veig que aquí hi ha hagut spam

Miro les revisions que hi ha hagut en la page_id=782: 

select p.page_id, p.page_title, r.rev_id, r.rev_text_id,r.rev_user, r.rev_timestamp from page p, revision r where p.page_id=r.rev_page and p.page_id=782;

2 revisions el 24/5/2010, és a dir, que aquesta informació sem'm va passar quan vaig netejar el spam en el canvi de servidor.

  • namespace=4:
select page_id,page_title from page p where p.page_namespace=4 order by page_id;

|     786 | Ajuda      | -> spam
|     801 | Copyrights | -> spam
|     822 | Cerca      | -> spam
|    1242 | Quant_a    | -> correcte

Mirem Project:Ajuda, que redirigeix a Wikijoan:Ajuda, i sí que hi ha spam

  • namespace=9:
select page_id,page_title from page p where p.page_namespace=9 order by page_id;

|     995 | Addedwatch |-> correcte
  • namespace=15:
select page_id,page_title from page p where p.page_namespace=15 order by page_id;

|     714 | Informàtica_Musical       | -> spam
|     715 | PostgreSQL                 | -> spam
|     909 | DMS                        | -> spam
|     910 | Electrònica               | -> spam
|     911 | Wordpress                  | -> spam
|     922 | Programació_PHP           | -> spam
|     931 | MidiShare                  | -> spam
|     936 | Postfix                    | -> spam
|     976 | Mediawiki                  | -> spam
|     997 | Openbravo_POS              | -> spam
|    1072 | Administració_de_Sistemes | -> spam
|    1160 | Android                    | -> spam
|    1168 | SQLite                     | -> spam

La bona notícia és que tots els atacs s'aturen el 11 d'octubre del 2011, que vol dir que els canvis que vaig introduir estan bé. Senzillament aquesta informació que m'apareix és encara spam que no vaig eliminar. El que em diu el wireshark és que encara hi ha intents de spam, però ara són infructuosos.

Efectivament, si no estic logat, la comanda:

no deixa editar, i efectivament les últimes revisions són de principis d'octubre de 2011.

Per tant, ara la manera de procedir és com vaig fer:

  • eliminar les revisions d'aquestes pàgines
  • eeliminar el text d'aquestes pàgines
  • eliminar les pàgines

nota. No eliminar les revisions abans no hagi fet les dues select, doncs si no la informació de rev_text_id ja no apareix i no sé quines files de la taula text eliminar. 

select CONCAT(r.rev_id,',') from page p, revision r where p.page_id=r.rev_page and p.page_id=782;
7298,
7366,

select CONCAT(r.rev_text_id,',') from page p, revision r where p.page_id=r.rev_page and p.page_id=782;
7295,
7363,

delete from revision where rev_id IN (
7298,
7366
);

delete from text where old_id IN (
7295,
7363
);

delete from page where page_id=782;

Actualització II Novembre 2011: esborrar spam que encara havia quedat

Encara hi ha molta merda a la base de dades 

mysql> select old_id, left(old_text,30) from text where old_text like '%viagra%' limit 10;

old_id	left(old_text,30)
4666	hi http://overbookers.ning.com
4728	hi http://www.myanmarcafe.net/
4788	5 http://st12band.ning.com/pro
6518	Acetaminophen and viagra the T
9454	 s are Online carry you been e
9459	the ultram complete the catast
9466	Weighttaking desmethyl pills a
9474	 tramadol was and that treatme
9480	of risk if ordering mention at
9484	mu ingredients enhances an mam

a quines pàgines es correspon? 

select rev_id, rev_page, rev_text_id, rev_user from revision where rev_text_id=4666;
rev_id	rev_page	rev_text_id	rev_user
4669	695	4666	0

és la rev_page=695 

select page_id, page_namespace, page_title from page where page_id=695;

page_id	page_namespace	page_title
695	14	Arduino

Veig que el namespace=14 és Categoria, i que hi ha la Categoria Arduino. Tant si miro la pàgina com si miro el seu historial veig que hi ha merda:

En general, a totes les pàgines de Categoria hi ha merda

Per veure tot l'historial de la categoria Arduino: 

mysql> select rev_id, rev_page, rev_text_id, rev_user, rev_user_text from revision where rev_page=695 limit 10;
rev_id	rev_page	rev_text_id	rev_user	rev_user_text
4643	695	4640	0	194.8.75.251
4644	695	4641	0	194.8.75.251
4645	695	4642	0	194.8.75.251
4646	695	4643	0	194.8.75.251

i veig que més important que mirar el rev_user (=0, que vol dir que aquestes pàgines s'han editat sense estar logat), es pot mirar el rev_user_text, on hi ha les IP's que es poden detectar quines han d'estar en una llista negra.

La conseqüència d'haver deixat temps que en la wiki es pugués editar sense estar registrat (que no és el cas d'ara), és que hi ha moltes IP's que van editar spam, i que això encara s'ha de netejar: He de detectar les IP's que són bones (casa, local, institut,...) i mirar què han fet les altres. Aleshores detectaré les pàgines que han modificat. 

mysql> select distinct rev_user_text from revision where rev_user=0 order by rev_user_text limit 10;
rev_user_text
MediaWiki default
192.168.1.131
82.151.209.77
213.176.161.200
88.12.238.172
83.60.39.45
127.0.0.1
213.176.161.208
83.32.194.17
83.50.70.25

select count(distinct rev_user_text) from revision where rev_user=0 limit 10;
count(distinct rev_user_text)
3577

hi ha 3577 IP's diferents. Per exemple, la IP 194.8.75.251:

Un cop estic el.laborant una llista blanca i una llista negra de IP's puc fer: 

select distinct rev_user_text from revision where rev_user=0 and rev_user_text NOT IN (
'192.168.1.131',
'82.151.209.77',
'88.12.238.172',
'213.176.161.200',
'83.60.39.45'
) 
and rev_user_text NOT IN (
'194.8.75.251',
'109.111.190.9',
'109.123.110.214'
) order by rev_user_text limit 10;

select count(*) from revision where rev_user_text='194.8.75.251';
199 revisions
select rev_id, rev_page, rev_text_id from revision where rev_user_text='194.8.75.251';

select page_id, page_namespace,page_title from page where page_id=695;

que es corresponen totes a la pàgina 695: http://wiki.joanillo.org/index.php/Categoria:Arduino 

select page_id, page_namespace,page_title from page where page_id=695;

Per tant, el que he de fer és com sempre:

  • localitzar les pàgines tocades
  • eliminar els texts introduïts
  • eliminar les revisions introduïdes
  • eliminar la pàgina si cal, i si no cal assegurar-se de què la pàgina apunti a la última revisió correcta.
select CONCAT(r.rev_text_id,',') from page p, revision r where p.page_id=r.rev_page and p.page_id=695;
4640,
4641,
...

select CONCAT(r.rev_id,',') from page p, revision r where p.page_id=r.rev_page and p.page_id=695;

4643,
4644,
...


delete from text where old_id IN (
4640,
4641,
...
);


delete from revision where rev_id IN (
4643,
4644,
...
);


delete from page where page_id=695;

Hauré d'anar el.laborant una llista blanca i una llista negra: llista blanca: 

192.168.1.131
82.151.209.77
88.12.238.172
213.176.161.200

llista negra (ja processada): 

194.8.75.251
109.111.190.9
109.123.110.214

Resum

select count(*) from revision where rev_user_text='109.111.190.9';
select rev_id, rev_page, rev_text_id from revision where rev_user_text='109.111.190.9';
select page_id, page_namespace,page_title from page where page_id=695;
select CONCAT(r.rev_text_id,',') from page p, revision r where p.page_id=r.rev_page and p.page_id=1011;
select CONCAT(r.rev_id,',') from page p, revision r where p.page_id=r.rev_page and p.page_id=1011;

delete from text where old_id IN (
4276,
4277,
4278,
4279
);

o bé (compte amb l'ordre!): (va molt lent)

delete from text where old_id IN (
select r.rev_text_id from page p, revision r where p.page_id=r.rev_page and p.page_id=1011
);

delete from revision where rev_id IN (
4279,
4280,
4282,
4284
);

delete from revision where rev_id IN (
select r.rev_id from page p, revision r where p.page_id=r.rev_page and p.page_id=1011
);

delete from page where page_id=1011;

Important. El.laborar una llista blanca i una llista negra de IP's és molt laboriós. És més fàcil detectar quines pàgines han tingut moltes revisions (i molt més fàcil si ens fixem que el namespace=14, o diferent de 0): 

select p.page_id,p.page_namespace,p.page_title,count(*) as num_revisions from page p, revision r where p.page_id=r.rev_page group by p.page_id,p.page_namespace,p.page_title order by num_revisions desc;

+---------+----------------+------------------------------------+---------------+
| page_id | page_namespace | page_title                         | num_revisions |
+---------+----------------+------------------------------------+---------------+
|    1031 |             14 | Pràctiques_ASI-C3-IAIG            |          8647 |
|     768 |             14 | Backup                             |          5761 |
|    1083 |             14 | 50_Ways_to_play_Una_Plata_d'Enciam |          4438

Molt millor

Quan tracto amb consultes que retornen milers de files (per exemple 8000), és un engorro anar amb fitxers de text i fent-copiar i enganxar de la consola (el buffer em deixa unes 500 línies). És molt més pràctic fer el següent procediment:

La informació que vull eliminar és (està ple de merda l'historial): 

select p.page_id,p.page_namespace,p.page_title,count(*) as num_revisions from page p, revision r where p.page_id=r.rev_page and p.page_namespace=14 group by p.page_id,p.page_namespace,p.page_title  order by num_revisions desc;

1031	14	Pràctiques_ASI-C3-IAIG	8647
768	14	Backup	5761
773	14	Administració_de_Sistemes	3922
1062	14	PrГ ctiques_ASI-C3-IAIG	1432
1199	14	Programació_mòbil	571
868	14	DMS	291
856	14	Electrònica	256
857	14	Wordpress	241
972	14	Administració_de_sistemes	222
974	14	Informàtica_musical	220
978	14	Ies_jaume_balmes	218
977	14	Postgresql	215
891	14	Programació_PHP	114
1137	14	Classificació_de_fotos	52
1090	14	Compilació_kernel	47
999	14	Apache	37
992	14	Pràctica_EC	33
870	14	Postfix	31
990	14	SQLite	30
1058	14	Android	7
1146	14	Alfresco	4
1145	14	Bases_de_dades	3
1149	14	5_maneres_de_calcular_g	3
1147	14	ALSA	3
1151	14	Dyndns	3
1150	14	MySQL	3
1097	14	Gestió_Empresarial	3
1100	14	Ubuntu	3
1144	14	Assignatura_ASI-C3-IAIG	3
800	14	Llenguatge_C	2
1161	14	Assignatura_ASI-C6-ASGBD	1
1091	14	Www.joanillo.org	1
780	14	JAVA	1
540	14	XML	1
804	14	Linux	1
816	14	Pràctica_Cicles	1
794	14	Mediawiki	1
619	14	MidiShare	1
1189	14	Openbravo	1
376	14	Oracle	1
919	14	Text-to-Speech	1
411	14	Groupion	1
815	14	Pentaho_Data_Integration	1
802	14	Tips_Linux	1
912	14	IES_Jaume_Balmes	1
287	14	PostgreSQL	1
499	14	Ubuntu_Studio	1
485	14	Informàtica_Musical	1
806	14	Postgres	1

en el servidor remot: 

mysql -u root -p -D wikidb117 -e "select CONCAT(r.rev_text_id,',') from page p, revision r where p.page_id=r.rev_page and p.page_id=1031" > txt.txt
mysql -u root -p -D wikidb117 -e "select CONCAT(r.rev_id,',') from page p, revision r where p.page_id=r.rev_page and p.page_id=1031" > rev.txt

copio els fitxers a l'ordinador local: 

$ scp joan@wiki.joanillo.org:txt.txt .
$ scp joan@wiki.joanillo.org:rev.txt .

afegeixo en els fitxers: 

delete from text where old_id IN (

);

delete from revision where rev_id IN (

);

torno a enviar al servidor: 

$ scp txt.txt joan@wiki.joanillo.org:txt.txt
$ scp rev.txt joan@wiki.joanillo.org:rev.txt

I ara executo els dos deletes en el servidor: 

mysql -u root -p wikidb117 < txt.txt
mysql -u root -p wikidb117 < rev.txt

Finalment: 

mysql -u root -p -D wikidb117 -e "delete from page where page_id=1031"

La conseqüència és que ara la informació es processa molt més ràpidament. Com que tota aquesta informació fa referència al namespace=14, segur que no faig cap trastada.

i encara més coses

Si cerco per viagra, sex, penis, enlargement, encara es troba molta cosa. Això sí, només en la taula text, i no en la taula revisió. Per tant, només he d'eliminar aquestes files.

compte!!: no eliminar aquesta pàgina, que precisament conté aquestes paraules (però deu ser la única...) 

mysql -u root -p -D wikidb117 -e "select CONCAT(old_id,',') from text where old_text like '%viagra%'" > eliminar.txt
scp joan@wiki.joanillo.org:eliminar.txt .
delete from text where old_id IN (
scp eliminar.txt joan@wiki.joanillo.org:eliminar.txt
mysql -u root -p wikidb117 < eliminar.txt

i encara elimino més informació: 

mysql -u root -p -D wikidb117 -s -e "select CONCAT(old_id,','),left(old_text,50) from text where old_text like '%zxv%' or old_text like '%xcf%' or old_text like '%bbd%' or old_text like '%fgt%'" > eliminar.txt
scp joan@wiki.joanillo.org:eliminar.txt .
eliminar del fitxer totes les files que són bones
processar amb el cal, quedar-se amb la primera columna, afegir una coma
delete from text where old_id IN (
scp eliminar.txt joan@wiki.joanillo.org:eliminar.txt
mysql -u root -p wikidb117 < eliminar.txt

mysql -u root -p -D wikidb117 -s -e "select CONCAT(old_id,','),left(old_text,50) from text where old_text like '%xlm%' or old_text like '%crr%' or old_text like '%ptq%' or old_text like '%dkl%' or old_text like '%skl%'" > eliminar.txt
mysql -u root -p -D wikidb117 -s -e "select CONCAT(old_id,','),left(old_text,50) from text where old_text like '%lli%' or old_text like '%jnk%' or old_text like '%pfg%' or old_text like '%sjk%' or old_text like '%dxf%' or old_text like '%sml%' or old_text like '%crk%' or old_text like '%rtg%'" > eliminar.txt

i encara en podria eliminar més.

creat per Joan Quintana Compte, desembre 2010, octubre 2011, novembre 2011

Obtingut de «http://wiki.joanillo.org/index.php?title=Combatre_el_spam_en_una_mediawiki&oldid=248156»