Delete spammers and their posts via SQL?

[Pseudonym]

Hey guys,

After I went AWOL from my forum for a few years, my site was completely overrun with spammers. Now I’m trying to clean it up, and already I’ve deleted hundreds of thousands of spam posts (literally – half a dozen spammers had ~50,000 posts each!) 😬

Anyway, I’m now cleaning up the long tail of ~1000 spammers who’ve made 50 posts or less. I’ve identified these on a spreadsheet, and am now ready to build my SQL query to do some mass culling.

My question is… what’s the best way to tackle this? I can think of two possibilities.

1)    Replicate the “Flag as spammer” function
If I could use SQL to flag as a spammer, I could then use the built-in IPS functions to remove the spammers and their posts, so I can be sure it’s done cleanly and properly. Problem is, I can’t see how to flag as spammer with a simple SQL command. I was hoping there’d be a “is_spammer” field in a table somewhere, but haven’t found that yet. Can anyone tell me how function this works?

2)    Manually delete the spammers using SQL
In this case I’d delete spammers from the ips_core_members table, and their posts from ips_forum_posts and ips_forums_topics. This is easy, but it doesn’t feel like the “proper” way. I’d be nervous about other records not being updated and causing database inconsistencies.

I had originally thought I’d assign my spammers to a new member group, then search and Purge through the ACP. Unfortunately, Purge seem to delete a member, but not their content. So that’s not a solution.

Can anyone advise me on the best approach here? Thanks in advance.

[teraßyte]

Do not delete them with a SQL query. Member data is all over the place in multiple tables, if you delete it only from the members and topics/posts tables you'll be in for a lot of weird issues all over the place.

 

The best solution I can give you is to create a new group in ACP (call it TO DELETE or whatever), and then update the member_group_id column for all those members you've identified to the ID of that new group. Once that's done filter your members in ACP based on the group, and then prune them from the member search results page.

[Pseudonym]

Yeah, weird issues is exactly what I was afraid of... hence my hesitancy!

The ACP members group pruning technique is what I was talking about here:

1 hour ago, Pseudonym said:

I had originally thought I’d assign my spammers to a new member group, then search and Purge through the ACP. Unfortunately, Purge seem to delete a member, but not their content. So that’s not a solution.

Apologies, I called it "purge", not "prune" (I mean, I was pretty close!). But as mentioned, the problem here is that prune only removes the members, not the posts. So I'll still be left with with thousands of spam posts, just from guest users. Unless there's some way I can follow up the member pruning with a clean-out of guest posts? 🤔

[Marc Stridgen]

There isnt really any way around that unfortunately. You would need to mark them as spammers individually which would remove the content alongside them. I realise you are looking for a quick solution there, but there isnt one available I can recommend 

[Miss_B]

9 hours ago, Pseudonym said:

So I'll still be left with with thousands of spam posts, just from guest users.

Imo the posts and topics of guests are easier to delete with the sql queries without causing much issues. This would be the better way under the circumstances. 

[wegorz23]

11 hours ago, teraßyte said:

Do not delete them with a SQL query. Member data is all over the place in multiple tables, if you delete it only from the members and topics/posts tables you'll be in for a lot of weird issues all over the place.

u have rights it is because other tables store last comment of topics, count numbers by comments and pagination on topics and other stuff

[Marc Stridgen]

2 hours ago, Miss_B said:

Imo the posts and topics of guests are easier to delete with the sql queries without causing much issues. This would be the better way under the circumstances. 

We would absolutely not recommend this

[wegorz23]

The easiest wait is to move that spammers to some member group and then on ACP panel remove them that profiles or just filter that in ur ACP panel and remove one by one.

We ban some spammers accounts every day and mark them as spammer. (it is because it hide all posts and block that account)

 

14 hours ago, Pseudonym said:

(literally – half a dozen spammers had ~50,000 posts each!) 😬

