How to bulk clean spam posts and comments from your WordPress website

This lesson will show you how to find, flag and remove spam content from your WordPress website. It uses a database search to identify content that contains spam words, and flags them for removal so you can remove them safely via the WordPress admin dashboard.

The steps for doing this are as follows;

1. Identify a list of words that would only appear in a spam post or comment.
2. Back up your database
3. Search your WordPress database for posts or comments that contain one or more of those words.
4. Trash any posts or comments.
5. Empty Spam

1. Identify words that indicate a post or comment contains spam.

It's fairly easy to identify a set of keywords that identify a spam comment - simply read through your comments. Words like viagra and tramadol are likely to come up frequently.

So, in this step, you need to make a list of these words so you can scan for them in step 2 below.

This lesson uses the words cactus and banana as spam words. You may want to add some posts/pages/comments/product reviews that contain those words if you want to test the database queries used in this lesson.

Be careful to look for words that may come in different form, e.g. banana and bananas (this is is just an example, unless you consider banana to be a spam word!).

Once you have your list, move on to the next step.

2. Back up your database.

The database queries in Step 3 will make changes directly to your database. If you do something wrong, BAD things could happen to your WordPress website including BREAKING it completely.

So, back up your database.

AND

Make sure you know how to restore the backup. If you don't know how to restore your database backup, you could be stuck with a broken WordPress website.

Finally, we can't take any responsibility for your database, your WordPress website or your sanity if you try this lesson without taking a backup. If you don't feel comfortable doing this, then stop now and find an expert who can do this for you.

3. Trash any Posts or Pages that contain spam.

in this step, we'll check the content of Posts and Pages for the keywords cactus OR banana*, and then trash Posts or Pages any that match. Remember that by using the * with banana, we're saying that we consider both banana and bananas (but not banana pancakes) as spam matches.

In order to use the examples exactly as they are, you should create one Post and one Page that contain either of the words cactus or banana. When you're ready do to this for real, then you can substitute your own list of spam words.

First, run this ALTER query. This only needs to be run once at the start.

ALTER TABLE `wp_posts` ADD FULLTEXT(`post_content`);

Then, check how many posts or pages match your spam words, and will show you a list of all . This query will not modify your database. If you get a FULLTEXT error, make sure you ran the ALTER query above correctly.

SELECT id,post_title,post_type FROM wp_posts
WHERE (post_type = 'post' OR post_type = 'page')
AND MATCH (post_content)
AGAINST ('cactus banana*' IN BOOLEAN MODE)

You should check the items that this query returns to make sure that they really are spam. If they are not, you should modify your spam words before continuing.

Note that you can add more terms to the AGAINST clause. Simply add more words into the section where cactus and banana sit in this example.

Finally, if you're happy that your list of spam words is finding actual spam but is not picking up posts or pages that are not spam then you can continue and run the following query, which will trash all posts and pages that match.

By the way, This query will modify your database. Now is the time to back up your database if you didn't do that already. And it won't hurt to back it up again....

UPDATE wp_posts set post_status='trash' where id in (
    SELECT id FROM wp_posts
    WHERE (post_type = 'post' OR post_type = 'page')
    AND MATCH (post_content)
    AGAINST ('cactus banana*' IN BOOLEAN MODE)
);

And that should be it. You can now go to your WP Admin dashboard and check to see that these posts/pages have been trashed. If you are sure that they are all spam, then you can empty the trash - and they'll be all gone!

4. Trash any Comments or Product Reviews that contain spam.

This step is similar to step 3, except it works on Comments and Reviews.

Be careful when trashing comments with this method - there are many comment types in WordPress that are not actually comments. In theory the comment_type of an actual comment should be comment but in the sites I manage the comment_type field is empty. I've allowed for both here:

First, run this ALTER query. You only need to do this once. You can repeat the next query as many times as you like once this query has been run.

ALTER TABLE `wp_comments` ADD FULLTEXT(`comment_content`);

Then, run this query to check which comments or product reviews contain your spam words:

SELECT comment_ID,comment_type, comment_approved FROM wp_comments
WHERE (comment_type = 'comment' OR comment_type = '' OR comment_type = 'review')
AND MATCH (comment_content)
AGAINST ('cactus banana* quality' IN BOOLEAN MODE);

Check the results to make sure that the comments/reviews listed actually do contain spam, then run this query:

Just another reminder. This query will modify your database. Now is the time to back up your database if you didn't do that already. And it won't hurt to back it up again....

UPDATE wp_comments set comment_approved='spam' where comment_ID in (
    SELECT comment_ID FROM wp_comments
    WHERE (comment_type = 'comment' OR comment_type = '' OR comment_type = 'review')
    AND MATCH (comment_content)
    AGAINST ('cactus banana* quality' IN BOOLEAN MODE)
);

Once you've done this you can check your Comments in the WordPress admin dashboard, and Empty Trash if you're happy that the items in Trash are all OK to delete.