SQL query to ban SPAM created accounts by certain date

[swatme]

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



thanks

 

[Jake Bunce]

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

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

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/

4) This person wanted to mass delete all users in the moderation queue, but there is no option to mass select the delete option on that page. I posted a template edit here to change the default selections:

http://xenforo.com/community/threads/how-to-mass-delete-users-awaiting-approval.35568/#post-404156

This is useful if you want to delete all users in the moderation queue and there are a lot of them.

 

[swatme]

very nice!

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

thanks jake!

 

[OMGClay]

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.

 

[Jake Bunce]

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.

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]

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.

Thank you very much!

 

[digital150]

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

 

[Jake Bunce]

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

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.