Unwelcome anonymous accounts

Some time ago someone installed a group of the WP e-Commerce plugins on an internal WordPress site. Recently I noticed a boatload (600+) of “anonymous” accounts when auditing the wp_users table. What I did follows.

This is what you call a Known Issue. At least to the 143 people who commented on WP e-Commerce: spam users in wp_users after wpsc upgrade beginning 2 months ago. While it’s still unclear to me exactly what happened, it looks like at some point these plugins were leveraging anonymous accounts as part of their shopping cart subroutines. In answer to all the questions raised by the post and later comments, the plugin developers assured everyone that everything was alright and that those accounts were harmless.

The only remedial step the developer recommended, adding the following line to wp-config.php, had no effect:

define('WPSC_CUSTOMER_DATA_EXPIRATION', 3600*12);

Looking at the source code myself, I noted that it did appear to contain a routine to purge customer data after 48 hours, but my poor coding skills didn’t allow me to verify what exactly that customer data was.

Having just finished migrating the 30 or so blogs on an 8 year-old WP Multi-User site over to a brand new WP Multi-Site instance because the database had been bloated to 9G due to someone’s having installed and enabled the default settings for the Wassup statistics plugin, I was a tad more sensitive to the question of misbehaving plugins than some less experienced sysadmin might be (that last line is proof that an M.A. degree is no assurance that the holder isn’t capable of writing run-on sentences).

Ultimately I went ahead and deleted the offending accounts using a MySQL query that I hesitate to share here for fear that some neophyte WP admin will bork their whole site and blame me.

But since one of the things I find most frustrating about the WordPress forums, and the Codex for that matter, is the dearth of system administration information found in them, I’m going to go ahead and outline what I did and how I did it.

DO NOT TRY THIS AT HOME. I AM A PROFESSIONAL.

Or, in the words of GPL v2.0:

BECAUSE THE PROGRAM IS LICENSED FREE OF CHARGE, THERE IS NO WARRANTY FOR THE PROGRAM, TO THE EXTENT PERMITTED BY APPLICABLE LAW. EXCEPT WHEN OTHERWISE STATED IN WRITING THE COPYRIGHT HOLDERS AND/OR OTHER PARTIES PROVIDE THE PROGRAM “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE PROGRAM IS WITH YOU. SHOULD THE PROGRAM PROVE DEFECTIVE, YOU ASSUME THE COST OF ALL NECESSARY SERVICING, REPAIR OR CORRECTION.

So what did I do? Well, first I decided to get a list of all the accounts at issue, to make sure they weren’t “real” user accounts.

mysql> select user_login,user_registered,user_status,display_name
    -> from wp_users
    -> where user_login REGEXP
    -> '^_([a-z]|[0-9])+'
    -> into OUTFILE '/tmp/anonusers.txt'
    -> fields terminated by ','
    -> enclosed by '"'
    -> lines terminated by '\n';

Once I’d eyeballed that list I was convinced that those accounts were, indeed, not those of real users, and therefore in my small sysadmin’s mind, unneeded.

Deleting the accounts was simple. No fussing with the WP gui or going out and installing phpMyAdmin (perhaps the single biggest contributor to SQL ignorance among WP admins). Just some simple and direct SQL:

mysql> delete from wp_users
    -> where user_login REGEXP '^_([a-z]|[0-9])+';

That was it. All done.

Of course I did this in my development environment first, because I NEVER do anything in production before trying it out in dev. I also enlisted by own WP admin to check things out in dev to make sure I hadn’t missed some hidden issue (he could do that because dev is still substantially like prod, although sadly not identical).

Closing advice to sysadmins who are involved with WP sites and, especially, to WP admins: study the WP database schema, learn SQL, and most of all ALWAYS TEST PLUGINS BEFORE DEPLOYING IN PRODUCTION. I know I will. From now on.

This entry was posted in Development, System Administration, Systems Analysis, Web on by .

About phil

My name is Phil Lembo. In my day job I’m an enterprise IT architect for a leading distribution and services company. The rest of my time I try to maintain a semi-normal family life in the suburbs of Raleigh, NC. E-mail me at philipATlembobrothersDOTcom. The opinions expressed here are entirely my own and not those of my employers, past, present or future (except where I quote others, who will need to accept responsibility for their own rants).