News:

Want to get involved in developing SMF, then why not lend a hand on our github!

Main Menu

Deleting spam accounts

Started by Goober5000, January 02, 2011, 05:18:11 PM

Previous topic - Next topic

Goober5000

In the course of several years administrating a SMF forum, we've had more and more troubles with spambots and people who register accounts just to post ads.  It got so bad a few weeks ago that we upgraded to SMF 2 RC4, so that we could use SMF 2's new permissions system for better spam protection.  I'm pleased to say that things have been much better since then.

So now I decided to find out how many spam accounts we actually missed.  By running queries on the underlying database, I found over 1200 accounts with no posts but with a signature or website link.  And from a cursory glance, 99.999% of them look to be spam accounts.  Naturally I want to delete them, but SMF doesn't have a batch-delete feature that will do this.  So I thought I'd develop some database queries to do this, running them past you all first to make sure they're correct.

Here is the query for finding members who have a signature, website title, or website link, but no posts:
SELECT * FROM `smf_members` WHERE `id_member` NOT IN (SELECT DISTINCT `id_member` FROM `smf_messages`) AND `id_member` NOT IN (SELECT DISTINCT `id_member_from` FROM `smf_personal_messages`) AND (`signature`!='' OR `website_title`!='' OR `website_url`!='')

Using this clause, I created several delete statements, adapted from deleteMember in Subs-Members.php:
DELETE FROM `smf_log_actions` WHERE `id_member` IN (SELECT `id_member` FROM `smf_members` WHERE `id_member` NOT IN (SELECT DISTINCT `id_member` FROM `smf_messages`) AND `id_member` NOT IN (SELECT DISTINCT `id_member_from` FROM `smf_personal_messages`) AND (`signature`!='' OR `website_title`!='' OR `website_url`!=''))

DELETE FROM `smf_log_boards` WHERE `id_member` IN (SELECT `id_member` FROM `smf_members` WHERE `id_member` NOT IN (SELECT DISTINCT `id_member` FROM `smf_messages`) AND `id_member` NOT IN (SELECT DISTINCT `id_member_from` FROM `smf_personal_messages`) AND (`signature`!='' OR `website_title`!='' OR `website_url`!=''))

DELETE FROM `smf_log_comments` WHERE `id_member` IN (SELECT `id_member` FROM `smf_members` WHERE `id_member` NOT IN (SELECT DISTINCT `id_member` FROM `smf_messages`) AND `id_recipient` NOT IN (SELECT DISTINCT `id_member_from` FROM `smf_personal_messages`) AND (`signature`!='' OR `website_title`!='' OR `website_url`!=''))

DELETE FROM `smf_log_group_requests` WHERE `id_member` IN (SELECT `id_member` FROM `smf_members` WHERE `id_member` NOT IN (SELECT DISTINCT `id_member` FROM `smf_messages`) AND `id_member` NOT IN (SELECT DISTINCT `id_member_from` FROM `smf_personal_messages`) AND (`signature`!='' OR `website_title`!='' OR `website_url`!=''))

DELETE FROM `smf_log_karma` WHERE `id_target` IN (SELECT `id_member` FROM `smf_members` WHERE `id_member` NOT IN (SELECT DISTINCT `id_member` FROM `smf_messages`) AND `id_member` NOT IN (SELECT DISTINCT `id_member_from` FROM `smf_personal_messages`) AND (`signature`!='' OR `website_title`!='' OR `website_url`!=''))

DELETE FROM `smf_log_karma` WHERE `id_executor` IN (SELECT `id_member` FROM `smf_members` WHERE `id_member` NOT IN (SELECT DISTINCT `id_member` FROM `smf_messages`) AND `id_member` NOT IN (SELECT DISTINCT `id_member_from` FROM `smf_personal_messages`) AND (`signature`!='' OR `website_title`!='' OR `website_url`!=''))

DELETE FROM `smf_log_mark_read` WHERE `id_member` IN (SELECT `id_member` FROM `smf_members` WHERE `id_member` NOT IN (SELECT DISTINCT `id_member` FROM `smf_messages`) AND `id_member` NOT IN (SELECT DISTINCT `id_member_from` FROM `smf_personal_messages`) AND (`signature`!='' OR `website_title`!='' OR `website_url`!=''))

DELETE FROM `smf_log_notify` WHERE `id_member` IN (SELECT `id_member` FROM `smf_members` WHERE `id_member` NOT IN (SELECT DISTINCT `id_member` FROM `smf_messages`) AND `id_member` NOT IN (SELECT DISTINCT `id_member_from` FROM `smf_personal_messages`) AND (`signature`!='' OR `website_title`!='' OR `website_url`!=''))

DELETE FROM `smf_log_online` WHERE `id_member` IN (SELECT `id_member` FROM `smf_members` WHERE `id_member` NOT IN (SELECT DISTINCT `id_member` FROM `smf_messages`) AND `id_member` NOT IN (SELECT DISTINCT `id_member_from` FROM `smf_personal_messages`) AND (`signature`!='' OR `website_title`!='' OR `website_url`!=''))

DELETE FROM `smf_log_subscribed` WHERE `id_member` IN (SELECT `id_member` FROM `smf_members` WHERE `id_member` NOT IN (SELECT DISTINCT `id_member` FROM `smf_messages`) AND `id_member` NOT IN (SELECT DISTINCT `id_member_from` FROM `smf_personal_messages`) AND (`signature`!='' OR `website_title`!='' OR `website_url`!=''))

DELETE FROM `smf_log_topics` WHERE `id_member` IN (SELECT `id_member` FROM `smf_members` WHERE `id_member` NOT IN (SELECT DISTINCT `id_member` FROM `smf_messages`) AND `id_member` NOT IN (SELECT DISTINCT `id_member_from` FROM `smf_personal_messages`) AND (`signature`!='' OR `website_title`!='' OR `website_url`!=''))

