The one SQL query that catches almost every backdoor admin in WordPress

If a WordPress site of yours has been compromised — even briefly, even silently — there’s a very good chance it now has at least one administrator account that you didn’t create. Most WP malware families plant one as part of their persistence mechanism: drop a backdoor file, create an admin user, hide the user from the admin UI via a pre_user_query filter, and walk away. Cleaning the file alone doesn’t help if the planted user is still there with admin rights and a known password.

The attackers are sloppy in one specific way: they skip the user_registered field. WordPress’s default schema has user_registered DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', but MySQL with strict mode rewrites that '0000-00-00 00:00:00' to '1979-01-01 00:00:00' — Unix epoch zero in the local timezone for some early-2000s server defaults. The malware author didn’t pick that date deliberately; they just used INSERT without setting the column, and the database filled it in. Almost no real WordPress install has admin users registered on January 1, 1979.

That’s the audit query, in one line.

The query

SELECT u.ID, u.user_login, u.user_email, u.user_registered
FROM wp_users u
JOIN wp_usermeta m ON u.ID = m.user_id
WHERE m.meta_key = 'wp_capabilities'
  AND m.meta_value LIKE '%administrator%'
  AND ( u.user_registered = '1979-01-01 00:00:00'
     OR u.user_registered = '0000-00-00 00:00:00'
     OR u.user_email = '' );

Three signals, any one of which is suspicious:

user_registered = '1979-01-01' — epoch-zero spoof.
user_registered = '0000-00-00' — same idea on a different MySQL strict-mode setting.
user_email = '' — admins always have an email in normal WordPress flows. Empty email + admin role = user inserted directly into the DB by a script.

Run it. If you get zero rows, you’re probably clean on that vector. If you get rows, every one of them is a candidate for a planted admin.

Common username patterns to recognize

From compromise cleanups across multiple sites, the planted admins almost always look like one of:

deleted-<random> — meant to look like a previously-deleted account; e.g. deleted-Gq3fP06V.
wp_update-<timestamp> — fakes “WordPress auto-update service”; the trailing number is a Unix timestamp from when the user was created.
wpcron<hex> — fakes WP-Cron infrastructure.
root2, backup-admin, support — generic plausible-looking names.
A 32-character hex string — username equal to the MD5 hash of the malware’s mu-plugin filename. The DOLLY family does this.

If you see any of these returned by the query, treat them all as malicious. There’s no overlap with legitimate WordPress workflows.

Run it across every site at once

If you run multiple WordPress sites on the same server, you want to scan all of them, not log in to each one. The table prefix and database name vary — pull both from wp-config.php:

DB_PASS='your-mysql-root-password'

for cfg in $(find /usr/local/lsws -maxdepth 6 -name "wp-config.php" 2>/dev/null); do
  db=$(grep -oP "DB_NAME['\"]?\s*,\s*['\"]\K[^'\"]+" "$cfg")
  pfx=$(grep -oP "table_prefix\s*=\s*['\"]\K[^'\"]+" "$cfg")
  # Some sites have prefix without trailing _ (e.g. "gdrlckd_wp"),
  # so the table name is just ${pfx}users not ${pfx}_users
  users_t="${pfx}users"; meta_t="${pfx}usermeta"; cap_key="${pfx}capabilities"
  echo "--- $db ($cfg) ---"
  mysql -uroot -p"$DB_PASS" -h127.0.0.1 "$db" -e "
    SELECT u.ID, u.user_login, u.user_email, u.user_registered
    FROM \`$users_t\` u JOIN \`$meta_t\` m ON u.ID=m.user_id
    WHERE m.meta_key='$cap_key' AND m.meta_value LIKE '%administrator%'
      AND ( u.user_registered = '1979-01-01 00:00:00'
         OR u.user_registered = '0000-00-00 00:00:00'
         OR u.user_email = '' );
  " 2>&1 | grep -v Warning
done

Empty output means clean on every site. Any returned rows are your worklist.

Cleaning a hit

For each suspicious user, delete the user-meta rows first (otherwise FK cleanup might block you on some MySQL setups), then the user:

-- Replace 'deleted-Gq3fP06V' with the actual user_login
DELETE m FROM wp_usermeta m
JOIN wp_users u ON m.user_id = u.ID
WHERE u.user_login = 'deleted-Gq3fP06V';

DELETE FROM wp_users WHERE user_login = 'deleted-Gq3fP06V';

If the malware also planted persistence in wp_options (DOLLY-family malware does — it stores its config under an MD5-named option), drop those too:

-- The MD5 here matches the mu-plugin filename
DELETE FROM wp_options WHERE option_name = 'ddfa33f7f16e351f13bb9232d7362c18';

-- Reset cron entirely; WP rebuilds legitimate hooks on next request
DELETE FROM wp_options WHERE option_name = 'cron';

Then go find the file artifact — there will be one. Backdoor admins are persistence; the malware also has an entry-point. Common locations: wp-content/mu-plugins/<md5>.php, fake plugins under wp-content/plugins/<random-name>/, or webroot-level droppers like wp-admin.php (note: not the directory).

Schedule it

This query takes milliseconds. Run it from cron weekly and email yourself any results:

# /etc/cron.weekly/wp-admin-audit
#!/bin/bash
out=$(/root/wp-admin-audit.sh 2>&1)
[ -n "$out" ] && echo "$out" | mail -s "WP backdoor admins" you@example.com

Cheap, fast, high-signal. The query itself has produced exactly zero false positives across every site I’ve ever run it against, and it has caught five distinct malware families’ planted admins. If you do one piece of WordPress security hygiene this week, do this.

Animated Creativity