How to Purge Comment Spam from Drupal
I recently wrote about a spam comment problem I had that was so bad it effectively took down my website. Fortunately by rooting around in the database for my installation for a few minutes, I was able to remove all of the spam in bulk, rather than manually deleting the comments in batches of 50 or so (which is about all the Drupal interface will let you do in a single action).
Comment Table Structure
I want to talk briefly about how the comment table is structured in the Drupal’s database – don’t worry if this seems a bit to in-depth for you though, I’ll post templates for commands you can modify without a complete understanding of the table!
There are fourteen columns in the comment table, which are listed in the following table:
|
Name | Type | Description |
|
Cid | Int |
Comment id, a number which uniquely identifies a commenting |
|
Pid | Int | Parent id, points to the parent comment in the thread |
|
Nid | Int | Node id, points to the node (generally speaking, page) that the comment is on |
Uid | Int |
User id, an identifier for the user (0 if anonymous) |
Subject | Varchar |
Subject of the comment |
|
Hostname |
Varchar | The IP address/web address of the commenter |
|
Changed | Int | Last time the comment was modified |
|
Status | Tinyint | Indicates if the comment has been approved |
|
Thread | Varchar | Manages the thread sorting |
|
Name | Varchar | Name of the commenter |
|
| Varchar | Email of the commenter |
|
Homepage | Varchar | Homepage of the commenter |
|
Language | Varchar | Indicates the language in the comment |
Created | int | Date of creation |
It’s possible that if you have comment related modules, there may be more columns, or columns may have been modified, but my expectation is changing of the columns will be very rare.
Bulk Cleaning Spam Comments
I’m going to assume that the reader knows how to execute a query on their Drupal database, and just provide some basic queries and their descriptions.
WARNING: Please backup your database before running these commands! A mistake could permanently destroy content!
Deleting All Unapproved Comments
If you had your comments set to only be posted after they were manually approved, then you can simply mass delete unapproved comments. To do this, simply run:
DELETE FROM comment WHERE status = 0;
Deleting All Comments After a Certain Date
Since Drupal stores its dates in epoch form (basically, as a single number), you’ll need to either grab the created datestamp from the first of the offending comments, or else use a converter like the one at http://www.esqsoft.com/javascript_examples/date-to-epoch.htm to get the date in the right format.
Once you have the date in Epoch time, you can delete all comments after that date-time with (replace 1310022907 with your example):
DELETE FROM comment WHERE created > 1310022907;
To delete that time as well, just add an equals sign after the greater than sign:
DELETE FROM comment WHERE created >= 1310022907;
Deleting All Comments with Specific Text in a Column
You can also delete a comment based on the text in a given column. For example, if all (or a large fraction of) the spam comments had “Ugg Boots” as the subject, you could delete all of them with:
DELETE FROM comment WHERE subject = “Ugg Boots”;
In general, you could do this with any of the columns that are varchar, allowing you to target the spammer’s email, hostname, or any other attribute.
Resetting Comment Count on Pages
Resetting the comment count on pages seems to simply require running Drupal’s cron manually, which gets Drupal to rerun its statistics.