Worst Offenders for Wordpress 2.5 - Pre-Alpha

March 13th, 2008, by Rich.

I’m in the process of rewriting the Worst Offenders plugin for the soon-to-be-released WordPress 2.5. Before I make a tested and polished version of the code globally available, I’d be interested to hear from anyone who’d like to alpha test it.

As before Worst Offenders works cooperatively with other anti-spam plugins: its primary purpose is identifying and deleting the comments that are 100% definitely spam (sent by the very worst offenders) so that any “false positives” (sent by real genuine humans) can be rescued from the spam bin!

Download it from
WordPress.org

I’ve got it working on this site already, where it’s proved faster than the previous versions - it also has a nicer user interface. There are a few minor operational features that need to be finalised, but it’s basically capable of doing what it’s supposed to.

This version has a pluggable interface, so different “litmus tests” can be applied to spam at the same time, and third parties can easily write tests without having to write a whole interface.

The Worst Offenders v3.0.0.0alpha User Interface

I’m keen to hear from people who:

Know their way around Wordpress/PHP already.
Can take a look at the litmus test API and comment on ways to improve it.
Suffer from very high spam loads (hundreds or thousands per day) who’ll be able to give the existing litmus tests a bit of a workout to check if their SQL is as efficient as I hope.

Development SVN is being kindly hosted by Automattic and releases will be available here.

Chris Samuel Says:
March 22nd, 2008 at 1:00 pm

