SQL code to find forum spammers

[Megiddo]

Putting this here so I don't lose it again:

Code: [Select]

SELECT * FROM smf2_members WHERE verified_not_spammer=0 AND posts=0 AND (signature!="" OR personal_text!="" OR website_url!="" OR website_title!="")

I use this on the SQL database from time to time to check for spam accounts.

[Megiddo]

We changed one of the registration questions to "What is the name of our admin mod? (Ends in X)", because the bots were blowing past the math question. Hopefully this helps.

[Stickly Man!]

For those of you curious, we just went on a spambot rampage again today and removed quite a few accounts   I brought it up because in the past 8 days we had 47 new accounts register, and only 12 of those were legitimate. 75% ratio of spam bot to real registrations is just too much :S

[JamminR]

Are any of you versed enough to write a SMF mod (or plain SQL/php addition*) that would remove periods from email address userids when doing the duplicatioon check/comparing to the rest of the user email addresses?
I've noticed while doing some of my own cleaning that many of the gmail userid addresses are the same, just with different period placement.
someuser@gmail
some.user@gmail
s.o.m.e.user@gmail
All go to the same box using Gmail's alias coding.

* = SMF mod would be better for all SMF forum community members if uploaded to the SMF repository.

[Megiddo]

I tried creating such an SQL query but ended up crashing MySQL... I don't use SQL frequently enough to know the answer to this one.

I'm not really worried about extraneous, harmless accounts though. I think the query posted in the top gets all spammy accounts.

[Stickly Man!]

Well, it has been 3 days since we added the ULX question to registration, and so far not a single bot registration. I am pleased!   

In case spammers figure out the answer to the question and incorporate it with their bots (if it's possible and if they think it's worth it), we should come up with a bank of similar questions that we can switch to when spambots start flooding in again.

[MrPresident]

How are we doing on spam account registrations still?

On my forum a few months back we had so many spam accounts that there was literally 20 spam accounts for every one legit account with 20-40 being registered every day.. it was sickening. Most of them wouldn't post, thank goodness, but it was still annoying.

One of my coders added the steam's OpenID verification to our registration template so that before a new user could register they had to login through the steam community portal. Since then, we've not had a single spam account registration at all.

It was kind of crazy to require that our users have a steam account.. but then I got to thinking.. we're a Garry's Mod community primarily and everyone who comes to our forums has Garry's Mod which means they have steam.

Probably the best decision we've made for spam filtration ever.

[Stickly Man!]

As the guy that still checks every new account (lol), I think since we've set up the simple question, we've only had maybe 3 spam accounts get registered, and it was likely that they were set up by humans rather than bots. Pretty good for a simple little question 

Steam account idea does sound quite interesting though, not sure about Megiddo's thoughts on setting it up here.

[Megiddo]

Steam login isn't worth the trouble if we've only had three spam accounts in as many months. Plus as long as I run that query once in a while, it picks up any accounts we may have missed...

[MrPresident]

No, 3 over the last few months is actually not bad at all. And yeah, the problem is that anymore, spam accounts are being created by humans as opposed to bots. It's getting trickier to fool them. Extra verification is really one of the only ways, but then you run in to the trouble of possibly alienating potential members. The internet is a huge place full of crazy and wonderful things!