[wp-hackers] Clearing out bot users

[Kevin Newman]

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