Heal Your Church WebSite

Teaching, rebuking, correcting & training in righteous web design.

Using MySQL with the MT-Blacklist to Auto Ban IP addresses

One of the downsides of increased blog popularity is an increase in comment spam attempts. Fortunately, using a variety of tools already at hand, I’m able to identify and block these persistent buggers automagically while I sleep.

The Problem

Last week, this blog enjoyed some linkage from the technical ‘A-list’ regarding my analysis SixApart’s since modified licensing schedule for MovableType 3.0. With this recognition came a flood of comment spam attacks. Fortunately, most entries were thwarted using Jay Allen’s MT-Blacklist plug-in; however a few employing intentional typos got through.

I had pondered some sort of trap for the spammers … in fact I still am. Until then, I need a means of automatically denying them access regardless of typos without denying read access to legitimate visitors. So I began to pour through my various log files for a solution.

MT-Blacklist Logs to the Rescue

One of the options the MT-Blacklist offers is to log failed entry attempts. I have this turned on so I can quickly peruse my MovableType activity log. In fact, it was this very feature that brought to my attention the stepped-up attacks on my system. It also brought to my attention that the few successful entries shared IP addresses with many of the failed entries.

You see, along with being greedy and lazy, spammers, especially comment spammers, are persistent. I know because it took a set of 5 ips four days to finally sneak some comments onto my system … after 87 failed attempts. It was at this point I realized what I could do the temporarily thwart the spamscum:

Crontab as SQL statement that inserts records into the MovableType mt_ipbanlist table from entries in the mt_log where a denied IP address shows up more than once. I then encapsulated the SQL statement in a Perl program so I could add bells and whistles at a later date.

Yes, I realize the LEFT JOIN clause is less efficient than a NOT IN subquery, but unfortunately, I couldn’t get this more efficient syntax to work with the crufty version of MySQL on my server.

The Aforementioned Bells and Whistles

Next step is to expand this program to find the most egregious sinners determine which ones are not based in North America, and deny them using my .htaccess file. Here’s the MySQL statement I’d use to find these bums:

SELECT DISTINCT count( log_ip ), log_ip
FROM mt_log
WHERE mt_log.log_message
LIKE “MT-Blacklist comment denial%”
GROUP BY log_ip
HAVING count( log_ip ) >4

Perhaps Jay Allen could be so kind as to add an option to the MT-Blacklist to automatically add IP addresses to the banned IP table? Or along the same lines, perhaps there could even create some sort of banned-IP list so I could share it with those using other blogging applications such as WordPress and pMachine?

That said, if you can juice it up the above code, leave a comment. I’d be interested in how you deal with it.

Comments are closed.