< Fighting spam comments in WordPress

I’ve recently volunteered as a webmaster for a football club that I have been involved in for the last few years. The website that was built by someone else and it does look pretty good using WordPress, however it’s being affected by spam comments and it is putting quite a heavy strain on the website and the MySQL database.

The task that was set before me for this is to improve the responsiveness of the website and to identify the components (in this case, the spam comments) that was causing the website to slow down and to remove / fix the issues.

The actions I took

From the initial investigation in the backend, I had spotted that there were 28,483 spam comments either previously approved, marked as spam or waiting for action to be taken. I immediately decided that it would be counter-productive if I was to use the backend to remove all the spam comments then. It was to be a SQL query that would remove all the comments quickly.

Before I could do that, it is good practice to back up all the WordPress files and the MySQL database. The MySQL database with the spam comments had ballooned to 366MB. With all the files backed up, it was time to go inside PHPMyAdmin on the web hosting package to check the wp_comments table in the database.

Once I was inside the wp_comments table, the 28,483 rows of spam comments was on display then. To remove them all quickly, I used a SQL query like the one below:

1
DELETE FROM wp_comments WHERE comment_approved = 0

If the comments were previously approved and is not what you would consider to be a genuine comment and is considered to be spam, then you can change the SQL query to:

1
DELETE FROM wp_comments WHERE comment_approved = 1

Once the SQL query had been processed, all the spam comments was removed from the database, and then I went back to the database main page in PHPMyAdmin and selected all the tables, and selected Optimize Table option to optimise the database at the bottom of the page. The file size of the database went from 366MB to 20MB in a fell swoop which provided a massive 93.3% reduction of the database size.

Now it’s halfway there to removing spam from the WordPress site. In the WordPress backend Settings > Discussion there is an option to disable comments on future posts or pages. This option was checked originally, so it got unchecked to remove the option that users/bots could comment on articles or pages.

To prevent more spam comments coming in, I had to go and mass edit all the existing posts and pages to turn off the comments function to prevent new spam comments being made. Fortunately WordPress’ backend supports bulk editing and it was a relatively simple exercise by selecting all the posts and pages and pressing edit and selecting Do not allow option in the comments section.

Fast forward a few hours, the spam comments on the pages and posts had been eliminated and no new comments could be made then. However the spam comments kept coming and it was via another way of commenting on the website, and it was on the image attachment posts/pages. To remove that as a short-term measure, I installed a plugin called Attachment Pages Redirect which would make redirects of the image attachments to be of its parent page where the image is at. This removed the capability of the spam comment bots to be able to comment on the pages.

For those that may not want to use a plugin for this function, it is a relatively simple fix to implement in your theme folder with the filename image.php having the code below:

1
<?php wp_redirect(get_permalink($post->post_parent)); ?>

This code will do exactly what the plugin does and prevents image attachment pages being visible but end up being redirected to the parent page.

Later on with the spam comments largely eliminated from using the image attachment pages but now they were coming in via trackback on old posts or pages as I had turned them off on new pages or posts but the older pages or posts had them enabled, and it was counter-productive to do them individually via the backend so I found out that there’s a way of disabling trackback on all pages/posts via a SQL query inside the WordPress database with the wp_posts table:

1
UPDATE wp_posts SET ping_status="closed";

With this SQL query, it will enable you to disable trackbacks on every posts and pages that are in the database. Another door closed to spam!

The outcome

With the actions being done by posts/pages and image attachment pages, I have removed the capability of where the spam comment bots could post anywhere on the website, and thus in return stopping them effectively from overwhelming the database (specifically the table wp_comments having thousands of rows of rubbish) inside the database which would be on the web hosting.

As a result of the actions being taken to combat spam comments, the football club website is noticeably faster now and is more responsive to user actions when they navigate through the website. It certainly helps if the database is 93.3% smaller than what it was originally.