How to purge fake/bot WooCommerce customer accounts directly in the MySQL database

DANGER! If you don’t know the havoc one can wreak with a DELETE statement in MySQL, stop right here. I take no responsibility for what you might do with the information that follows.


Bots like to create fake customer accounts on WooCommerce (WordPress) sites, apparently. What they’re attempting to do, I don’t know. But if you don’t stay on top of things, you might find you have thousands of fake customer accounts in your site. Chances are they haven’t, won’t, and can’t actually cause any damage, but they’re cluttering things up, and any unnecessary user account in a WordPress database represents a potential future security risk.

On a particular client’s heavy-traffic WooCommerce site, I discovered that over the course of the site’s 7-year lifespan it had accumulated nearly 8,000 such accounts, and I wanted to be rid of them.

After carefully exploring the data surrounding a few of these obviously fake accounts, I determined a pattern, and came up with a fairly cautious set of conditions that, to me, indicated a customer was fake:

  1. They had the customer role.
  2. Their user account had nothing in the First Name or Last Name fields.
  3. Likewise, their user account had nothing in the Billing First Name or Billing Last Name fields. (If you’re feeling extra draconian, you might skip this one.)
  4. They had never placed an order while logged in — their user ID did not have an _order_count entry in the wp_usermeta table. Which is perhaps an obvious condition because…
  5. They had never logged in at all — their user ID did not have a wfls-last-login entry in the wp_usermeta table. This condition will only apply if your site uses WordFence.

You can test all of those conditions with a single SQL query:

SELECT DISTINCT `user_id`
FROM `wp_usermeta`
WHERE
`user_id` IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'wp_capabilities' AND `meta_value` = 'a:1:{s:8:"customer";b:1;}') AND
`user_id` IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'first_name' AND `meta_value` = '') AND
`user_id` IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'last_name' AND `meta_value` = '') AND
`user_id` NOT IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'billing_first_name') AND
`user_id` NOT IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'billing_last_name') AND
`user_id` NOT IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = '_order_count' AND meta_value > 0) AND
`user_id` NOT IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'wfls-last-login');

You may want to spot check some of these IDs in the wp_users table, or directly in the site admin, just to be sure everything looks right. Then you can turn the above into a subquery that will delete all of the matching users. Be sure to make a full backup of your database before doing this!

DELETE FROM `wp_users` WHERE `ID` IN (
SELECT DISTINCT `user_id`
FROM `wp_usermeta`
WHERE
`user_id` IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'wp_capabilities' AND `meta_value` = 'a:1:{s:8:"customer";b:1;}') AND
`user_id` IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'first_name' AND `meta_value` = '') AND
`user_id` IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'last_name' AND `meta_value` = '') AND
`user_id` NOT IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'billing_first_name') AND
`user_id` NOT IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'billing_last_name') AND
`user_id` NOT IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = '_order_count' AND meta_value > 0) AND
`user_id` NOT IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'wfls-last-login')
);

OK… so the users are gone. But each one has a bunch of records in the wp_usermeta table. Now that we’ve gotten rid of the users themselves, it’s easy to purge their associated meta data:

DELETE FROM `wp_usermeta` WHERE `user_id` NOT IN (SELECT `ID` FROM `wp_users`);

How to make the WooCommerce main shop page show featured products only in 2022

Sorry for the click-bait-y title (especially the “in 2022” part), but I searched fruitlessly for way too long and found too many woefully outdated answers to this. Ultimately what I found still wasn’t quite the complete answer, so I modified it a bit myself to arrive at the following.

The goal here is, as the title suggests, to get the WooCommerce main shop page to only show your featured products. Why this isn’t just a checkbox option in WooCommerce is beyond me. But then a lot of the decisions made by the WooCommerce dev team are beyond me. (Excuse me, Professor Brainiac, but I’ve built e-commerce platforms from scratch and, uh, I think I know how a proton accelerator… oh wait, never mind.)

Anyway, this is it:

add_action('woocommerce_product_query', function($query) {
    if (is_shop()) {
        $query->set('tax_query', array(array(
            'taxonomy' => 'product_visibility',
            'field' => 'name',
            'terms' => 'featured',
            'operator' => 'IN',
        )));
    }
});

What exactly is happening here? Well, as noted by the most helpful resource I found, since WooCommerce 3 (currently on 6.1.1), the “featured” status has been handled by the product_visibility taxonomy, and not by the _featured post meta field. So this needs a tax_query and not a meta_query.

Beyond that, we’re making an extra check that we’re on the shop main page — so this doesn’t affect category archive pages. And we’re using the woocommerce_product_query hook, not pre_get_posts as some other examples suggest, so it only runs on WooCommerce queries and we can skip adding extra conditionals for pre_get_posts to run on, you know, every single post query on every page of the site, including admin.

That’s all there is to it. Now your main WooCommerce shop page will only display featured products, and nothing else changes.

Add arbitrary product data to order items in WooCommerce

This seems to be way more convoluted than it needs to be, but I’m not sure how much of that is that it’s actually convoluted, how much is that Woo’s documentation sucks, and how much is that everyone else’s tutorial on it is tl;dr.

Anyway… I just wanted to do something fairly simple. I want to have each product’s short description get sent into the order data. This is a specific use case with a client who’s syncing data over the REST API with an external system, and we’re shoehorning data into the short description that maybe could go somewhere else. The point is, use your imagination as to how this might be useful to you.

I’m stripping out a lot of the other details. All I want is a way to a) add the data to the item in the cart, and b) carry that data over into the order item meta data in the database. You may need or want more, but this will get you started.

// Add custom order item meta data to cart
add_filter('woocommerce_add_cart_item_data', function($cart_item, $product_id) {
  if (!isset($cart_item['short_description'])) {
    if ($product = wc_get_product($product_id)) {
      $cart_item['short_description'] = $product->get_short_description();
    }
  }
  return $cart_item;
}, 10, 2);

// Add custom order item data from the cart into the order
add_action('woocommerce_checkout_create_order_line_item', function($item, $cart_item_key, $values, $order) {
  if  (isset($values['short_description'])) {
    $item->add_meta_data('Short Description', $values['short_description'], true);
  }
}, 10, 4);

This is a major distillation of stuff I found in these two tutorials: How to Add a Customizable Field to a WooCommerce Product and Add Custom Cart Item Data in WooCommerce.

I spent 5 hours troubleshooting this WordPress problem so you don’t have to (starring: WooCommerce Action Scheduler)

Sorry for that “clickbaity” headline. I added the parenthetical so it might be at least marginally useful. Since my WordPress-related posts are always about how I solved a particularly weird or obscure WP issue, I usually consider their titles carefully. “What would I have googled to find a solution to this problem?” But honestly, I spent 5 hours on this yesterday partly because I wasn’t sure what to google. (And I use lowercase “google” as a generic for “conduct an Internet search”; I normally use DuckDuckGo.)

OK, so here’s the situation. This particular site is — among my normally very-low-traffic clients — one of the busiest I work on. It’s a WooCommerce site with hundreds of products and 20+ daily orders. (Yeah, 20+ orders a day is not huge, but on the scale I normally deal with, it’s a lot.)

This site runs on its own virtual private server, with 8 GB RAM and 4 vCPUs. Pretty substantial for a single site. And yet, for weeks it has been maxing out RAM and CPU resources. Not to the point where the site was in crisis mode that demanded my immediate attention, but it was frustratingly slow. Just slightly below the threshold of me dropping work on other new projects to try to fix this. (At this point I feel obliged to note that I did not actually build or maintain this site for its first couple of years of existence, so I don’t know its inner workings as well as I normally would. I just know it’s running way too many plugins and desperately needs some TLC I have not had time to give it.)

Yesterday things finally got to the breaking point. For me, at least. The client had contacted me about an unrelated issue, but as I was dealing with that, I got frustrated by seeing all of this inexplicable resource usage, so I had to address it.

As it happens, this post is actually a bit of a sequel to my last post, about getting Apache’s mod_status and mod_rewrite to play nicely on a WordPress site. About three weeks ago I finally got mod_status working on this site, and had planned to come back, when I had a chance, to investigate this issue.

If you are not familiar with mod_status, you should check it out. Apache is generally a bit of a “black box” but this lets you see exactly what’s happening with each thread — the requested URL, the requesting IP address, connection time, resource usage, etc.

I noticed an absurd number of threads were coming from the localhost and were requesting wp-admin/admin-ajax.php with a query string referencing WooCommerce’s Action Scheduler. But what to do with that information?

I’ll admit, this is where I wasted a bunch of time in fruitless searches, because I don’t know a lot about Action Scheduler. I read a few threads on the WordPress support forums and StackOverflow that kind of danced around the problem I was having but never really got at it.

Eventually I ended up in phpMyAdmin, scrutinizing the wp_actionscheduler_actions table, and trying to figure out where all of the wc_facebook_regenerate_feed actions were coming from. I used my old favorite Search-Replace-DB to try to find any instances of “facebook” in the database. (This was an utter failure, for reasons I can’t explain. But that failure was critical to why this took me so long to resolve.)

I went to Tools > Scheduled Actions in WP admin and discovered there were over 200,000 actions, although there were only about 70,000 (only!) showing up in wp_actionscheduler_actions. Mystery!

I went to wp_actionscheduler_actions again, saw that those wc_facebook_regenerate_feed actions had all been scheduled weeks ago, and decided to just chuck out the lot. I truncated the table, but within seconds it started filling up again with hundreds of wc_facebook_regenerate_feed actions, with the same weeks-old scheduled dates. Where were they coming from???

What was especially maddening to me about all of this was that I had already, weeks ago, determined that the plugin that had created these — Facebook for WooCommerce — had been causing some kind of trouble, and I had deactivated it. Yesterday I even went so far as to delete it. I scoured the theme code for references to Facebook. I looked in the file system for stray files that might be responsible. And as I mentioned before, I tried to search the database for any references to Facebook. I was getting nowhere.

Eventually I realized Search-Replace-DB was having problems, so I dove into phpMyAdmin directly and started searching individual fields, in individual tables, for “Facebook”. And that’s where I finally figured it out.

WordPress puts everything in wp_posts, and that’s a problem.

I’ve complained over the years about the database architecture in WordPress. Having built multiple custom CMSes in the years prior to when I finally, fully embraced WordPress in 2014, I have a fair bit of experience designing databases. And two things I learned in that experience were: 1) clearly define what your data tables are for, and 2) indexes make databases efficient. WordPress is awesome for many things, but it has far, far outgrown its original conception as blogging software. Custom Post Types and Custom Fields make it super-flexible, but shoving everything they create into wp_posts and wp_postmeta can create a disastrous situation.

Case in point, WooCommerce scheduled actions. In earlier iterations, those were custom posts! (As are, still, WooCommerce orders, which is totally f***ed up, if you ask me.) At some point Woo or Automattic realized scheduled actions don’t belong in wp_posts, so they created four new tables specifically for managing them. Plugins that use scheduled actions had to create new scheduled actions for migrating the old wp_posts scheduled actions into the new tables.

And that’s where I found myself. Through some curious set of circumstances with this particular site, which probably at some point included someone other than me disabling WP-Cron to try to fix some other problem, 200,000+ scheduled actions from the Facebook for WooCommerce plugin (in the wp_posts table) got queued up for migration to the new tables. And as quickly as I was deleting them from the new tables, Action Scheduler (which runs once a minute!!!) was dutifully refilling them.

(And obviously they were never actually running… perhaps because I had deactivated the plugin? Or because they were simply timing out? Who knows? But here’s something I see as a flaw with Action Scheduler: it should check to see if the plugin that scheduled an action is currently active, and if not, purge the action immediately.)

At last here was the fix. I had to run this SQL query in phpMyAdmin. (Proceed with caution! Don’t just use this code… look in your database for exactly what is causing problems and adjust accordingly.)

DELETE FROM `wp_posts` WHERE `post_title` = 'wc_facebook_regenerate_feed';

Note: I’m doing this from memory — and a glance back at my browser history from yesterday. I didn’t keep notes on exactly what the title was.

For a more generalized — and drastic — approach, you could also do this:

DELETE FROM `wp_posts` WHERE `post_type` = 'scheduled-action';

I’ll just conclude here with a nice little graph of the site’s CPU and RAM usage over the past 24 hours. It was 6 PM when I finally figured this out!

Fixing a redirect loop on WordPress sites with WooCommerce when converting the site to all-SSL

Best practice these days is to run sites on all-SSL, not just the parts of the site that “need” it. But not long ago, it was common to apply SSL only when it was absolutely necessary, because SSL encryption meant a performance hit. Not anymore.

You may find, if you’re trying to convert an existing WordPress / WooCommerce site to all-SSL, that reconfiguring your URLs, by using a tool such as interconnect/it’s super-slick, powerful (and dangerous) Search Replace DB tool, that once you’ve made the changes, your home page kicks into a redirect loop, indefinitely cycling between http:// and https:// versions of your URL.

WooCommerce may be to blame!

Specifically, a setting in WooCommerce called “Force HTTP when leaving the checkout”. Head on over to WooCommerce > Settings > Checkout and… um… check it out.

Simply uncheck that box, and, while you’re at it, uncheck “Force secure checkout” since it’s unnecessary on an all-SSL site, save your changes, and your home page should come back to live!