Automatically cleaning up SPAM Wordpress comments

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]
DELIMITER $$

DROP PROCEDURE IF EXISTS `our_blog_db`.`REMOVE_OLD_SPAM`$$
CREATE PROCEDURE `our_blog_db`.`REMOVE_OLD_SPAM` ()
MODIFIES SQL DATA
COMMENT 'remove comentarios marcados como SPAM'
BEGIN

DECLARE done BIT(1) DEFAULT false;
DECLARE commtbl VARCHAR(50);
DECLARE comments_tbls CURSOR FOR SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'our_blog_db' AND TABLE_NAME LIKE '%comments';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;


OPEN comments_tbls;

REPEAT
FETCH comments_tbls INTO commtbl;
SET @next_tbl = CONCAT('DELETE FROM our_blog_db.',commtbl,'
WHERE comment_approved = "spam"
AND comment_date_gmt < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 15 DAYS)');
PREPARE get_next_tbl FROM @next_tbl;
EXECUTE get_next_tbl;

UNTIL done END REPEAT;

CLOSE comments_tbls;


END$$

DELIMITER ;
[/mysql]

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]
CREATE EVENT `EV_REMOVE_OLD_SPAM` ON SCHEDULE EVERY 1 DAY STARTS '2009-08-01 21:00:00' ON COMPLETION NOT PRESERVE ENABLE
COMMENT 'remove comentarios marcados como SPAM' DO
BEGIN

SELECT GET_LOCK('remove_spam',5) INTO @remove_spam_lock;

IF @remove_spam_lock THEN
CALL REMOVE_OLD_SPAM();

END IF;

END
[/mysql]

Enjoy!