Deleting Unapproved Comments In Drupal 7

I recently came under a spam attack that gave me a bit of a problem to sort out. Over the course of 24 hours my blog received over 50,000 comments, all of which were utterly useless. What was good was the fact that my tiny little VPS server managed to stay available for most of the attack.

Due to the vast number of comments the normal administration form in Drupal became a little useless. I could only delete 25 comments at a time, so after an hour of this I decided that I needed to run a few SQL statements to clear out all of the unwanted comments in the database. I thought I would write up the commands I used in a post.

The first thing to do was to delete any unapproved comments from the comments table.

DELETE FROM comment WHERE status = 0;

Next, the comment body fields in the field_data_comment_body table also need to be removed. This was done by also cross referencing any missing comments from the comment table.

DELETE field_data_comment_body FROM field_data_comment_body
LEFT JOIN comment ON field_data_comment_body.entity_id = comment.cid
WHERE comment.cid IS NULL;

The comments also have revisions, which need to be deleted in the same way.

DELETE field_revision_comment_body FROM field_revision_comment_body
LEFT JOIN comment ON field_revision_comment_body.entity_id = comment.cid
WHERE comment.cid IS NULL;

I should note here that if you are doing this at home and you have added any other fields to your comments then you will have to look out for those tables as well.

Finally, if you have Mollom installed then you can also truncate the Mollom log table. This can be quite full of log records from the comments that you just deleted.

TRUNCATE TABLE mollom;

Incidentally, according to the Mollom reports the amount of spam I received over the weekend would have been closer to 300,000 comments. So thanks to Mollom it could have been a lot worse.