Deleting spam comments from WordPress MySQL database

… if comments are enabled, or ever were, the WordPress MySQL database stores each of these in a separate row; this can make the size of the database swell considerably in a very short space of time.

Even if server space is not expensive, it pays to massage the MySQL database back-end of WordPress from time to time.
One example of this is deleting entries for spam comments.

The WordPress blogging system relies on the free MySQL database, which stores the information needed to link all posts to images and themes. Images are stored separately in a folder called ‘uploads’: < root >/wp-content/uploads, where < root > is the directory path on the server to the WordPress installation.

The database stores all words typed or copy-pasted into WordPress, including all comments.

My experience has been that WordPress comments must be turned off to avoid drowning in a tsunami of spam. To enable comments without significant security procedures simply invites robot scripts to fake user logins and dump endless nonsense posts just to create links back to commercial or malware sites.

But if comments are enabled, or ever were, the WordPress MySQL database stores each of these in a separate row; this can make the size of the database swell considerably in a very short space of time.

Every once in a while it pays to export the database from the web server and to take a good look around inside. This can be done with a good text editor or PHP admin console (I use XAMPP as a desktop development environment). You should never use Windows Notepad or any kind of word processing package to do this. Notepad is not strong enough to open larger files, and word processing packages alter the code characters by turning them into printable symbols that can no longer be recognised as code when saved back to the database.

The WordPress database includes a table called wp_comments which has a column called comment_approved. In that column, one of the values can be ‘spam’, meaning that comments have been marked as spam by the user. Unfortunately this does not delete the database entry (because some WP plugins use this data to automatically mark posts by the same user as spam).

However, spammers rarely use the same name twice, auto-generating user name and login IDs. Sometimes hundreds at a time for a single site.

Unless and until the WordPress system offers pretty airtight security against spammers, it is best to disable WordPress comments.

If comments were ever enabled, all database entries marked spam should be deleted. I recently saved myself 6000 rows of data, and roughly half the size of my total database, after doing just that.

Methods for cleaning MySQL vary by server installation and virtual administration utilities, but any PHP admin utility should let you access the database, navigate the tables, and run an SQL query against them.

The query itself will vary from version to version, but will look something like:

DELETE FROM wp_comments WHERE comment_approved=’spam’;

You might not actually have marked comments as spam, and there may be thousands of comments awaiting your approval.

If you are certain that these comments are spam rather than legitimate, you can also delete all comments awaiting approval from the database by running a query something like this:

DELETE FROM wp_comments WHERE comment_approved=’2’;

In this example ‘2’ is the value for pending approval.

You might want, instead, to delete all comments in a date range:

DELETE FROM wp_comments WHERE comment_date > '2013-12-25 11:30:00' AND comment_date <= '2014-11-13 00:00:01';

This, or a similar query, will delete every comment written to the database between half past eleven on the morning of 25 November 2013 to just after midnight on the morning of 13 November 2014. But this query will delete EVERY COMMENT in this date range.

As an absolute iron rule: back up the database before making any changes so you can roll back if things go wrong. Always test immediately after changes to see that everything still works the way it ought to. If you are at the stage where you feel confident about cleaning MySQL, consider setting up an entirely separate development environment for these tasks, and offline testing.

 

Leave a Reply

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