Dealing with SPAM on Drupal

By Drupal, How to, Web Design

SPAMMERS are becoming more and more sophisticated, recently we had to support a client’s website protected with Mollom, although Mollom does a good job, it’s not enough, we found there were already a great amount of these annoying messages, we found that even reCaptcha module challenges already set were bypassed by robots, in any case we changed some rules:

We set the basic Captcha image as replacement for the ReCaptcha and we configured it in such way that the characters shown were really difficult to guess, of course, I’m not saying that this will stop all the SPAM but we found that it was a better option

However, we still had a problem, what to do with the existing messages, if you go to the Drupal Admin area and search for the comments, it shows you a list of 25 items, and spending hours and hours removing the messages manually may not be an option, so we acted directly on the DB (after backing it up):

In Drupal there are 3 tables storing the comments data:

  • comment
  • field_data_comment_body
  • field_revision_comment_body

So, you have to remove data from all of them to avoid leaving garbage in your DB, to start we located the SPAM messages, in our case it was simple, all of them were SPAM but we didn’t want to remove already approved comments, so we removed the new message by issuing this command:

DELETE FROM comment WHERE status = 0;

As you may know, there are no real reference integrity with Drupal DB, so although records in the other two tables are related to the records deleted from the comment table, you won’t have any alert with the statement above

Next, we removed the records associated in the other two tables:

DELETE FROM field_data_comment_body WHERE entity_id NOT IN (SELECT nid FROM comment);
DELETE FROM field_revision_comment_body WHERE entity_id NOT IN (SELECT nid FROM comment);

Pretty easy, no? As you can notice, the trick is to find the comments that are not real messages from user, and of course you can have a different configuration for your comments

Hope you find this useful 😉

Tagged under: