Automatically cleaning up SPAM Wordpress comments

Ouvir com webReader

Doing the maintenance of our blogs (Wordpress), I bumped over one that had fallen on an active botnet. It was receiving like 5 or 6 spam comments per minute. It was nearly the only one in such an harassment, so I suspect the botnet loved it for being open on commenting.

Since I've activated reCaptcha I've been monitoring my "spam folder" and I'm really confident on his guesses, so I just wrote a STORED PROCEDURE to clean up these spam comments on a periodic basis, so I can do a sitewide cleanup:

MySQL:
  1. DELIMITER $$
  2.  
  3. DROP PROCEDURE IF EXISTS `our_blog_db`.`REMOVE_OLD_SPAM`$$
  4. CREATE PROCEDURE `our_blog_db`.`REMOVE_OLD_SPAM` ()
  5.     MODIFIES SQL DATA
  6.     COMMENT 'remove comentarios marcados como SPAM'
  7. BEGIN
  8.  
  9. DECLARE done BIT(1) DEFAULT FALSE;
  10. DECLARE commtbl VARCHAR(50);
  11. DECLARE comments_tbls CURSOR FOR SELECT TABLE_NAME
  12.     FROM information_schema.TABLES  
  13.     WHERE TABLE_SCHEMA = 'our_blog_db' AND TABLE_NAME LIKE '%comments';
  14. DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  15.  
  16.  
  17. OPEN comments_tbls;
  18.  
  19. REPEAT
  20.     FETCH comments_tbls INTO commtbl;
  21.     SET @next_tbl = CONCAT('DELETE FROM our_blog_db.',commtbl,'
  22.         WHERE comment_approved = "spam"
  23.         AND comment_date_gmt < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 15 DAYS)');
  24.     PREPARE get_next_tbl FROM @next_tbl;
  25.     EXECUTE get_next_tbl;
  26.  
  27. UNTIL done END REPEAT;
  28.  
  29. CLOSE comments_tbls;
  30.  
  31.  
  32. END$$
  33.  
  34. DELIMITER ;

It's very easy to stick it into an EVENT, if you have MySQL 5.1 or bigger, and which to do a daily clean up automatically:

MySQL:
  1. CREATE EVENT `EV_REMOVE_OLD_SPAM` ON SCHEDULE EVERY 1 DAY STARTS '2009-08-01 21:00:00' ON COMPLETION NOT PRESERVE ENABLE
  2. COMMENT 'remove comentarios marcados como SPAM' DO
  3. BEGIN
  4.  
  5. SELECT GET_LOCK('remove_spam',5) INTO @remove_spam_lock;
  6.  
  7. IF @remove_spam_lock THEN
  8.     CALL REMOVE_OLD_SPAM();
  9.  
  10. END IF;
  11.  
  12. END

Enjoy!


You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button

2 Responses to “Automatically cleaning up SPAM Wordpress comments”

  1. [...] Pode consultar o artigo completo aqui. [...]

  2. [...] permissions - we had an user wide open to spam, and that was when the automatic process of cleaning up the blog spam was implemented. In any case, this graph will evidence future similar situations which I hope are [...]

Leave a Reply