WMD against forum spam

[Panzer Max]

We have a lot of spam accounts in our db. 99% of them join and never log in again, they simply have spam in their sig or homepage. I want to make a query that will find all the users with a signature or homepage, who have less than 2 posts, and their last activity is 24 hours from their join date.

SELECT U1.userid, U1.username, U1.homepage, S1.signature
FROM user AS U1, usertextfield AS S1
WHERE U1.userid = S1.userid
AND U1.joindate >1291165261
AND ( U1.lastactivity - U1.joindate ) < 86400
AND U1.posts < 2
AND S1. signature NOT LIKE ''
ORDER BY U1.username


I think that query will isolate the spammers who sign up with spam sigs and rarely return after joining, from the real people who may not post but do return. I could make a similar script for homepages.

Then I want to run an UPDATE query to change their userid from 2 (registered usergroup) to 99 (spammer usergroup). And then I can go into the ACP>Users>Move and Prune and delete all members of that usergroup.

What would the UPDATE query be to do that? This seems like it would be universally helpful to vB owners fighting spam. :)

[Disasterpiece]

Every user should have a primary usergroup field in the user table if im Not completely Mistaken.

BTW: least of my known spambots use a signature and they don't Post at all. Hmmm

[Panzer Max]

Oh, you're right, I forgot to include usergroupid:

SELECT U1.userid, U1.username, U1.usergroupid, S1.signature
FROM user AS U1, usertextfield AS S1
WHERE U1.userid = S1.userid
AND U1.joindate >1291165261
AND ( U1.lastactivity - U1.joindate ) < 86400
AND U1.posts < 2
AND S1. signature NOT LIKE ''
ORDER BY U1.username

Then I want to run an UPDATE query to change their usergroupid from 2 (the registered usergroup) to 99 (the spammer usergroup). And then I can go into the ACP>Users>Move and Prune and delete all members of that usergroup.

Yes, you would be surprised how many spam accounts there are, sitting with no posts but spam links in their sigs and homepages...and these are magnets for more spam. Afterall, if the spammers see you are a willing spam depository, they won't leave you off their list!