1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'XenForo Questions and Support' started by Stuart Wright, Aug 26, 2012.

  1. Stuart Wright

    Stuart Wright Well-Known Member

    There are thousands.
     
    Digital Doctor and Luis like this.
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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
     
  3. Stuart Wright

    Stuart Wright Well-Known Member

    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 !
     
  4. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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.
     
  5. Stuart Wright

    Stuart Wright Well-Known Member

    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.
     
  6. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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:

    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);
    
     
    Brandon Sheley likes this.
  7. cmeinck

    cmeinck Well-Known Member

    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).
     
  8. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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
     
  9. cmeinck

    cmeinck Well-Known Member

    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?
     
  10. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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.
     
    Digital Doctor likes this.
  11. sonnb

    sonnb Well-Known Member

    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 and Jake Bunce like this.
  12. Digital Doctor

    Digital Doctor Well-Known Member

    Ouch.
     
  13. gotski

    gotski Member

    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!
     
  14. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

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

    Digital Doctor Well-Known Member

    Exactly the information I wanted !
     
  16. gotski

    gotski Member

    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.
     

Share This Page