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:
-
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:
-
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!
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.
September 8th, 2009 at 1:07 pm
[...] Pode consultar o artigo completo aqui. [...]
September 28th, 2009 at 12:14 am
[...] 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 [...]