wp_users SQL: drie queries die tonen wie echt inlogt

Een Nederlands bureau dat we soms helpen erfde een WordPress-installatie van een voorganger. Het overdrachtsdocument zei: "drie admins, de rest zijn abonnees van de oude nieuwsbrief." Een snelle SELECT COUNT(*) FROM wp_users; gaf 412 rijen terug. Ze stelden de voor de hand liggende vraag: van die 412, wie logt er nu echt in?

De wp_users-tabel beantwoordt die vraag niet. Hij vertelt je wie er bestaat, wanneer ze zich registreerden en wat hun e-mail was bij aanmelding. Hij vertelt je niets over of ze ooit nog teruggekomen zijn. Om dat te achterhalen moet je wp_usermeta in, waar WordPress stilletjes per gebruiker session tokens, verloopdatums en de rol-en-capability-blob opslaat die bepaalt wat elk account mag.

Dit artikel is drie SQL-queries. Draai ze op volgorde tegen elke WordPress-database (single-site of multisite, ruil alleen de wp_-prefix om). Samen maken ze van een wp_users-dump een eerlijk beeld van wie de site echt gebruikt.

Het wp_users-schema en wat het weglaat

Het wp_users-schema is bewust sober. De kolommen die je krijgt zijn ID, user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status en display_name. Let op wat er ontbreekt: geen last_login, geen current_role, geen last_seen_ip. WordPress core heeft die kolommen nooit weggeschreven. Alles wat dynamisch is aan een gebruiker leeft in wp_usermeta als key-value-paren, meestal PHP-geserialiseerd.

Twee meta-keys doen het zware werk in dit onderzoek. De eerste is session_tokens, toegevoegd aan core in 4.0, met een serialized array van elke actieve sessie inclusief verloopdatum, IP, user agent en login-timestamp. De tweede is wp_capabilities, met een serialized array van roltoewijzingen. De eerste beantwoordt: "gebruikt deze persoon de site eigenlijk wel?" De tweede: "moet het ons iets schelen?"

Wil je de formele beschrijving van het user-object, dan dekt de developer-referentie voor WP_User de API. Het schema zelf staat uitgewerkt op de pagina Database Description.

Query 1: wie heeft er nu een actieve sessie

De goedkoopste filter eerst. Een gebruiker met een niet-lege session_tokens-rij heeft minstens één sessie die WordPress nog geldig vindt. Verlopen sessies worden lui opgeruimd, dus dit is geen perfect antwoord op "is op dit moment ingelogd", maar het dunt de tabel uit met 80 tot 95 procent op een doorsnee site van vijf jaar oud.

SELECT u.ID,
       u.user_login,
       u.user_email,
       u.user_registered,
       LENGTH(um.meta_value) AS session_blob_bytes
FROM   wp_users u
JOIN   wp_usermeta um ON um.user_id = u.ID
WHERE  um.meta_key   = 'session_tokens'
  AND  um.meta_value <> ''
  AND  um.meta_value LIKE 'a:%'
ORDER  BY u.user_registered DESC;

De LIKE 'a:%'-check is paranoia. De waarde hoort altijd een PHP-geserialiseerde array te zijn (a:N:{...}), maar handmatig gemigreerde sites hebben soms lege strings of een verdwaalde b:0; in de kolom staan. Sla die over.

Op de 412-rijentabel van het bureau gaf dit 38 rijen terug. Dat alleen verandert het gesprek al. Er gebruiken geen 412 mensen de site. Het zijn er 38, en bijna allemaal op subscriber-niveau.

Query 2: de laatste echte login per gebruiker

Een sessierij vertelt je dat iemand er recent was. De login-integer in de geserialiseerde blob vertelt je wanneer. WordPress schrijft bij elke authenticatie een verse Unix-timestamp in de sessie weg, en dat is daarmee het dichtst bij een echte last_login_at-kolom dat de database biedt.

Je kunt PHP niet rechtstreeks vanuit MySQL deserialiseren, maar je kunt de timestamp eruit vissen met een regex. Hiervoor heb je MySQL 8.0 of MariaDB 10.0.5+ nodig voor REGEXP_SUBSTR:

SELECT u.ID,
       u.user_login,
       u.user_email,
       FROM_UNIXTIME(
         MAX(
           CAST(
             REGEXP_REPLACE(
               REGEXP_SUBSTR(um.meta_value, '"login";i:[0-9]+'),
               '"login";i:', ''
             ) AS UNSIGNED
           )
         )
       ) AS last_login_at
FROM   wp_users u
LEFT JOIN wp_usermeta um
       ON um.user_id  = u.ID
      AND um.meta_key = 'session_tokens'
