Remove post_status Spam Posts from WordPress

In Blog by Jerad Hill1 Comment

One of our clients with an outdated WordPress website was notified by Google that their website may have been hacked. There were some pages that Google was reporting that could be spam. Looking in the backend of WordPress, we did not find any spam posts. However, one thing stuck out to me as odd. When viewing all posts in the WordPress backend, it said there were 168,030 posts but only 13 of them were published. It also only displayed 13 posts. It was as if WordPress was confused as to how many posts were there.

I went into MySQL and looked in the wp_posts table and sure enough, there was a mess of spam posts in there. Apparently their website had been injected with over 168,000 spam posts. These spam posts were not publically viewable through normal site navigation because the post_status was set to “test.” This was also odd to me because that post_status was not a valid WordPress option.

The only valid post_status entries should be: publish, future, draft, pending, private, trash, auto-draft and inherit. Anything else is most likely spam or shouldn’t be there.

The typical database cleanup plugins such as WPOptimize did not work because they only look for valid post_status entries. I needed to get rid of a post_status that was not valid to WordPress. The one thing all of these spam posts had in common was the post_status of “test.” I accessed the database via phpMYAdmin and navigated to the wp_posts table and clicked on SQL. This box allows me to execute queries on the database manually. This is a dangerous process because if you do not do it right, you can damage the database and immediately experience data loss. Make sure to run a database backup before doing this. In my case, the database was so large (875MB), I took my chances by executing the query anyway. I had a backup from 12 hours ago I could have rolled back to if needed.

Here is the SQL Query to run once you are inside the wp_posts table.
DELETE FROM wp_posts where
post_status like '%test%'

This query can help you delete posts even if the post_status is something other than test. If you are wanting to clean up WordPress posts that are in a valid post_status, such as “drafts,” I would recommend using a plugin such as WPOptimize. This method is a last ditch effort to get rid of the spam content without taking extreme measures such as deleting all of your sites content and adding it back in manually.

The portion of the query: “DELETE FROM wp_posts” tells mySQL to only delete from the wp_posts table. Pretty basic. “where post_status” tells mySQL to look in the post_status column. “like ‘%test%'” tells mySQL to delete rows that contain the word “test.” It will only delete if it is a match. If you are trying to delete entries based on a different column, you would simply change post_status to post_type or another column name. You would then need to change what is to be matched. So change the word test to attachment, or whatever you need it to match for deletion. PLEASE BE CAREFUL!!!

Even with as many as 168,000 spam posts, it should only take a moment to remove all of those rows from your database. This is not a complete cleanup as there is most likely associated post_meta as well, but it removes the posts from your website and will satisfy Google when they crawl the site again to see if it has been cleaned. The URLs that were leading to the spam posts will now hit the default 404 error page.

Before starting any of this, you will need to make sure your website has been added to the Search Console in Google Webmaster Tools. You can do that here: https://www.google.com/webmasters/tools/home?hl=en

Follow by photos:

Here is what one of the spam posts looked like:

spam-post

Select wp_posts in phpMyAdmin

phpMyAdmin-wp-posts

Select SQL to run custom SQL Query on your database

phpMyAdmin-sql-query

Enter the SQL Query to run on your database

phpMyAdmin-post_status-query

Have Google review your site by requesting a review in Search Console

search-console-security-issues

Comments

  1. I have a client site that has just under 600k draft posts which are all spam. could I simply change the word test to draft and successfully delete all of the draft posts?

Leave a Comment