Spam problem not quite solved.

[appel]

Can't we locate the persons behind these spam bots, and just physically beat the hell out of them? 

[princec]

Simply delete any accounts that don't post after 7 days.

Cas

[Riven]

Simply delete any accounts that don't post after 7 days.

Cas

As said earlier, deleting an account is not simply deleting a record from the jgoforums_members table. There are foreign keys pointing to those members, and by removing the record (including primary key) you can get into a lot of trouble, to the extend that the forum stops working.

I therefore use the 'ban this account' built into SMF, feeding it the result of the query that figures out all accounts without posts.

1   2   3   4   5   6   7   8

SELECT ID_MEMBER FROM jgoforums_members WHERE NOT EXISTS (    SELECT *    FROM jgoforums_messages    WHERE jgoforums_members.ID_MEMBER = jgoforums_messages.ID_MEMBER    ) AND `dateRegistered` < UNIX_TIMESTAMP() - 7*24*3600

Removing accounts one at a time, will (eventough automated) probably take a while and may have to be throttled, so I'm probably going to put it in a cronjob.

Besides that, it's not a solution to the spam problem.

Games published by our own members! Check 'em out!

[princec]

hm slightly cranky database design... I suppose it doesn't come with on delete cascade etc.

Cas

[jezek2]

What about people that are registered so they can track new posts? For example, I had posted my first post after over year since I registered account here.

You can't just blindly delete all accounts with 0 posts. What about some check for links in signature and last login activity? I think people who register just for tracking wouldn't set their signature right away (at least majority), but I can be wrong. Maybe just checking the last login date would be sufficient.

[princec]

Yes, that's a better idea. Basically vape dormant accounts.

Cas

[kappa]

What about people that are registered so they can track new posts? For example

You should use a RSS feed

[Riven]

Okay: who exactly are we helping by deleting unused accounts? It certainly doesn't have noticeable impact on the forum performance.

There are 20.000 accounts without posts and without personal messages and without topic notifications.

After a cleanup, there would be 10.000 accounts remaining. I doubt anybody would notice the difference.

So why not put all effort in the spam problem itself, instead of the lingering accounts that might or might not be created by spammers. I couldn't really care less about signature spam in some far away page that will have an extremely low pagerank, as there are hardly any links to it.

[kappa]

Out of the 30341 members that would get rid of 23971 (accurate at the time of posting ).