GROUP  BY u.ID, u.user_login, u.user_email
ORDER  BY last_login_at DESC;

Een NULL in last_login_at betekent dat de gebruiker geen actieve sessie heeft en je dus überhaupt geen timestamp hebt. Hij of zij is niet meer ingelogd sinds de laatste sessie-opschoning, wat voor de meeste gebruikers neerkomt op nooit.

Op dezelfde tabel gaf dit een nette splitsing. 38 gebruikers met een echte timestamp binnen de laatste 60 dagen. 14 met timestamps ergens tussen zes maanden en drie jaar oud, sessies allang verlopen waarvan niemand de meta-rijen heeft opgeruimd. 360 met NULL. Die 360 zijn de "nieuwsbriefabonnees uit 2019" waar het overdrachtsdocument vaag over deed.

Query 3: bevoorrechte accounts die nooit komen opdagen

Dit is degene die mensen stil maakt. WordPress slaat roltoewijzingen op in wp_capabilities als geserialiseerde array, bijvoorbeeld a:1:{s:13:"administrator";b:1;}. Een LIKE '%administrator%'-substringmatch is voldoende voor een audit. Maak je alleen druk om false positives als je een custom rol hebt met dat woord in de naam. Combineer het met de afwezigheid van een sessierij om de gevaarlijke vorm te vinden: accounts met admin-rechten waarvan niets erop wijst dat iemand ze gebruikt.

SELECT u.ID,
       u.user_login,
       u.user_email,
       u.user_registered,
       um_caps.meta_value AS roles
FROM   wp_users u
JOIN   wp_usermeta um_caps
       ON um_caps.user_id  = u.ID
      AND um_caps.meta_key = 'wp_capabilities'
LEFT JOIN wp_usermeta um_sess
       ON um_sess.user_id  = u.ID
      AND um_sess.meta_key = 'session_tokens'
WHERE  (um_caps.meta_value LIKE '%administrator%'
    OR  um_caps.meta_value LIKE '%editor%')
  AND  (um_sess.meta_value IS NULL OR um_sess.meta_value = '')
ORDER  BY u.user_registered ASC;

Voor het bureau gaf dit zeven rijen terug. Twee waren de eigen accounts van het voorgaande bureau, nog steeds administrator, geregistreerd in 2018. Eén was een oud-medewerker. Eén was een wp_admin_backup-account waarvan niemand de herkomst kon verklaren. Drie waren editor-accounts gekoppeld aan e-maildomeinen die niet meer bestonden.

Geen van die rijen is op zichzelf kwaadaardig. Maar het zijn allemaal credentials die, als ze ooit lekken, direct in de back office van een productiesite landen, en niemand gaat het opmerken omdat niemand naar dat account kijkt. Dat is precies het uitgangspunt van OWASP A07: Identification and Authentication Failures. De meeste schade komt van accounts die niet meer hadden moeten bestaan.

Gangbare verhoudingen op een site van vijf jaar oud

Gedraaid op een WordPress-site van vijf jaar oud is de verhouding bijna altijd hetzelfde. Ruwweg 5 tot 15 procent van de wp_users-rijen heeft een actieve sessie. Nog eens 5 tot 10 procent heeft verlopen sessiemetadata rondslingeren. De rest zijn inerte abonnees, verlaten klantaccounts of imports van een al lang vergeten plugin. Van de bevoorrechte rijen is meestal 1 tot 3 procent van de tabel administrator-niveau, en ergens tussen een derde en de helft daarvan is sinds het jaar van aanmaak niet meer aangeraakt.

De beslissing volgt niet vanzelf. Je doet geen bulk-delete op de stille gebruikers. Sommigen zijn echte klanten die één keer per jaar inloggen voor een factuur of een download-token. Maar je hoort te weten dat ze er zijn, en welke administrator-rechten hebben. Met de drie queries hierboven heb je daar in vijf minuten een getal op.

Toen we Pier bouwden liepen we keer op keer tegen precies dit patroon aan: bureaus wilden wp_users op een verouderde site auditen en eindigden met heen-en-weer springen tussen phpMyAdmin-tabs om geserialiseerde blobs op het oog te lezen. De MySQL editor leest session_tokens en wp_capabilities inline, en elke schrijfactie landt in de versiegeschiedenis, zodat een opschoning van junk-accounts één klik ongedaan te maken is.

Het kleinste wat je vandaag kunt doen

Open je productiedatabase met een read-only gebruiker (of draai lokaal een verse dump) en voer query drie uit. Als het aantal ongebruikte admin- en editor-accounts hoger uitvalt dan je uit je hoofd kunt opnoemen, weet je waar je deze week mee bezig bent.