How to remove SPAM users from WordPress site

In today’s post we’ll show you how we deal with spammers on our blog. Since we gave you the Ask and Answer (http://www.itcuties.com/answers) function on our site, there are a lot of spammers that are registering to itcuties.com.

ITCuties - WordPress remove spam users - SPAM accounts

ITCuties – WordPress remove spam users – SPAM accounts

Those users don’t contribute to our site – they don’t write any questions, answers or comments. We have decided to remove those account since they are worthless. Here is how we do it.

# Create temporary table
CREATE TABLE tmp_contributors (user_id bigint(20), username VARCHAR(100));

# Insert all the users that write comments
INSERT INTO tmp_contributors (user_id, username) SELECT ID, user_login FROM wp_users WHERE user_login IN (SELECT DISTINCT comment_author FROM wp_comments);

# Insert all the users that write posts
INSERT INTO tmp_contributors (user_id, username) SELECT ID, user_login FROM wp_users WHERE ID IN (SELECT DISTINCT post_author FROM wp_posts);

# Delete all the users that does nothing
DELETE FROM wp_users WHERE ID NOT IN (SELECT DISTINCT user_id FROM tmp_contributors);

# Delete user meta
DELETE FROM wp_usermeta WHERE user_id NOT IN (SELECT DISTINCT user_id FROM tmp_contributors);

What we do in this SQL script is we create temporary table tmp_contributors where we store user id’s na usernames of the accounts that contribute to our site. Next we get accounts that have at least one comment (wp_comments table) (comments are answers to the questions as well – this is our implementation) on our site and we store them in the temporary table. The same is with accounts that write questions, which are a special type of posts (wp_posts table) in our implementations. We get data for accounts that have at least one post published on our site and store it in the temporary table. At the end of our script we delete data from wp_users table based on the ID’s – all the accounts that have ID that is not stored in the temporary tmp_contributors table are deleted. The same thing is done with account metadata, all the rows with user_id that is not stored in the temporary table are deleted.
The last step of our “deal with the spammers” procedure is to delete the temporary table.

# Delete temporary table
DROP TABLE tmp_contributors;

2 Responses to "How to remove SPAM users from WordPress site"

  1. how to make a website says:

    It’s a shame you don’t have a donate button! I’d definitely donate to this excellent blog!
    I guess for now i’ll settle for book-marking
    and adding your RSS feed to my Google account.
    I look forward to fresh updates and will share this website with my Facebook group.
    Chat soon!

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Java by Example App is available at Google Play Store NOW