Forgot to say - that’s the current development version (you need to look under “Other Versions” link from the download page.

Chris Samuel Says:
March 22nd, 2008 at 1:18 pm

More information - I’ve noticed that there are SQL errors being generated by the attempts to create indexes, they’re not being picked up by your code but are getting reported in the Apache error log.

Here you go, these are all from just clicking on the “Update Options” button on the WorstOffenders Config page:

[Sun Mar 23 00:14:23 2008] [warn] mod_fcgid: stderr: WordPress database error FUNCTION wp2.wordcount2 does not exist for query SELECT wordcount2(comment_content, ‘http://’) as num, group_concat(comment_id separator ‘,’) as comment_id_list FROM wp_comments where comment_approved=’spam’ group by num having num >= 10 order by num desc; made by runCachedMatchesQuery
[Sun Mar 23 00:14:23 2008] [warn] mod_fcgid: stderr: WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”wp_comments’ ADD INDEX ‘ip_spotter’('comment_author_IP’)’ at line 1 for query ALTER TABLE ‘wp_comments’ ADD INDEX ‘ip_spotter’('comment_author_IP’); made by addIndex
[Sun Mar 23 00:14:23 2008] [warn] mod_fcgid: stderr: WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”wp_comments’ ENGINE = MyISAM ROW_FORMAT = DYNAMIC;
[Sun Mar 23 00:14:23 2008] [warn] mod_fcgid: stderr: \t\t\t\tALTER TABLE ‘wp_comment’ at line 1 for query
[Sun Mar 23 00:14:23 2008] [warn] mod_fcgid: stderr: \t\t\t\tALTER TABLE ‘wp_comments’ ENGINE = MyISAM ROW_FORMAT = DYNAMIC;
[Sun Mar 23 00:14:23 2008] [warn] mod_fcgid: stderr: \t\t\t\tALTER TABLE ‘wp_comments’ ADD FULLTEXT INDEX ‘content_fulltext’('comment_content’);
[Sun Mar 23 00:14:23 2008] [warn] mod_fcgid: stderr: \t\t\t\tCREATE FUNCTION wordcount2 ( a text, b VARCHAR(255) )
[Sun Mar 23 00:14:23 2008] [warn] mod_fcgid: stderr: \t\t\t\tRETURNS INTEGER
[Sun Mar 23 00:14:23 2008] [warn] mod_fcgid: stderr: \t\t\t\tCONTAINS SQL DETERMINISTIC
[Sun Mar 23 00:14:23 2008] [warn] mod_fcgid: stderr: \t\t\t\tRETURN (CHAR_LENGTH(a)-CHAR_LENGTH(REPLACE(a, b, ”)))/CHAR_LENGTH(b);
[Sun Mar 23 00:14:23 2008] [warn] mod_fcgid: stderr: \t\t\t made by addIndex
[Sun Mar 23 00:14:23 2008] [warn] mod_fcgid: stderr: WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘if exists url_spotter’ at line 1 for query drop index if exists url_spotter; made by addIndex
[Sun Mar 23 00:14:23 2008] [warn] mod_fcgid: stderr: WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”wp_comments’ ADD INDEX ‘url_spotter’('comment_author_url’)’ at line 1 for query ALTER TABLE ‘wp_comments’ ADD INDEX ‘url_spotter’('comment_author_url’); made by addIndex
[Sun Mar 23 00:14:23 2008] [warn] mod_fcgid: stderr: WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”wp_comments’ ADD INDEX email_spotter’('comment_author_email’)’ at line 1 for query ALTER TABLE ‘wp_comments’ ADD INDEX email_spotter’('comment_author_email’); made by addIndex
[Sun Mar 23 00:14:23 2008] [warn] mod_fcgid: stderr: WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”wp_comments’ ENGINE = MyISAM ROW_FORMAT = DYNAMIC;
[Sun Mar 23 00:14:23 2008] [warn] mod_fcgid: stderr: \t\t\t\tALTER TABLE ‘wp_comment’ at line 1 for query
[Sun Mar 23 00:14:23 2008] [warn] mod_fcgid: stderr: \t\t\t\tALTER TABLE ‘wp_comments’ ENGINE = MyISAM ROW_FORMAT = DYNAMIC;
[Sun Mar 23 00:14:23 2008] [warn] mod_fcgid: stderr: \t\t\t\tALTER TABLE ‘wp_comments’ ADD FULLTEXT INDEX ‘content_fulltext’('comment_content’);
[Sun Mar 23 00:14:23 2008] [warn] mod_fcgid: stderr: \t\t\t\tCREATE FUNCTION wordcount2 ( a text, b VARCHAR(255) )
[Sun Mar 23 00:14:23 2008] [warn] mod_fcgid: stderr: \t\t\t\tRETURNS INTEGER
[Sun Mar 23 00:14:23 2008] [warn] mod_fcgid: stderr: \t\t\t\tCONTAINS SQL DETERMINISTIC
[Sun Mar 23 00:14:23 2008] [warn] mod_fcgid: stderr: \t\t\t\tRETURN (CHAR_LENGTH(a)-CHAR_LENGTH(REPLACE(a, b, ”)))/CHAR_LENGTH(b);
[Sun Mar 23 00:14:23 2008] [warn] mod_fcgid: stderr: \t\t\t made by addIndex
[Sun Mar 23 00:14:23 2008] [warn] mod_fcgid: stderr: WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”author_fulltext’ (comment_author)’ at line 1 for query ALTER TABLE wp_comments ADD FULLTEXT INDEX ‘author_fulltext’ (comment_author); made by addIndex

Rich Says:
March 22nd, 2008 at 3:04 pm

I’ve removed references to roll_call (I need to find a better way of setting configs, probably within the “content” method).

Chris Samuel Says:
March 22nd, 2008 at 10:01 pm

Hi Rich,

I’ve emailed you a patch for the current devel version that fixes all those MySQL bugs and results in all the indexes getting created correctly.

I’ve gone from it finding 2 messages to finding 49 that it can deal with as it’s now picking up 46 in the MultiLink category it wasn’t seeing before.

All 49
IP 0
MultiLink 46
Domain 0
Email 0
MD5 0
Name Length 2
ObviousName 1

ovizii Says:
April 8th, 2008 at 12:03 am

Plugin could not be activated because it triggered a fatal error.
Parse error: syntax error, unexpected T_CLASS in /var/www/web6/web/wordpress/wp-content/plugins/worst-offenders/classes/litmus.php on line 3

using wp 2.5

Rich Says:
April 8th, 2008 at 4:48 am

Thanks for the report ovizii. I’ve just checked in a modified version which may should help.

AJ Says:
April 11th, 2008 at 10:33 am

Hey Rich,

I’ve been using Worst Offenders since forever and love it.
I have recently switched to Defensio and was wondering if you could make this work with Defensio too (defensio.com)

I would love to start using this plugin again. Thanks a ton for the plugin :)

Rich Says:
April 11th, 2008 at 10:36 am

Sounds interesting… It should be fairly easy to write a defensio litmus test for Worst Offenders. If nobody else has a crack at it, I’ll take a look.

AJ Says:
April 11th, 2008 at 10:59 am

Awesome.. I’ll be waiting for it :)