Thats more then 79% of members, further a few thousand (at least 5k) can probably be shaved off as they are inactive or dead (haven't posted anything or logged on in years, and have less then 5 posts).

A cleaner database is a happy database

[Riven]

A cleaner database is a happy database

The database doesn't really care, and removing accounts can cause a lot of lurkers to loose their account. I am already receiving personal messages from lurkers. I just don't see the advantages here, and if any, they are outweighed by the potential problems and inconveniences big time.

Games published by our own members! Check 'em out!

[TheAnalogKid]

To make life much harder to spam bots the forums should require a capcha validation on each post and more importantly on user registration. I know it's embarassing for honest users but that helps.

[ryanm]

Okay: who exactly are we helping by deleting unused accounts?

<clutches at straws>New users whose preferred name has already been taken?

Pretty weak I guess. If it's going to be a ball-ache it's probably not worthwhile. You can put lurkers' mind at rest though - the last-active-on record should shield them from any cull

[EgonOlsen]

For the forum on the jPCT-website (based on SMF 1.1.12 with its weak captcha too), i maintain a large ban list. With some experience and the help of http://www.stopforumspam.com/ you get a pretty good feeling of what to put on the ban list and which account to delete. Spammers' stategies change from time to time, but you can easily adopt to it. With that, i've reduced spam accounts from 10-60 per day down to 0-5. Of course, this may hurt some people whose mail addresses match the current spamming strategy by accident, but i can live with that. For example, i'm banning every account from registering where the mail address matches *[0..9]@gmail*, *[0..9]@live* and *[0..9]@hotmail* to ban all those dumbashell152326@gmail.com suckers who register using dynamic IPs so that you can't ban them by IP.

I tried to use alternative captchas for SMF but they all sucked in one way or another. The problem with doing your own is, that you have to modify the code again every update.

It's still annoying and if somebody plans to use a tank to roll over some spammers, please count me in.

[Nate]

I see we just got 15 spam posts. Rejecting any post by users with < 10 posts would fix that.

It is hardly a good idea to disallow posting for people with too few posts, that way they will never be allowed to post
(sorry, if I just explained a bad joke)

Haha... sorry, I meant "reject any post that contains links by users with < 10 posts".

[kappa]

Haha... sorry, I meant "reject any post that contains links by users with < 10 posts".

Might unfairly penalise people posting for the first time to show of their project, which a lot of members do in their first post.

[ShannonSmith]

Some other forums replace links in posts of new users with a message saying: "links for users with less than 5 posts disabled". After 5 posts the links auto-enable.

[Riven]

As those spambots easily make over 10 posts, that's not really effective.

I think it's best to hide links from new accounts for 24 hours (since registration).

[pjt33]

For example, i'm banning every account from registering where the mail address matches *[0..9]@gmail*, *[0..9]@live* and *[0..9]@hotmail* to ban all those dumbashell152326@gmail.com suckers who register using dynamic IPs so that you can't ban them by IP.

FWIW gmail addresses also work as whatever @ googlemail.com, so you might want to add that.

[EgonOlsen]

FWIW gmail addresses also work as whatever @ googlemail.com, so you might want to add that.

Yes, but spammers aren't using it that much ATM.

[kappa]

I think it's best to hide links from new accounts for 24 hours (since registration).

does it really matter if the links from spambots show or not? posts get cleaned up pretty quickly anyway, i think the bigger problem is them posting in the first place and their posts showing up which is really the inconvenience here.

[Nate]

i think the bigger problem is them posting in the first place and their posts showing up which is really the inconvenience here.

Exactly, that is why I propose not allowing the post if it has a link. Just removing the links isn't that helpful. It would probably be sufficient to require at least 1 post before you can make a post containing a link.

[Riven]

Yeah, like next time I will just ignore the posts that have nothing to do with the issue at hand -- like removing those ancient accounts.

[kappa]

Yeah, like next time I will just ignore the posts that have nothing to do with the issue at hand -- like removing those ancient accounts.

sorry, probably came across too harsh/rude there, wasn't the intention.

[Riven]

This is the plan:

In the post process of members with zero or one posts, a post with a link in it will be (silently!) rejected and thus will not show up on the forum. Instead, the contents of the topic will be sent to the emailaddress of the poster, explaining this is an anti-spam measure, and kindly requesting him/her to make a new post without the link(s).

It conveniently leaves the post count at zero, preventing the case that everything (including spamming) is allowed after N posts. I'm pretty sure only human spammers can get through -- you're never going to stop them anyway.

Anybody opposed to this? Please enlighten me of any downsides.

[ryanm]

Sounds good to me.

[woogley]

Is there any way to disable signatures for new users? Much of the spam in the past has been plain text, with the actual spam links in their signature.

[Riven]

Additional captcha in registration process:

http://www.java-gaming.org/index.php?action=activate;u=0;code=0

_{(naturally it won't work when you try to actually activate it, as there is no userid 0)}

[ryanm]

Well that seems to have worked, I haven't seen a spam post since
Bizarrely, there are still spam profiles getting through. I find it incredible that there are real people out there who have the knowledge to compile and run a java snippet who find it worthwhile to spam forums. Do we know if they try to post, or is their objective just to get the signature into the DB? Does the verification snippet change? Is it possible that the solution is being shared amongst spammers?

I also note that I've gained a "Delete this account" ability that purports to nuke a user and all their posts. Is this safe to use? Have the potential DB problems been resolved?
I have to admit, its presence makes me slightly nervous. I'm pretty sure I'm not going to abuse the awesome power it affords, but who knows what evil lurks in the hearts of men? It's like a big red button labelled "DO NOT PRESS"