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

SQL query to ban SPAM created accounts by certain date

Discussion in 'XenForo Questions and Support' started by swatme, Aug 31, 2012.

  1. swatme

    swatme Well-Known Member

    Hi

    may i know the SQL qeuery to be run via phpmyadmin
    i want to ban all accounts dated august 19 and 20 2012

    i already deleted all spammers post.
    i only need to ban the accounts instead of deleting it
    so that if there is a legitimate member that was banned,

    banning the accounts on specific date prevents SLEEPER CELL SPAMMERS
    (registered members using the xrumer and will sleep and post on future dates)

    -banning spammers accounts means they wont be able to reuse the email account
    -added members list stats in your forum :D



    thanks
     
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Run these queries. The red is the range of registration dates you want to ban. The blue is the banning information for the log (user_id of banner, and the reason):

    Code:
    UPDATE xf_user
    SET is_banned = 1
    WHERE register_date BETWEEN UNIX_TIMESTAMP('2011-12-06 00:00:00') AND UNIX_TIMESTAMP('2011-12-08 00:00:00');
    
    INSERT INTO xf_user_ban (user_id, ban_user_id, ban_date, end_date, user_reason)
    	SELECT user_id, 1, UNIX_TIMESTAMP(), 0, 'manually banned via query'
    	FROM xf_user
    	WHERE is_banned = 1
    	AND register_date BETWEEN UNIX_TIMESTAMP('2011-12-06 00:00:00') AND UNIX_TIMESTAMP('2011-12-08 00:00:00')
    ON DUPLICATE KEY UPDATE
    	user_id = VALUES(user_id);
    
    Edit... one person reported that banned users still show on the "Users Awaiting Approval" page (if you were using account moderation). You can fix this by running another query (after the previous two):

    Code:
    UPDATE xf_user
    SET user_state = 'valid'
    WHERE user_state = 'moderated'
    AND is_banned = 1;
    
    Or alternatively, there is a way you can delete all users in the moderation queue (as opposed to ban):

    http://xenforo.com/community/resources/dealing-with-forum-spam.980/

     
    Abracadaniel and swatme like this.
  3. swatme

    swatme Well-Known Member

    very nice!

    this helps a lot.
    it bans all the users registered on the spam attack :D
    it bans 100+ sleeper spammers where it spams on a later date...

    thanks jake!
     
  4. OMGClay

    OMGClay Member

    Hi guys, can someone of you tell me how I actually run these queries? I have never done that before... Where do I paste that code and so on? Thank you very much.
     
  5. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Most web hosts preinstall phpmyadmin for you. Open phpmyadmin, select your database from the left column, and click the SQL tab. Paste the queries in there.
     
    OMGClay likes this.
  6. OMGClay

    OMGClay Member

    Thank you very much!
     
  7. digital150

    digital150 Member

    Could you do the same, but for deleting spam users?? And what would be the code for that ?
     
  8. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Hard deletes can't be done with simple queries. There are many joined tables involved in a hard delete, as opposed to a ban which is just a status change.

    Banning should be enough. XenForo effectively removes banned users from the forum by hiding them.
     

Share This Page