PDA

View Full Version : deleting spam accounts


zmanda_it
10 Jul 2008, 19:06
I'm running into a problem where spammers are using our forum to host spam in the "About Me" area of their account. Somehow an automated bot has been able to register hundreds of accounts using an easily-compromised mail host in russia. The originating IP addresses are mostly (if not all) Russian. There are too many to delete by hand, and vB doesn't have an option to mass delete by search criteria. I'd be willing to do cleanup by hand if I could get the vast majority of accounts in an automated fashion. Here is an example SQL query that returns 560 results:

select userid,ipaddress,lastvisit,lastactivity,posts from vb3_user where email like '%mail.ru' and posts = 0 and abs(lastvisit - lastactivity) < 3600 order by ipaddress;

These are all accounts whose registration and last activity are nearly identical, who have zero posts, and are registered using the same mail host in Russia. vB support states that user accounts are deleted from the following tables:

user
userfield
usertextfield

then, additionally, every table with a userid field has to be gone through and fixed. However, these accounts have no activity and zero posts, so is it safe to assume that all tables other than the 3 mentioned are clean w/respect to the accounts mentioned? Is it safe to delete the accounts with matching UIDs directly from the 3 mentioned SQL tables and be done with it?

Thanks

cheat-master30
10 Jul 2008, 21:14
If you don't have any members from Russia, the chance of a legitimate member using mail.ru is slim, so delete the accounts. Not sure about whether to use SQL queries to do this directly, but delete them. Oh, and it may then be wise to add the domain to the banned email addresses list.

zmanda_it
10 Jul 2008, 22:51
Thanks for your reply and suggestions. I do, however, need a way to automate this deletion... maybe I posted in the wrong forum?

cheat-master30
10 Jul 2008, 23:23
Possibly, because removing them is an administrative or a coding issue.

zmanda_it
11 Jul 2008, 01:09
The acccounts in question are not banned at this time, but I found a way to determine which accounts are actually hosting spam:

SELECT username,t1.userid FROM vb3_user t1 JOIN vb3_usertextfield t2 ON (t1.userid = t2.userid) WHERE t2.signature like '%http%' or signature like '%href%' or signature like '%src%' or signature like '%url%' or signature like '%buy%' or signature like '%img%';

In my case, that brought the total number down to 37, which I can delete by hand. Hopefully this helps someone else.