MySQL - Remove Spambot Emails from database

[EvilGenius]

Hi,

So here's the problem.

My forum has like 2-3K spambots that signed up but they're dormant due to all the rules n shit, now i run a wrestling site, 3-5 times a month mass mailing all the members about PPVs and stuff like that.

So, I need to remove the bot emails from my database.

Now the following at the domains that i have in my ban list

@meltmail.com
@freeholidayaccommodation.info
@moreprobe.com
@islandaccommodation.info
@seorj.cn
@wpmu.org.cn
@mail.ru
@list.ru
@mailbo********
@bk.ru
@e-email.com
@e-mail.net
@e-mail.com
@e-mail.org
@xmail.com
@xmail.org
@yandex.ru
@email.org
@megapochta.cn
@mail.obmenhelp.ru
@obmenhelp.ru
@3gpfilms.ru
@rambler.ru
@narod.ru
@konversia-aero.ru
@2008-online.info
@hotmail.net
@email.org
@hotmail.org
@tmail.org
shu92
@*.cn

How do i remove them from my database, so i never email anyone with those IDs.

P.S: Mail Queue is about 18K when I send a mass mail.


Thanks i adv.

EvilGenius Reviewed by EvilGenius on 16th Jun 2010. MySQL - Remove Spambot Emails from database Hi, So here's the problem. My forum has like 2-3K spambots that signed up but they're dormant due to all the rules n shit, now i run a wrestling site, 3-5 times a month mass mailing all the members about PPVs and stuff like that. So, I need to remove the bot emails from my database. Now the following at the domains that i have in my ban list Rating: 5

[Lock Down]

Depends on the layout of your database and type.

Is it just a straight db with one table having only the email address?

[nYXem]

What forum script are you using?

[EvilGenius]

Vbulletin

[Lock Down]

Ok. If you do not have a prefix for the tables and 8 is the banned group id you can use these sql commands to put them in the banned group and then use acp to prune all members in the banned group.
Remember always back up the table first. In this case the user table.

update user set usergroupid = 8 where email like '%@meltmail.com%';
update user set usergroupid = 8 where email like '%@freeholidayaccommodation.info%';
update user set usergroupid = 8 where email like '%@moreprobe.com%';
update user set usergroupid = 8 where email like '%@islandaccommodation.info%';
update user set usergroupid = 8 where email like '%@seorj.cn%';
update user set usergroupid = 8 where email like '%@wpmu.org.cn%';
update user set usergroupid = 8 where email like '%@mail.ru%';
update user set usergroupid = 8 where email like '%@list.ru%';
update user set usergroupid = 8 where email like '%@mailbo%';
update user set usergroupid = 8 where email like '%@bk.ru%';
update user set usergroupid = 8 where email like '%@e-email.com%';
update user set usergroupid = 8 where email like '%@e-mail.net%';
update user set usergroupid = 8 where email like '%@e-mail.com%';
update user set usergroupid = 8 where email like '%@e-mail.org%';
update user set usergroupid = 8 where email like '%@xmail.com%';
update user set usergroupid = 8 where email like '%@xmail.org%';
update user set usergroupid = 8 where email like '%@yandex.ru%';
update user set usergroupid = 8 where email like '%@email.org%';
update user set usergroupid = 8 where email like '%@megapochta.cn%';
update user set usergroupid = 8 where email like '%@mail.obmenhelp.ru%';
update user set usergroupid = 8 where email like '%@obmenhelp.ru%';
update user set usergroupid = 8 where email like '%@3gpfilms.ru%';
update user set usergroupid = 8 where email like '%@rambler.ru%';
update user set usergroupid = 8 where email like '%@narod.ru%';
update user set usergroupid = 8 where email like '%@konversia-aero.ru%';
update user set usergroupid = 8 where email like '%@2008-online.info%';
update user set usergroupid = 8 where email like '%@hotmail.net%';
update user set usergroupid = 8 where email like '%@email.org%';
update user set usergroupid = 8 where email like '%@hotmail.org%';
update user set usergroupid = 8 where email like '%@tmail.org%';
update user set usergroupid = 8 where email like 'shu92%';
update user set usergroupid = 8 where email like '%.cn%';

Expand

[EvilGenius]

thanks buddy!
will try it soon

[Lock Down]

Glad to help. Don't forget the backup first.

[EvilGenius]

thanks a lot!

Backed up, no issues.

Added 700 users to banned.

will try to filter out more spam emails and lay them to rest.

thanks again buddy!

[Lock Down]

No problem. Glad it is working out for you.