I just thought this might be handy for anyone else running a board. I scan for comments that have been backfilled with spam (editing previously dropped seemingly-innocuous comments with spam links) as well as signatures by people that have been edited after they've dropped messages and come back later. It's very simple, but handy.
First, get rid of bonehead signatures:
update smf_members set signature='No links in signatures please' where posts < (your threshold)
Set this to run daily automatically.
Second, show me all posts where there is a link, the message has not been moved to our private Spam and Quarantine board and posters have less than (a threshold) of posts:
select body
from smf_messages, smf_members
where
smf_messages.body like '%[url%' and
id_board <> (your spam board ID_BOARD value) and
smf_members.id_member = smf_messages.id_member and
smf_members.posts < (your threshhold)
Using phpMyAdmin it's easy to see what you've already allowed through and what makes sense, vs what is selling something and pointing where it shouldn't. There is a subtle problem with running this automatically and having a cure in it. You may well have people that have less than (your threshold) posts but the message is valid and/or desired. So you can add a field called "approved" and set that to true, once you've approved the post (thereby excluding it in the future). But if you do that, you risk that same person (being the enormously clever spammer they are) coming back and backfilling that very post with spam. Once it's approved in that model, it would be invisible to you. So although it takes a bit longer, I just look through them and know which ones to ignore.
