[Systems] Pootle spam registration issues

[Chris Leonard]

Dear Infrastructure Team,

I have some concerns about certain Pootle registrations that I have
been seeing recently.  We are currently running Pootle 2.0.5 is
powered by Translate Toolkit 1.7.0.

I have asked the Pootle devs if any improvements in the registration
process been introduced since that version (e.g. some sort of
CAPTCHA).  I'm waiting for an answer on that, but in the meantime I
would like some help with developing some SQL scripts I can run to
dump out the user information from the Pootle database in a format
that will allow me to do some manual review (and possibly purging).

I must reluctantly admit that my command-line SQL-fu has become weak
and feeble due to over-dependence on fancy commercial tools.  Can
someone offer assistance with developing a script(s) that will dump
out all of the availble information from the user database (say as
CSV)?

I am hoping that moving to a new Pootle version (on a ne wVM) will
help address this issue going forward, but I will want to do some work
on purging existing spam registrations as they make me very
uncomfortable about the potential for vandalism of our valuable
translations.

cjl

+++++++++++++++++

For examples of the problematic registrations, see below.

http://translate.sugarlabs.org/notices/57075
New user Deenereli registered.

http://translate.sugarlabs.org/accounts/Deenereli/

on checking in the Pootle User Administration panel, I found that this
user's e-mail was donationasw at gmail.com

furthermore, the account had been activated, presumably by a response
to the registration confirmation e-mail that goes out from Pootle.

The results of a Google search on that e-mail address

http://lmgtfy.com/?q=donationasw%40gmail.com

gives me cause for concern.  The results are all from botscout and
stopforum spam web-sites.

I have inactivated the account manually and mailed a request to the
account for a personal response.  If I do not get one, I will delete
the account via the Pootle User Admin page.

http://translate.sugarlabs.org/admin/users.html?page=19

My concern is this is a registration from a forum spamming bot that
has the smarts to reply to registration mails in order to activate
accounts.

The problem for Pootle is that while we accept drive-by suggestions
from anonymous users (pootleuser group "nobody"), once a username is
registered, we typically accept actual submissions (pootleuser group
"default").  This is potentially far more disruptive than suggestions.

I would rather not tighten down our "nobody" and "default" privs if I
can avoid it.  I would much rather understand how to prevent the forum
bots from successfully registering.  It may be that

There are some other patterns I am seeing in username registration
(without successful activation) that make me think there may be more
than one bot in play here.


Presumptive spam account registrations in the last 24 hours:

aidesteisolve
disperseerd at gmail.com

AnogemotUntot
frank.pyzia at aol.com   (activated)

bisonbialz
gobizonkeva23 at gmail.com

BorisPef
borlorx at gmail.com

demonmayj
gobizon.keva23 at gmail.com

FreshNatalieXXY
freshnataliexxl at gmail.com

gateBeathyBum
hyfrogenyuj at gmail.com

irnytxjoit
withdrawdfr at gmail.com

nunavivi
nunaviviaza at gmail.com

olbjovnaiw
stickpinmqa61 at gmail.com

Proxitreck
comocadas at gmail.com

Scollussy
olgamalinkowskiewiczowy at gmail.com

Teetlewafe
danisummin12 at aol.com   (activated)

TrenseFaT
akdimonn at yandex.com   (activated)