XF 1.1 Forum taken over by spam, spam cleaner not working

oTradeMark

Member
I left my forum unmanaged for a long time and it's now been over run with spam and spam accounts. I had Xenutilities installed as an addon to prevent spammers from registering and for months I never had an issue. It's just been over the past month or so that it's been completely overrun.

I upgraded xenforo to 1.1.4 and xenutilities to 1.2.2 but the issue I'm having now is that when I run the spam cleaner nothing happens.

I open the spam cleaner, I click on clean up. It has the "in progress" symbol flashing in the top right corner and on the clean up section but it never goes to the confirmation screen. Eventually the "in progress" just stops and it never deletes anything or bans anyone.
 
The spammers have thousands of posts...

Yes I'm on shared hosting, bluehost.com

Assuming the limitation is my hosting, is there any way to still clean up my forum or erase the spam?

Also, what steps should I take to ensure that spammers don't register so easily and ruin my forum like this in the future?
 
I run that query from phpMyAdmin? I would be most interested in mass deleting all posts from a range of users.

Until I get things figured out, is there a way to prevent all users from posting anything until I clean the forum up?
 
Until I get things figured out, is there a way to prevent all users from posting anything until I clean the forum up?

stophumanspam will stop user from posting links, spaming profiles, etc...
They have to meet conditions (such as minimum likes, posts, registration time, post:like ratio) to be able to do this. In your case, you can just set these conditions really high..

You can use that from the free pack here, until you manage to delete the spam users:
http://xenforo.com/community/resources/tac-tenants-anti-spam-collection-anti-spam-free-version.1474/


Or... you could change the group permissions for everyone, including new users, so that they do not have permissions to post (or any permissions)
 
Question about this code:
Code:
UPDATE xf_post
SET message_state = 'deleted'
WHERE message_state = 'visible'
AND user_id BETWEEN 2 and 10;
 
INSERT INTO xf_deletion_log (content_type, content_id, delete_date, delete_user_id, delete_username, delete_reason)
SELECT 'post', post_id, UNIX_TIMESTAMP(), 1, 'admin', 'manually deleted via query'
FROM xf_post
WHERE message_state = 'deleted'
AND user_id BETWEEN 2 and 10
ON DUPLICATE KEY UPDATE
content_id = VALUES(content_id);
 
UPDATE xf_thread
SET discussion_state = 'deleted'
WHERE discussion_state = 'visible'
AND user_id BETWEEN 2 and 10;
 
INSERT INTO xf_deletion_log (content_type, content_id, delete_date, delete_user_id, delete_username, delete_reason)
SELECT 'thread', thread_id, UNIX_TIMESTAMP(), 1, 'admin', 'manually deleted via query'
FROM xf_thread
WHERE discussion_state = 'deleted'
AND user_id BETWEEN 2 and 10
ON DUPLICATE KEY UPDATE
content_id = VALUES(content_id);

Will this permanently delete the posts? I don't want them to just not be visible like the default spam cleaner does. I want them to be gone forever.

Or is there just a way to delete all of the posts on the forum without erasing the users that are registered? The forum is relatively new so I don't mind having a clean slate as long as the users don't get deleted. I think I had about 200 non-spam users registered before this happened.
 
Question about this code:
Code:
UPDATE xf_post
SET message_state = 'deleted'
WHERE message_state = 'visible'
AND user_id BETWEEN 2 and 10;
 
INSERT INTO xf_deletion_log (content_type, content_id, delete_date, delete_user_id, delete_username, delete_reason)
SELECT 'post', post_id, UNIX_TIMESTAMP(), 1, 'admin', 'manually deleted via query'
FROM xf_post
WHERE message_state = 'deleted'
AND user_id BETWEEN 2 and 10
ON DUPLICATE KEY UPDATE
content_id = VALUES(content_id);
 
UPDATE xf_thread
SET discussion_state = 'deleted'
WHERE discussion_state = 'visible'
AND user_id BETWEEN 2 and 10;
 
INSERT INTO xf_deletion_log (content_type, content_id, delete_date, delete_user_id, delete_username, delete_reason)
SELECT 'thread', thread_id, UNIX_TIMESTAMP(), 1, 'admin', 'manually deleted via query'
FROM xf_thread
WHERE discussion_state = 'deleted'
AND user_id BETWEEN 2 and 10
ON DUPLICATE KEY UPDATE
content_id = VALUES(content_id);

Will this permanently delete the posts? I don't want them to just not be visible like the default spam cleaner does. I want them to be gone forever.

Or is there just a way to delete all of the posts on the forum without erasing the users that are registered? The forum is relatively new so I don't mind having a clean slate as long as the users don't get deleted. I think I had about 200 non-spam users registered before this happened.

Those queries soft delete. They do not physically delete like you want.

I do not recommend trying to physically delete threads/posts with manual queries because you can end up with orphaned data in other joined tables, including orphaned attachments.

There is no utility to mass physically delete right now. You could just leave everything soft deleted until such a utility is added to the software.
 
Those queries soft delete. They do not physically delete like you want.

I do not recommend trying to physically delete threads/posts with manual queries because you can end up with orphaned data in other joined tables, including orphaned attachments.

There is no utility to mass physically delete right now. You could just leave everything soft deleted until such a utility is added to the software.
What about deleting/recreating the table entirely and then just importing the xf_user section back into the table? I apologize for my inexperience and lack of knowledge on this subject I'm just wondering what the easiest way is to get a clean slate on the forums. There are literally over 70,000 spam discussions on my site and I want them all gone.

Again, it's a relatively new forum so I'm not opposed to drastic solutions as long as I can get it back to feeling brand new. I wouldn't even be opposed to re-installing as long as I could just copy my template changes over.
 
If you are comfortable working with the database then you can do that. But you need to be careful that the necessary data remains intact. For example, users have 5 required tables:

http://xenforo.com/community/threads/script-to-create-a-new-user.21286/#post-270084

You need to consult with the datawriters to ensure you are accounting for all relevant data.

And this post contains queries which are said to empty the database to make way for running the same import again a second time without reinstalling. That might help in this situation:

http://xenforo.com/community/threads/import-question-second-import-over-old-one.16663/#post-311819

Again, I do not recommend this. It will be your job to ensure that you don't break anything while manually deleting records. If you aren't careful then you can create problems in your database that will surface later.

Consider reverting to a backup from before you were spammed.
 
If you are comfortable working with the database then you can do that. But you need to be careful that the necessary data remains intact. For example, users have 5 required tables:

http://xenforo.com/community/threads/script-to-create-a-new-user.21286/#post-270084

You need to consult with the datawriters to ensure you are accounting for all relevant data.

And this post contains queries which are said to empty the database to make way for running the same import again a second time without reinstalling. That might help in this situation:

http://xenforo.com/community/threads/import-question-second-import-over-old-one.16663/#post-311819

Again, I do not recommend this. It will be your job to ensure that you don't break anything while manually deleting records. If you aren't careful then you can create problems in your database that will surface later.

Consider reverting to a backup from before you were spammed.
I just restored the oldest backup I had which was from March 29 but this was still after spammers flooded the forum. I don't understand what changed on my site though. I didn't have any spam posts for months even when xenutilities showed registrations being blocked and then suddenly they just went rampant in the past 2 months. =(

Also, did the database get changed at all in the xenforo 1.1.4 update? Because I didn't update to 1.1.4 until today and want to know if I need to redo the update.
 
I noticed that the majority of the spam posts are in a subcategory/subforum of the General category. If I delete the sub-forum will it remove all of the posts within that subcategory?
 
Top Bottom