Deleted Spambots

[kung foo man]

Hey all,

I've just deleted like half of the bots from the user database. The query is quite simple, but seems like it got only bots:

delete from user where posts = 0 and LENGTH(homepage) > 0

I've looked through the whole data set with HeidiSQL, but with 3430 rows, I might have also deleted a "real" account. Nonetheless, it would be a zero poster, not a big loss.

For later analysis, I saved all the deleted accounts as .sql and .html, so anybody who feels for a little bot detection algorithm, have fun. ^^ I can use that then to delete the bots with no homepage set, but based on the same subnet or email substrings e.g.

There are still like 3000 other accounts, which might be bots. I don't know yet why http://www.stopforumspam.com/ didn't detect all those bots, gotta lookup that issue later.

In register.php I have this:

		    $spamcheck = file_get_contents("http://www.stopforumspam.com/api?ip=$ip");
    if (strpos($spamcheck, "yes"))
        die(); 
	

Some example queries I figured:

select userid, username, homepage, email, posts, ipaddress   from user where posts = 0 and email like "%mini.reopenetic.com%"
delete from user where ipaddress LIKE "%104.128.138%"

[IzNoGoD]

Please dont use the % in front of that ip. Would delete legit users at xxx.104.128.138 too

[kung foo man]

No fear, before I delete stuff I first do "SELECT *" of it to check the result ;P