Combatre el spam en una mediawiki
Contingut
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
- http://www.mediawiki.org/wiki/Manual:Combating_spam
- http://www.mediawiki.org/wiki/Extension:ConfirmEdit
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:
- http://wiki.joanillo.org/index.php/Discussi%C3%B3:Alternatives_al_SQL*Plus:_gqlplus
- http://wiki.joanillo.org/index.php?title=Discussi%C3%B3:Alternatives_al_SQL*Plus:_gqlplus&action=edit
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:
- http://wiki.joanillo.org/index.php/Categoria:Arduino
- http://wiki.joanillo.org/index.php?title=Categoria:Arduino&action=history
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