So, after I claaimed we'd have less spammers than others, I wanted to find out how many spammers we've actually had.

mysql> select EXTRACT(YEAR_MONTH FROM from_unixtime(created)) as yearmonth, count(*) as count from users where status = 0 and login != 0 group by yearmonth order by yearmonth desc ;

There are two interesting observatiosn to be made:

1) Spammers are most active on drupal.org in summer

2) Spamming on drupal.org is on the rise.

Especially the latter point is of concern.

I know that many readers will think "Why don't they simply deploy mollom on drupal.org?".

Unfortunately, this is not (yet) the answer. Most of these spammers do not create a single post on drupal.org, they merely use the high page rank of the user profiles to redirect the gullible to other sites. Mollom currently does not deal with user profiles at all. I guess that it would be interesting to use it for this purpose once support becomes available. However, I am not sure that the redesign of drupal.org will still use profile.module, so we maybe could standard node form support afterwards.

For the interested readers, here's the SQL query I use to find profile spammers:

select u.name, u.mail, concat('http://drupal.org/user/', u.uid, '/edit'), substr(p.value, 1, 60) from users u inner join profile_values p on u.uid = p.uid where u.uid > 430000 and p.value != '' and length(p.value) > 50 and u.status != 0;

Once a common pattern has been identified, you can confirm it with e.g.

elect u.name, u.mail, concat('http://drupal.org/user/', u.uid, '/edit'), substr(p.value, 1, 60) from users u inner join profile_values p on u.uid = p.uid where u.uid > 430000 and p.value != '' and length(p.value) > 50 and u.status != 0 and p.value like '%spam;

And then the grand finale:

update users set status = 0 where uid in (select p.uid from profile_values p where p.uid > 430000 and p.value != '' and length(p.value) > 50 and p.value like '%spam);