Mass deleting spam users

[Rakkar]

Running SMF 1.1.11, I went to my board this morning to find the board overrun by spam posts. I checked the user logs and it looks like over some large period of time there have been thousands of user accounts created, each with spam in the signature, but most of whom have never posted.

Apparently something happened this morning where many or all of the accounts all posted spam at the same time. One of my moderators went through and deleted every spam post (thanks to him!).

This is using Cavecost Captcha 1.0 and Anti-Bot Registration Puzzles 1.2

There's just too many users to delete through the regular admin page. They are intermixed with real users so it's harder to wipe things out. What I'd like to do is run an SQL query that just deletes every user that currently have 0 posts, and has created an account within the last say 30 days.

My first question is, how do I do that? I'm looking at the database now but don't know the query to run.

My second is, does anyone have recommendations on how to prevent this from happening again?

[YogiBear]

When you delete their accounts click the option to delete all posts associated with that account too.

Enable Moderation boxes. This makes it easier to remove threads.

Get your moderators on the case. Apart from above, it's housekeeping from now on.

Sorry to make it all sound so stark but that's what removing spam is really about.

There are many mods you can install to make it more difficult for them to sign but one of the most effective I've found is Verification Questions.

[Rakkar]

I appreciate the response but as I said there are THOUSANDS of spam users. It's like like there are 5 and I said thousands, there ARE thousands. So I need the SQL query.

Thanks for telling me about Verification Questions by the way. I will try that.

[busterone]

Admin- members- search for members
set the search parameters, then click search.
check on all the results and then click Delete Selected Members at the bottom

[JimM]

We normally don't recommend directly editing the database.  Instead the method that busterone recommended is the safest way.  However if you insist on using a SQL querry, here's one to try.  Make a backup of your database before trying this as I have not tested it.

Code Select Expand

DELETE FROM smf_members WHERE posts=0;

Of course if your database prefix is not smf_ replace that part with your prefix.

If you want to see how many rows this querry will delete, run this before you run the delete one.

Code Select Expand

SELECT count(*)
FROM smf_members
WHERE posts=0;

[Rakkar]

Thanks for all the replies. In the end I just hunkered down for 20 minutes and deleted page after page of accounts after sorting by posts. Would be good to have an option to deal with this in the future (for example, delete all accounts with X posts).

[JimM]

That's the recommended method.  You could have changed the lines per page as well to make them longer which might have shortened it a bit.  Anyway glad you got it solved.  Don't forget to mark the topic solved by clicking the link at the bottom left of the page.

[jobarjo]

Hi

How do you change the "lines per page" setting?
I have 3500 spam accounts to delete

Thanks

[Illori]

jobarjo please open a separate topic for your issue, it will get lost here as this topic belongs to someone else and is marked solved.

[Topman]

At least it is good to know we all have to suffer this.

Is it possible to send the banned IP addresses to stopforumspam.com I wonder.

[spiros]

This mod could help
http://custom.simplemachines.org/mods/index.php?mod=2569

[airtetdotin]

thank you just with smf 2.04 not worked for me

[Chalky]

thank you just with smf 2.04 not worked for me

Nobody will see your post here.  If you want a reply please start a new topic explaining your problem fully