Using Drush and SQL to Delete Spam Comments
spam filters fall apart; the centre cannot hold.
Spam Gets By …
From time to time, despite precautions, spammers get by the Drupal filters and captchas. Or perhaps, the problem gets put in your lap and you have the pleasure of deleting thousands of spam comments.
The Drupal Administrative interface doesn’t provide many resources unless you enjoy clicking “select all” and “delete selected comments”, page after page, 50 comments at a time.
Drush to the Rescue
But, if you are comfortable with sql, one easy solution is to use drush sql-query to delete the comments.
Before you start Deleting anything…
If you are unfamiliar at all with sql, first back up your drupal database.
You could also dump just the comment table. (The ‘~’ says to save it in your home directory.)
Now let’s get busy!
First take a look at the comment table:
( drush sqlq is the short form of drush sql-query)
You could also use drush sql-cli to view the comment table:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
Next take a look at the comments:
You can show comments from the last 30 days with the following.
The created field is stored as a unix timestamp (seconds since the Unix Epoch), so to get last month timestamp:
Delete comments within a range of comment ids.
If you know the range of spam comment ids, you can give sql a range of comment ids:
Show the range of comments:
Delete the range of comments:
Show comments, group by name of commenter
But, honestly, there’s probably a drush delete-comments module that needs to be written!
I’ll keep you posted of my progress on that. In the meantime, you can try using the previous commands. Just remember to backup your database first, its only a single drush command after all!