Apr
26
2012

Export all user email addresses as text, not BLOB, from MediaWiki

A client for a recent MediaWiki consultancy project of ours wanted to export all user email addresses as text from MediaWiki as part of an audit trail they were required to comply with.

This isn’t quite as simple as it sounds; many fields in the MediaWiki users table are in the BLOb (Binary Large Object) format, and so the original text (in this case, the user’s email address), is obfuscated. The following MySQL query neatly selects all email addresses (the user_email field) from the user table and converts them from BLOb to a format you can read:

SELECT CAST(user_email AS CHAR CHARACTER SET utf8) FROM user

So, if you have phpMyAdmin to hand, you can run this query through the SQL panel in the database, and then export all of your user’s email addresses to CSV or MySQL format (which might be handy if you need to import the email addresses in to another application’s user table).

Hopefully this quick tip will save you some time when you’re trying to provide a list of user’s email addresses in MediaWiki!