XF 1.1 Hit by spammers. How do I permanently delete all posts by a user?

[Stuart Wright]

There are thousands.

 

[Jake Bunce]

There isn't a good way to mass physically delete, but you can mass soft delete. The first thing you should try is the spam cleaner. The spam cleaner option is available to mods for "new" users based on this criteria:

Admin CP -> Home -> Options -> Spam Management -> Spam Cleaner User Criteria

If the user has thousands of posts then you probably need to relax this criteria so the spam cleaner is available for that user.

If not the spam cleaner then you can run manual queries to soft delete all posts by a user. See this post:

http://xenforo.com/community/thread...orum-now-it-doesnt-work-ok.33337/#post-380918

 

[Stuart Wright]

Thanks Jake but there were hundreds of spammers which needed all their posts deleting.
I haven't used Xenforo admin CP in anger until the last couple of days. There is *so* much functionality missing which I take for granted in vB3.
In the admin CP, you can't even see the date people registered !

 

[Jake Bunce]

Thanks Jake but there were hundreds of spammers which needed all their posts deleting.
I haven't used Xenforo admin CP in anger until the last couple of days. There is *so* much functionality missing which I take for granted in vB3.
In the admin CP, you can't even see the date people registered !

Is there a common criteria we can use to match all of the spammers? If you can come up with a common criteria that can be put into a query then we can make it work. Maybe a common email domain or range of user_ids.

 

[Stuart Wright]

Doesn't work like that, Jake. It's no good isolating by IP, email address etc. A range of userids would do it.
If I were going to continue with XF, I'd work out with Kier and/or Mike which functionality they were intending to write soon, and if something I needed weren't coming soon enough, I'd pay for it.
But... well you know.

 

[Jake Bunce]

A range of userids would do it.

Range of user_ids it is.

Try these queries. The red is the range of user_ids whose posts and threads you want to soft delete (the range is inclusive of the boundaries). The blue is the deletion info (user_id and username of the user who is doing the deleting, as well as a stated reason for the log). Backup first:

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);

 

[cmeinck]

Range of user_ids it is.

Try these queries. The red is the range of user_ids whose posts and threads you want to soft delete (the range is inclusive of the boundaries). The blue is the deletion info (user_id and username of the user who is doing the deleting, as well as a stated reason for the log). Backup first:

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);

Seeing over 20,000 spam posts across a few forums, I tried this with great hopes. For some reason, it's not working for me. It says MySQL returned an empty result set (i.e. zero rows).

 

[Jake Bunce]

Seeing over 20,000 spam posts across a few forums, I tried this with great hopes. For some reason, it's not working for me. It says MySQL returned an empty result set (i.e. zero rows).

Works for me. Tested it myself, and again just now. Make sure your range of user_ids is correct.

I would also note that you need to rebuild your thread and forum information after this:

Admin CP -> Tools -> Rebuild Caches

 

[cmeinck]

Works for me. Tested it myself, and again just now. Make sure your range of user_ids is correct.

I would also note that you need to rebuild your thread and forum information after this:

Admin CP -> Tools -> Rebuild Caches

Got it. This query was a lifesaver. Still spent most of my day after what was the worst spam attack I've seen on my forums. Some spammers had over 6,000 posts.

Next question would be, is there a query to hard delete all soft deleted threads?

 

[Jake Bunce]

Next question would be, is there a query to hard delete all soft deleted threads?

Unfortunately not. Hard deleting is not really possible with simple queries because it involves many joined tables. Hard deletions need to be done using inline moderation, and as of right now that is a one-by-one checkbox kind of proposition.

 

[sonnb]

Got it. This query was a lifesaver. Still spent most of my day after what was the worst spam attack I've seen on my forums. Some spammers had over 6,000 posts.

Next question would be, is there a query to hard delete all soft deleted threads?

You could do this by using this addon http://xenforo.com/community/resources/sonnb-messages-mass-actions.824/ with carefully attention (Backup before perform deletion).

 

[Digital Doctor]

Unfortunately not. Hard deleting is not really possible with simple queries because it involves many joined tables. Hard deletions need to be done using inline moderation, and as of right now that is a one-by-one checkbox kind of proposition.

Ouch.

 

[gotski]

Hello, please help me, I use this code in the php script, and received error message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO xf_deletion_log (content_type, content_id, delete_date, delete_user_' at line 6

what there can be a problem? Sorry for my english. thanks!

 

[Jake Bunce]

Hello, please help me, I use this code in the php script, and received error message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO xf_deletion_log (content_type, content_id, delete_date, delete_user_' at line 6

what there can be a problem? Sorry for my english. thanks!

Apparently you have a syntax error in your queries. Please post the full queries you are using.

 

[Digital Doctor]

In the admin CP, you can't even see the date people registered !

Exactly the information I wanted !

 

[gotski]

Apparently you have a syntax error in your queries. Please post the full queries you are using.

Thx, I used shell, and this work.... Don't have why it don't work in php

Big thanks Jake for your work! It very great helped for my problem with spam.