DELETE FROM `smf_collapsed_categories` WHERE `id_member` IN (SELECT `id_member` FROM `smf_members` WHERE `id_member` NOT IN (SELECT DISTINCT `id_member` FROM `smf_messages`) AND `id_member` NOT IN (SELECT DISTINCT `id_member_from` FROM `smf_personal_messages`) AND (`signature`!='' OR `website_title`!='' OR `website_url`!=''))

DELETE FROM `smf_pm_recipients` WHERE `id_member` IN (SELECT `id_member` FROM `smf_members` WHERE `id_member` NOT IN (SELECT DISTINCT `id_member` FROM `smf_messages`) AND `id_member` NOT IN (SELECT DISTINCT `id_member_from` FROM `smf_personal_messages`) AND (`signature`!='' OR `website_title`!='' OR `website_url`!=''))

DELETE FROM `smf_ban_items` WHERE `id_member` IN (SELECT `id_member` FROM `smf_members` WHERE `id_member` NOT IN (SELECT DISTINCT `id_member` FROM `smf_messages`) AND `id_member` NOT IN (SELECT DISTINCT `id_member_from` FROM `smf_personal_messages`) AND (`signature`!='' OR `website_title`!='' OR `website_url`!=''))

DELETE FROM `smf_themes` WHERE `id_member` IN (SELECT `id_member` FROM `smf_members` WHERE `id_member` NOT IN (SELECT DISTINCT `id_member` FROM `smf_messages`) AND `id_member` NOT IN (SELECT DISTINCT `id_member_from` FROM `smf_personal_messages`) AND (`signature`!='' OR `website_title`!='' OR `website_url`!=''))


Finally, delete the member itself:
DELETE FROM `smf_members` WHERE `id_member` NOT IN (SELECT DISTINCT `id_member` FROM `smf_messages`) AND `id_member` NOT IN (SELECT DISTINCT `id_member_from` FROM `smf_personal_messages`) AND (`signature`!='' OR `website_title`!='' OR `website_url`!='')

I'm hoping I got all that right. :)  Does anybody notice any problems?

May I also make a request for somebody to add this as a SMF feature, so that the deletion can go through the actual deleteMember function rather than tricky database manipulation?

Arantor

Better still, just call deleteMembers with an array of user ids, e.g. deleteMembers(array(100, 101, 102, 103), false); which will do the job, especially if there are well-behaved mods that update deleteMembers with new information, such as my drafts mod that also ensured drafts of deleted members were purged.

Can't really see bulk-user-delete being a core feature though, other than what's already present in Admin > Maintenance > Members

Mari-chi

Oh wow, this looks uber useful. :3 My forum is new (not even opened yet), but we've had several spam accounts being registered. I just removed the ability to have links in signatures, and have been manually removing spam posts... Now if I could only figure it out. XD

Goober5000

Quote from: IncognitoMuse on January 02, 2011, 05:24:19 PM
Better still, just call deleteMembers with an array of user ids, e.g. deleteMembers(array(100, 101, 102, 103), false); which will do the job
This is basically what I'm looking for, but with the option to generate the array dynamically (i.e. subject to a set of control-panel-specified conditions).  Right now it's possible to delete multiple members by selecting checkboxes in the member list, but that isn't satisfactory for two reasons.  One, you can't view signatures or website URLs from that list.  And two, I mentioned that I found over 1200 spam accounts -- I don't feel like hitting a checkbox 1200 times. :P

Hmm.  I wonder if I could use the SQL statement to generate the list of 1200 user IDs, then create a temporary PHP function which would feed those 1200 IDs into deleteMembers.

Goober5000


Arantor

I didn't have anything to add since you seemed to have it figured out, generate a list in SQL of member ids, push that into an array and call deleteMembers, job done.


Goober5000

Okay, users deleted. :)  Here's the function I came up with that does the dirty work:

function do_deletion_stuff()
{
   global $user_info, $sourcedir;

   // ========== HERE BE DRAGONS ==========

   require_once($sourcedir . '/Subs-Members.php');

   $numbers_to_delete = array(blah, blah, blah, ..., blah);

   $text_output = '';
   $array_batch_size = 100;

   $array_index = 0;
   while ($array_index < count($numbers_to_delete))
   {
      $numbers_to_delete_batch = array_slice($numbers_to_delete, $array_index, $array_batch_size, true);

      deleteMembers($numbers_to_delete_batch);
      $text_output = $text_output . implode(',', $numbers_to_delete_batch) . '<br>';

      $array_index += $array_batch_size;
   }

   return $text_output;
}


This function will delete all the member IDs in the $numbers_to_delete array, in batches of 100, by calling the deleteMembers function.  It will also return these numbers in text form, though this output can be ignored if desired.  I created the $numbers_to_delete array by running an SQL query on the members (the "SELECT FROM" statement as described in my first post), and exporting the resulting IDs into a one-dimensional PHP array.

WARNING: THIS FUNCTION IS DANGEROUS.  Calling it will immediately delete all members whose IDs are in the $numbers_to_delete array.  I take no responsibility if you accidentally delete a member you shouldn't have. :P

Arantor

Just note that anyone using this must be using PHP 5.0.2 or higher (there are still users on 4.4 or lower)

jobarjo

Hi

I have 3579 spam accounts after running your sql query. I have just upgraded to smf 2.
Now would like to run your script but I'm a newbie in PHP.
Where should I paste this script?
Could someone adapt it as a single file to run, and so it makes the mysql query also?

Thanks.

sdixon

Thanks

Just ran your scripts and removed over 400 spam accounts.

Advertisement: