[wp-hackers] Clearing out bot users

[Kevin Newman]

I'd thought of using a simple php loop and the WP functions, but it was 
more fun to try and come up with a single SQL query that could do it. :-)

Kevin N.


On 2/15/11 7:14 PM, Ryan Bilesky wrote:
> well you could use that query to select the user id's then use two delete
> queries one for user table based off the id, one for user meta based off of
> user id.  So exactly simple single query but it works.  Also if you intend
> to use this in a plugin on not directly on the database you could use
> wp_delete_user() which deletes the user and user meta for the user id, you
> pass.
>
> On Tue, Feb 15, 2011 at 2:56 PM, Kevin Newman<CaptainN at unfocus.com>  wrote:
>
>> Converting that to a single Query that can delete all usermeta rows seems
>> difficult for my shallow understanding of SQL.
>>
>> I'll spend a few more than 5 minutes on that another time. :-)
>>
>> Kevin N.
>>
>>
>>
>> On 2/15/11 5:06 PM, Otto wrote:
>>
>>> Make sure you delete their usermeta rows too.
>>>
>>> Also, *BACKUP FIRST*. Obviously. :)
>>>
>>> -Otto
>>>
>>>
>>>
>>> On Tue, Feb 15, 2011 at 3:55 PM, Kevin Newman<CaptainN at unfocus.com>
>>>   wrote:
>>>
>>>> Hey,
>>>>
>>>> I'd like to clear out all those thousands of bot users. I came up with
>>>> this
>>>> SQL statement to list all the spam users:
>>>>
>>>> SELECT * FROM wp_users AS u
>>>> JOIN wp_usermeta AS mv ON mv.user_id = u.ID
>>>> WHERE mv.meta_key = 'wp_capabilities' AND mv.meta_value LIKE
>>>> '%subscriber%'
>>>> AND ( SELECT count(*) FROM wp_comments AS c WHERE c.user_id = u.ID )<= 0
>>>> AND ( SELECT count(*) FROM wp_posts AS p WHERE p.post_author = u.ID )<= 0
>>>>
>>>> This could be converted into a delete statement to nuke any user that has
>>>> no
>>>> posts or comments and is a subscriber.
>>>>
>>>> Did I miss anything?
>>>>
>>>> Thanks,
>>>>
>>>> Kevin N.
>>>>
>>>>
>> _______________________________________________
>> wp-hackers mailing list
>> wp-hackers at lists.automattic.com
>> http://lists.automattic.com/mailman/listinfo/wp-hackers
>>
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers