How to delete spam WooCommerce user accounts

    | |
    BySebastian Chedal

    Estimated reading time: 2 minutes, 52 seconds

    SPAM is inevitable on the internet. This is especially true if you have forms of any kind on your website. SPAM Bots are constantly trawling the internet looking for new places to junk up. Many site owners are familiar with ReCaptcha and other “prove you aren’t a robot” tests for contact forms, but often overlook their user registration forms. When you first set up WooCommerce, the option for “Allow customers to create an account on the “my account” page” is often enabled and forgotten. If you’ve silenced the wp notifications for “new user registered”, you’ll often end up with a bloated wp_users table before you know it, with tens of thousands of spam user registrations. This will slow down your database and your actions in the backend of the site.

    At Fountain City, we advise in general against allowing customers to create accounts without actually buying anything. When there are specific use cases for this, it’s critical that you put forth some sort of bot challenge. Google’s ReCaptcha v3 is a purely invisible process for the user and doesn’t diminish your UX with frustrating challenges.

    But what about if you haven’t followed best practices when first setting up your site? Let’s fix this!

    Note, the process below is technical and requires access to your database. If you don’t have a developer to help you, reach out to us. We love creating systems for greater site stability and would love to talk to you about how we could help.

    1) Backup Your Database

    You are going to be making a very large change on your website. This might possibly crash if your server has low resources, or has a long-process kill script activated like they do on WP Engine. There’s multiple ways to do this. If you’re mucking about in the database, you probably already know how to export it via phpMyAdmin. Do that. There’s also numerous plugins like BackupBuddy, WP Migrate DB, etc that can help with this. Or you could do it from the command line.

    2) Test this process on a staging site first

    Seriously. You don’t want to mess things up on a live WooCommerce site. If you don’t have one, you need to either hire a WP Developer like us to set that up for you, or get better hosting. Many WordPress managed hosts like WP Engine, Flywheel, MediaTemple, or Siteground have built in systems for doing this.

    3) Execute SQL Queries

    It requires some comfortability with a tool like phpMyAdmin, which most people have access to through their web hosting cPanels.

    Run a select first to ensure you’re not deleting anyone important;

    SELECT * from wp_users where wp_users.ID not in (
	SELECT meta_value FROM wp_postmeta WHERE meta_key = '_customer_user'
) AND wp_users.ID not in (
	select distinct(post_author) from wp_posts
)

    That selects users with NO ORDERS and NO POSTS

    If it looks good, change SELECT * to  DELETE.

    After doing that, clear up user meta with:

    delete from wp_usermeta where wp_usermeta.user_id not in (select ID from wp_users);

    NOTE: You need to change the wp_ prefixes in these SQL queries to whatever your DB uses.

    Another potential tool solution

    There’s a plugin that could potentially help, User Spam Remover. However, it hasn’t been updated in 3 years. It also has no capability to specify a User Role…so if you want to just target subscribers, which is where most spam registrations reside, then it’s not possible. It is kind of a shotgun approach, and you run the risk of removing some users in other groups who you really didn’t intent to remove.