How I dealt with having to delete 1.3 million SPAM registrations

[hugh_]

I recently had the experience of cleaning up a forum which was left running vbulletin 3.6.9 for 8 years. With 1.3 million SPAM user account registrations to it's name. I thought it might be worth passing on some how I went about cleaning the place up.

Summarizing a lot of trial and error which isn't worth repeating. The first thing I did was backup the filesystem and database, then took the copies offline. I upgraded to 4.2.3, which took a while but went without a hitch thankfully. Then I started looking into the 'User Moving/Pruning System' which is limited unfortunately, not least in it's search abilities but mostly to 1,000 deletions at a time. I also looked into whether it could be done using SQL queries but apparently not, shame. And 'List Users Awaiting Moderation' might be able to list many thousands of users but it can't do anything helpful with them. Then I found a handy plugin for automating user deletions and usergroup changes which got my hopes up.

However this too is limited to a 1,000 accounts so I set about deleting users manually until it occurred to me to move all the problem accounts to a single usergroup, move a subset at a regular interval using a scheduled SQL query and delete them using the plugin run as a scheduled task. Unfortunately again here here was another limitation in that run as a scheduled task the plugin was unable to delete more than 100 users at a time on my system at least, but still this was it finally a way of deleting 14400 users (or 100 * 6 * 24) per day automatically.

Some other ideas I picked up along the way. If Who's Online is flooded with SPAM users. You might want to make their usergroup invisible. I also blocked all member profiles from all search engines using robots.txt and applied a mod to limit access to certain usergroup's profile pages. Going forward of course the upgraded forum is now using re:captcha2 and has all the relevant user permissions shored up as much as possible.

Anyway here's some of what I picked up along the way. As always backup thoroughly before attempting any of it!

- SQL to make all users in usergroup X invisible:

UPDATE user SET options=options + 512 WHERE NOT(options & 512) AND usergroupid = X

- SQL to move users selectively from one usergroup to another, with the added limitation of only moving users who's name starts with the letters A through F, have more than 1 post and limit the move to a 1,000 user records only.

UPDATE user set usergroupid = X where (usergroupid = Y AND username REGEXP '^[A-Z].*$' AND posts >=1) LIMIT 1000

- User cleanup mod:

https://vborg.vbsupport.ru/showthrea...hlight=cleanup

- Hide profiles mod:

https://vborg.vbsupport.ru/showpost....4&postcount=23

- How to schedule mysql queries:

http://www.mysqltutorial.org/mysql-t...heduled-event/

My schedule:

CREATE EVENT move_users_for_deletion
ON SCHEDULE EVERY 10 MINUTE
STARTS '2016-02-16 06:59:00' ON COMPLETION PRESERVE ENABLE
DO
UPDATE user set usergroupid = X where usergroupid = Y LIMIT 100

And the schedule I'm running for User Cleanup: 10, 20, 30, 40, 50, 00

[SaN-DeeP]

Thanks I have 200K + banned/spammers/ etc. users for deletion as well,
Was looking something, will update again if I need any suggestions.

Regards,

[hugh_]

I forgot to say. If you prefer to do things manually. The most efficient way I found is to just keep repeating:

1. Move 995 users to an empty usergroup with a query.
2. Do a Run Now of User Cleanup from the scheduled tasks menu.

[hugh_]

After moving this site to a host with cron access I cleaned up a remaining 165k spam registrations with a scheduled task run one minute before and the following job. 175 users was the most the server could delete at one time but your millage may vary.

9,19,29,39,49,59 * * * * /usr/bin/mysql -u root -p<MYSQLPASSWORD> -e "USE <DBNAME>;UPDATE user set usergroupid = <DELETIONGROUP> where usergroupid = <SPAMGROUP> LIMIT 175" > /dev/null 2>&1