Some week ago I posted about the users table in mediawiki and how to get the most of it. Later on, I posted about spam measures, which are usually not enough.
Why spammers sign up and do not post?
I had this problem in the linfoma.net forum, but it could happen as well in any wiki: people who register and never bothered to contribute even once. Why do they register? Because they get at least one link for free: the one in their profile. Of course they could post some spam, but that way they could be detected quickly, and everything (including their user) would be erased and banned. Nevertheless, the link in their profile is usually silent, unless you have a look as it follows.
List of users who never contributed
Actually, there is a field on the user table which you can check quickly: user_editcount
No need to publish the query for that. Another more useful one would be the users with zero edits and some website added. This information is stored in the user_options field, either as real name or as an alias. Anyway, we look for http’s or www’s or alike:
select user_id, user_name, substring( user_options, instr( user_options, 'http' ), 20 ), substring( user_options, instr( user_options, 'www' ), 20 ) from user where user_editcount = 0 and (user_options like '%www.%' or user_options like '%http%' )
(Since user_options is a BLOB, we get just the part of it which is suspicious).
No need to delete these users by default, they could be to be registered for publishing in the future, or to get updates. And the website could be the one they represent (even other wikis). Unfortunately, most of times it is the spam story, so use this from time to time as part of your antivandals book.
Revisions by not-registered-users
Depending on your configuration, you could allow visitors to edit pages without registering. If you want to disallow this, go to your LocalSettings.php and add
# Disable anonymous editing $wgGroupPermissions['*']['edit'] = false;
If you allow (or did it at some point in your wiki’s history) anonymous users, they are registered in the revision table as rev_user 0, and their IP as rev_user_text. This is the structure you will need:
then this is the way to get the list of pages that they edited:
SELECT p.page_title, r.rev_user_text, r.rev_timestamp FROM PAGE p, revision r WHERE p.page_id = r.rev_page AND r.rev_user =0 ORDER BY 3 DESC
The the user table in mediawiki by geometrus, unless otherwise expressly stated, is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.