to solve problem we installed cleantalk plugin and it fixed ur problem like in 99% of the time. 

 

Edited July 19, 2023 by wegorz23

[Pseudonym]

Thanks for the replies, everyone. Knowing what NOT to do is also helpful.

12 hours ago, Marc Stridgen said:

You would need to mark them as spammers individually which would remove the content alongside them.

Cheers Marc. I like the sound of marking them as spammers, and letting IPS take care of the cleanup. The only problem I have with this is the word "individually". 😄 There's about 1000 spam members, so anything that requires deleting one by one is not an option.

Let me reframe my question...

When I flag a user as a spammer through the ACP, what happens? It seems to load /admin/?app=core&module=members&controller=members&do=spam&id=XXXX&status=1&csrfKey=XXXXXXXXXXXXXXXXXXXXXXXXXXXX

Presumably this runs a function in the IPS core... how I can tap into this? Can I create a standalone PHP script that loads all my spammer user IDs into an array, loops through them, and either runs the Flag Spammer function, or just loads this URL?

Thanks in advance.

 

@wegorz23 - Cleantalk sounds great, but it seems like it might stop new spammers, but won't clean up my current ones?

[Marc Stridgen]

10 hours ago, Pseudonym said:

Presumably this runs a function in the IPS core... how I can tap into this? Can I create a standalone PHP script that loads all my spammer user IDs into an array, loops through them, and either runs the Flag Spammer function, or just loads this URL?

This would be a development question in which Im not really able to answer. I cant see any reason why not, but as mentioned, Im not a developer on the platform. You would be best to post that in the developer connection forum, if you are looking to tack onto that

[Miss_B]

On 7/20/2023 at 12:20 AM, Pseudonym said:

Presumably this runs a function in the IPS core... how I can tap into this? Can I create a standalone PHP script that loads all my spammer user IDs into an array, loops through them, and either runs the Flag Spammer function, or just loads this URL?

I 'have coded an app that will do that. It will display all members who have been flagged as spammers in the ModCP->Member Management area. It has a quck select all button where you can either delete and/or unflag all the selected members marked as spammers. You can decide what to do with their post content from the app 's settings page where there 2 options available, delete and/or hide their content.

 

Edited August 2, 2023 by Miss_B

[Pseudonym]

On 8/2/2023 at 11:21 PM, Miss_B said:

It will display all members who have been flagged as spammers in the ModCP->Member Management area

Thanks Miss_B. That looks like a useful app for people who have already flagged their spammers, but it seems to work after the spammers have been flagged. In my case, that would mean going through the forums individually identifying hundreds of spammers - which would defeat the purpose of the bulk action your app offers. 🙂 

[Miss_B]

7 minutes ago, Pseudonym said:

Thanks Miss_B. That looks like a useful app for people who have already flagged their spammers, but it seems to work after the spammers have been flagged. In my case, that would mean going through the forums individually identifying hundreds of spammers - which would defeat the purpose of the bulk action your app offers. 🙂 

I was under the impression that they were marked as spammers from your posts above. I am sorry for the misunderstanding.

If they are not marked as spammers, on what criteria would you like to delete their content then?

 

[Pseudonym]

I've been working on a script that runs through a pre-identified list of members, and flags them as spammers. I'm delighted to say that after spending way too many hours on this (seriously, it probably would have been quicker to delete them manually!) I have something that works.

I have now cleaned up my forum. I've deleted 387,162 spam posts, banned ~700 spammers, and reduced my database size by 3.2 Gb. Best of all, this was all done using IPS functions, so it shouldn't cause any nasty database discrepancies. I'm feeling quite pleased with myself about all this. 😄

I'll post instructions and my script here, and hopefully it helps someone else in the future.

[Marc Stridgen]

Glad to hear you got yourself cleaned up there 🙂 

[Pseudonym]

Figured I'd post this script in a new topic, so people don't have to wade through this one to find it.

Get the Bulk Spammer Removal Tool here: