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`);

The things I make don’t exist

Like most “creatives,” I have an impulse — maybe a compulsion — to make things. I’ve often reflected on the nature of this impulse, wondering why I simply feel in my bones that I need to do this.

I think in some ways it’s about mortality. Our existence is temporary and fleeting. We want to leave a mark on the world. Something that says “I was here.” The impulse to leave a mark often takes a dark turn, but even when it doesn’t, the fragility of our modern world calls into question the extreme vanity of thinking that somehow your existence matters enough that you need to construct monuments to yourself.

But in my case, there’s an even weirder element to the impulse. Nothing I make exists. That is, it doesn’t exist as physical objects. The closest I come, I guess, is if I print out a piece of sheet music I’ve composed. But unlike a painter, a sculptor, a knitter, a builder… the things I make are even more fleeting and ethereal than my own existence.

Sound waves in the air. Momentary flashes of pixels on a screen. The flow of electrons inside computer chips. None of it is real outside of the moments someone experiences it. And worse, most of it is dependent upon the right hardware and software continuing to operate to manifest those moments again in the future. And all of it depends on electricity.

Intermittently through the years I have run an online shop where I sell merchandise featuring my designs, all created on a computer. I just launched a new one. But I don’t make the products. I certainly am not stitching together the t-shirts or firing the coffee mugs in a kiln or… doing whatever it takes to physically create a sticker. I’m not buying the blank merchandise and printing my designs on it. I’m not even warehousing stock. Everything I sell is print-on-demand, and the “photos” of the merchandise on the site are just composites created by the fulfillment vendor’s software.

That gave me the idea for this t-shirt. It doesn’t exist. And as long as no one ever orders one, it will stay that way.

I don’t have a good desk chair

I don’t have a good desk chair.

I can’t sit in my not-good desk chair for very long. It’s not really a desk chair, even, other than that it’s a chair, and it’s at my desk.

It’s just an $80 IKEA side chair, “walnut,” which I feel probably needs to be in quotes since it’s from IKEA. At least it is solid wood, not a hardened slurry of glue and sawdust covered in a “veneer” that’s actually just a paper sticker with wood grain printed on it. Which is a factual description of some IKEA furniture (that does exist in the house of a couple who are on the verge of 50). This chair is undoubtedly real wood.

But a wooden chair is not a good desk chair.

It is not a good desk chair even when it has a memory foam cushion on it. Not even when that memory foam cushion is on top of a silicone honeycomb cushion, although now we are getting somewhere. (But where is that? One of the Amazon reviews of the cushions praised its effectiveness in the wheelchair of its buyer’s nonagenarian grandfather.)

I don’t have a good desk chair.

But do I really want a good desk chair? Over the past year I have converted my workstation to a fully portable setup. I’ve worked with a laptop as my sole computer for over a decade, and now my second monitor is an iPad. I can bring my two-screen setup anywhere.

For years we’ve been told that sitting at a desk all day long shortens our lives. I hate sitting at a desk all day anyway. And years of working in offices, where someone else paid for the series of various “real” (and surely quite expensive) desk chairs I used, taught me that there is no such thing as a good desk chair, at least not for me.

I do not sit with perfect posture. I do not sit in one position. I do not sit back in my chair, so lumbar supports are pointless. Except when I do sit back in my chair, in which case I am usually sprawled low, and the lumbar supports are again pointless.

But sitting perfectly in a precision engineered desk chair all day is still sitting at a desk all day. And when you do that, YOU DIE.

I don’t want a good desk chair.

And I really don’t want a standing desk.

(Neither do my feet, especially during one of my bouts of plantar fasciitis, such as the one I’ve been enduring over the past month as I write this.)

I just want to move around.

I am self-employed, and I have a mobile workstation. So I spend part of the day in my not-good desk chair. I spend part of the day in a lounge chair (IKEA) in our bedroom, with my feet up and the laptop living up to its name. Or that midcentury modern chair (a rare splurge from West Elm) in the living room, again with my feet up and the laptop laptop. Or — now that the weather is finally nice — sitting under the big umbrella at the cafe table we have on our deck, as long as I can find the perfect spot where its metal mesh top is unwarped and my laptop doesn’t wobble with every keystroke. Or in one of the Adirondacks* on the deck, in the afternoon when they’re shaded. Or sometimes even standing (!) with my laptop on the countertop of the pass through between our kitchen and dining room.

I’ve got options that don’t involve a good desk chair.

If I had a good desk chair, I would feel compelled to use it all the time. And then… YOU DIE.

*That would be a molded plastic Adirondack, of course. From Target. We are in our late 40s and still nearly every piece of furniture we own is from either IKEA or Target.

WordPress development in the Gutenberg era: Threading the needle of Block Theme development

As described in several of my recent posts here, I have been working for the past month or so on building my first “all-in” Block Theme for WordPress.

After nearly 4 years of adamantly resisting “Gutenberg” and the new Block Editor revolution — not because I disliked the block concept, but because I disagreed philosophically with the core team’s approach (to what constitutes a block, which types of blocks are important, and which technologies are used to manage the UI of the editing screen) — I am finally accepting that if I am to continue making a living primarily as a WordPress developer, I need to give up on my Classic Editor, Advanced Custom Fields “Flexible Content” approach, and embrace that the Block Editor is now The Way.

One of numerous challenges I’ve faced in this process (on top of the learning curve of a completely unfamiliar method of constructing themes, the dearth of adequate and up-to-date documentation, and the core team’s willingness to allow very unfinished versions of functionality to roll out in public WordPress releases) is figuring out the best way to approach some of the more complex design structures I am used to dealing with via ACF’s Flexible Content fields.

My biggest hurdle is recognizing that what I think of as a “block” is not what the WordPress core team thinks of as a “block”

Here I will admit this is a shortcoming of my own approach. I have been “opinionated” in my development approach (well, about everything, really), and created large-scale and complex “blocks” that, in Gutenberg/Block Editor terms, would really be “groups” or “block patterns,” not blocks. Gutenberg blocks are more granular.

Gutenberg blocks are also static, in that they generally do not interact with database content, or if they do, it is in very limited “bloggy” ways that don’t align with my use of WordPress as a general-purpose CMS.

So I’ve found myself falling back on ACF. I like its server-side approach. I’m more accustomed to dealing with PHP and MySQL. I use JavaScript (mainly jQuery) a fair bit for front-end interactive elements, but I don’t build complex functionality in JavaScript and I avoid AJAX if I can help it.

You can see I’m destined for a strained relationship with the Block Editor.

A concrete example: “Tiles”

One of the most idiosyncratic elements of my old ACF approach is the block I call “Tiles.” It’s a way of creating a set of small blurbs to link to other pages/posts. There are numerous options for appearance: number of tiles per row, relationship between the image and text in a tile, etc. And there are also numerous options for the content source: a specific page or post (with the title, featured image and excerpt automatically pulled in), a dynamic feed from the blog or a specific category (likewise with the info pulled in automatically, except this one auto-updates when there’s a new post), or a completely custom-built tile, where you manually select the image and enter the text and link.

Here are screenshots of the ACF-based editing tools for my Tiles block.

I tried recreating this entire setup as a new ACF-based Block field group. It worked, to be sure, but the complex ACF editing layout really did not feel right in the new Block Editor interface, either in the sidebar (eek) or in the main content area. It felt like a cop-out.

Then I considered creating a block pattern. I knew this would lack some of the benefits of my ACF-based Tiles block, but one in particular: the option to dynamically pull in the details of another page/post, rather than manually entering the text. But as a starting point, I decided to recreate the “custom” tile type.

That, also, worked. But it was finicky and didn’t apply well in a lot of different places. So I realized that instead of creating a Tiles block pattern, I needed to create a Tile block pattern. Just one tile. Instead of a monolithic block that was really a group, users would insert each individual tile into whatever larger structure they want (e.g. columns).

The end result was a block pattern that looked like this (screenshot instead of live code because, well, you really don’t want to use this):

I was really proud of myself for using the new “lock” feature to force the elements to stay in a particular order, but to allow the user to remove elements they don’t need, such as the image, lead-in text, or CTA button.

Still… I didn’t like it. And it didn’t allow for dynamic sources.

Along the way I also finally came to grasp another fundamental limitation of the Block Editor and Block Patterns. Since all of the parameters of the blocks are stored as HTML comments right in the post content, you (the theme developer) can’t update the design of a block pattern once it’s inserted into a page/post. The Block Editor isn’t dynamically inserting content into a template when the page is rendered. It’s making a one-off copy of the template and storing it right along with the content at the point when the content is inserted into the page/post.

This seems, to me, to be a fundamental flaw in the entire Gutenberg/Block Editor approach. It’s bad enough that if I were building it myself, I’d have stopped right there and taken a completely different direction. Maybe there’s a long-term plan to address this limitation, but for now it appears to be here to stay. Which led me back to ACF.

Threading the needle

And that was when I had my insight into the true nature of the problem. It was the fact that what I think of as a “block” is larger-scale than what the Block Editor treats as a block.

So I went back to my Tiles ACF field group, stripped out the repeater functionality and created a Tile ACF field group. Now you’re building one tile at a time, it has all of the previous benefits of ACF’s dynamic integration of content with template functionality, it seems to correctly fit the definition of a “block” in the WordPress sense, and you can still have a flexible presentation of multiple tiles in a group… just using Block Editor “groups” to achieve that.

I still have more to learn and questions to answer (followed by more questions to ask and then answer), but I feel like this was a major step forward in finding a way to merge the benefits of ACF with the… inevitability, I guess, of the Block Editor.

Getting out of my comfort zone… on bass and in the world

After two years of mostly not performing (or even interacting) with other people, suddenly I find myself thrust deeply into both.

I’m currently rehearsing on bass with the Minneapolis South High Community Jazz Band for a concert in the park on June 3, and I’ll also be playing bass in the pit orchestra (which is neither an orchestra, nor will it be in the “pit” for this show, but on stage!) for Summerset Theatre‘s production of the Queen jukebox musical We Will Rock You in Austin, Minnesota at the end of June.

Going into this, I figured the jarring proximity of other people in the post- (or, ongoing-) covid era would be the hardest thing for me to handle about the experience, but sight-reading sheet music for bass is reminding me of a much more enduring challenge.

I formally studied clarinet from 5th grade through my senior year of college, and I played saxophone in school jazz bands throughout high school and college as well. I was a music major, to boot. But I am entirely self-taught on bass (including reading bass clef), having mostly just played it in rock bands since first picking it up as a sophomore in high school so I could emulate Geddy Lee and Chris Squire.

I would label my bass skills as “intermediate” in general, maybe “intermediate-plus” if I’m feeling generous to myself. But I have three major weaknesses: occasional struggles to coordinate my left and right hands, less-than-instant recognition of notes above the staff in bass clef, and most significantly, a fear of the 6th to 11th frets.

It’s amazing how far you can get playing rock music and rarely venturing above the 5th fret (or outside of the 12th to 17th frets, which are easy to mentally map to the first 5). I have an immediate, instinctive recognition of where the notes on a piece of sheet music in bass clef are on the bass neck, if I’m thinking about the first 5 frets.

The problem is, jazz and musical theater expect a bit more out of a bassist. The past few years of playing in the jazz band (and having played in the pit for Summerset’s production of The Little Mermaid in 2019), along with numerous gigs as a part of 32nd Street Jazz, have forced me to finally learn the middle of the neck. But I still don’t have that immediate instinctive recognition of where the notes are.

And the weird thing about guitar and bass, compared to woodwinds, is that there are multiple places to play just about every note. So when you’re playing, say, a second line B-flat, you need to consider, “Am I going to play this on the 1st fret of the A string, or the 6th fret of the E string?” That consideration depends a lot on what comes next. If the line suddenly soars up to, say, a D above the staff, you really need to be on that 6th fret.

So far so good. If there are just a few sporadic notes, or a repetitive pattern, that sits best in the middle of the neck, I can get that down. But when there’s a fast run with a bunch of accidentals, I’m still just not tracking fast enough to pull it off. Until I have a part down cold, I often find myself in rehearsal panicking in the middle of a measure and quickly dropping back to the first 5 frets, where I immediately know a top-line A-flat is the 1st fret on the G string, for instance. Never mind the fact that in 2 beats I’m going to need to be up at the 9th fret. Eek!

It’s easy to get stuck for a long time as an “intermediate” bassist, especially if you’re in your 40s and not making a living as a musician, because there’s a big bump in the learning curve to get to that advanced level.

But maybe I’m just being too hard on myself. After all, other people are letting me play bass in these situations, so I must be doing it adequately. Right?