Spam_Killer MySQL StoreProc for WordPress
My latest solution to WordPress comment spam:
The basics:
1. Create a table (spam_words) with 1 field (word) containing your master back list, so to speak, of all spam words.
2. Create a stored procedure (Spam_Killer) that can easily be executed at will or via a script to iterate through your spam list and mark all comments as spam that match.
Spam_Words table:
CREATE TABLE spam_words (
word VARCHAR (20), PRIMARY KEY (word))
Set the field (word) to be the primary key, thus minimizing duplicate entrees.
Spam_Killer stored procedure:
CREATE PROCEDURE Spam_Killer()
BEGIN
DECLARE record_not_found INTEGER DEFAULT 0;
DECLARE spam_word VARCHAR(50) DEFAULT “”;
DECLARE spam_cursor CURSOR FOR SELECT word FROM spam_words;
OPEN spam_cursor;
the_loop: LOOP
FETCH spam_cursor INTO spam_word;
IF record_not_found THEN
LEAVE the_loop;
END IF;
UPDATE wp_comments SET comment_approved = ‘SPAM’ WHERE comment_author LIKE CONCAT(“%”,spam_word,”%”);
UPDATE wp_comments SET comment_approved = ‘SPAM’ WHERE comment_content LIKE CONCAT(“%”,spam_word,”%”);
END LOOP the_loop;
CLOSE spam_cursor;
END
Executing a MySQL StoredProc:
Call Spam_Killer
Notes:
I call the stored procedure in a master script file that runs every night automatically. The master script not only cleans spam but also optimizes tables, deletes post revisions and performs other various database tasks.
