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 ;
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
Enjoy!
