Last active
December 27, 2015 06:28
-
-
Save MaxLazar/7281376 to your computer and use it in GitHub Desktop.
Cleanup ExpressionEngine spammers from DB
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#Delete all pending ExpressionEngine members mysql query | |
DELETE FROM exp_member_data where member_id in (select member_id FROM exp_members where group_id = 4) LIMIT 1000000 | |
DELETE FROM exp_message_folders where member_id in (select member_id FROM exp_members where group_id = 4) LIMIT 1000000 | |
DELETE FROM exp_member_homepage where member_id in (select member_id FROM exp_members where group_id = 4) LIMIT 1000000 | |
DELETE FROM exp_members where group_id = 4 LIMIT 1000000 | |
#remove all users who have links in bio (be carefully with this!) | |
DELETE FROM exp_member_data where member_id in (select member_id FROM exp_members where bio REGEXP 'http') LIMIT 1000000 | |
DELETE FROM exp_message_folders where member_id in (select member_id FROM exp_members where bio REGEXP 'http') LIMIT 1000000 | |
DELETE FROM exp_member_homepage where member_id in (select member_id FROM exp_members where bio REGEXP 'http') LIMIT 1000000 | |
DELETE FROM exp_members where bio REGEXP 'http' LIMIT 1000000 | |
#more patterns for select spammers (you will also need do the same for tables exp_member_data, exp_member_homepage and exp_message_folders ) : | |
SELECT member_id FROM exp_members WHERE email REGEXP '[.].*[.].*[.].*[.].*@'; #select members who have emails with 4 dot's in name | |
select member_id FROM exp_members where bio REGEXP 'url=www.' | |
select member_id FROM exp_members where bio REGEXP 'a href="' | |
#most common spammers emails | |
select member_id FROM exp_members WHERE email REGEXP '.*@21cn.com' OR email REGEXP '.*@163.com' OR email REGEXP '.*@qq.com' OR email REGEXP '.*@tom.com' OR email REGEXP '.*@mailnesia.com' | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment