Banning the spammers

I am getting really tired of the trackback spam. I was looking around in my MySQL administrator and found the tables that Movable Type uses to track tentative trackbacks (mt_tbping) and banned IP addresses (mt_ipbanlist). I wrote up a little SQL to copy IP addresses from unapproved trackback locations to the banned list:

INSERT INTO mt_ipbanlist
(ipbanlist_blog_id, ipbanlist_ip)
SELECT MY_BLOG_ID, tbping_ip
FROM mt_tbping
WHERE tbping_ip <> 'MY_HOST_IP_ADDRESS'
AND tbping_ip NOT IN (
	SELECT ipbanlist_ip 
	FROM mt_ipbanlist
	WHERE ipbanlist_blog_id = MY_BLOG_ID)

Of course, I'll have to check that all unapproved trackbacks are spam first, but I am hoping that the volume drops in time.

2008-08-13: added my list of banned IP addresses, trackback spammers one and all.