Keeping comment spam under control with MovableType

Other than resource issues related to DOS-style spam attacks, comment spam isn't that big of a problem around here because:

1. I've installed MT-Blacklist.

2. I use MySQL to quickly delete large numbers of spam comments.

Regarding #2, here are some common commands. You'll need to connect to your server using SSH, login to your account, and then login to MySQL by typing something like:

mysql -u [your MySQL user name] -p [the name of your MT database]

So, if your MySQL username is 'fred', and you keep your MT information in the database 'mydatabase', you'd enter:

mysql -u fred -p mydatabase

Then, at the prompt you'd enter your MySQL password.

To see a list of all the distinct URLs used in trackback pings you've been sent, type:

select distinct tbping_source_url from mt_tbping;

To delete all trackback pings that link to a specific URL, replace in the following with the bad URL; be very careful:

delete from mt_tbping where tbping_source_url = '';

The following is similar to the last, except it uses a substring instead of a complete URL. Be very careful since this uses just part of the URL, not the whole URL. So, if you replaced 'spammername' with 'a' you'd delete all trackbacks that contain the letter 'a' in their URL, which is probably something you don't want to do:

delete from mt_tbping where LOCATE('spammername', tbping_source_url) > 0;

The following is untested, but might turn off trackbacks on all posts. Change the 1 to 0 to turn them back on:

update mt_trackback set trackback_is_disabled = 1;

The commands dealing with comments are similar:

select distinct comment_url from mt_comment;

delete from mt_comment where comment_url='';

delete from mt_comment where LOCATE('spammername', comment_url) > 0;

Use these commands at your own risk. Backup your database before using them.