I am steadily seeing an increasing number of 6 digit users with wierd names like "B79eb7" registering on my site. Who/what is creating these?
If I'm happy to edit the database can someone talk me through which tables reference the user table and how safe it is to delete these if they have never made any real edits to any content
(Actually does anyone have a script to remove all users who have never edited anything?)
The mediawiki faq has a little section on deleting users but it's not enough to understand the DB structure and figure out what needs to be changed
Any assistance gratefully appreciated
Ed W
Ed W (lists@wildgooses.com) [050623 19:11]:
I am steadily seeing an increasing number of 6 digit users with wierd names like "B79eb7" registering on my site. Who/what is creating these?
Appears to be an experimental Wikipedia spambot, being run by someone too clueless to realise that a tiny wiki will notice whereas Wikipedia probably wouldn't. Has anyone found anything out about this spambot?
- d.
Here are some ideas that may help you with bots registering: * Require an e-mail. Even if the bot still goes and registers, you have a contact (hopefully). * 1.5 w/enotif: E-mail verification * Log the IP when a user registers/logs in
On 6/23/05, David Gerard fun@thingy.apana.org.au wrote:
Ed W (lists@wildgooses.com) [050623 19:11]:
I am steadily seeing an increasing number of 6 digit users with wierd names like "B79eb7" registering on my site. Who/what is creating these?
Not new.
If I'm happy to edit the database can someone talk me through which tables reference the user table and how safe it is to delete these if they have never made any real edits to any content
It is _highly_ discouraged. However, considering that no one wants a dozen hex users, I'd say it's ok for this.
I gave a detailed explaination during the previous rash. Search the archives.
Note that while it's fairly straight forward if none of them have done anything, as soon as they've edited, it gets trickier.
(Actually does anyone have a script to remove all users who have never edited anything?)
No.
Appears to be an experimental Wikipedia spambot, being run by someone too clueless to realise that a tiny wiki will notice whereas Wikipedia probably wouldn't. Has anyone found anything out about this spambot?
Yes, it's a bot. No, we have no idea who it is. (Not even so much as an IP.)
-- Jamie ------------------------------------------------------------------- http://endeavour.zapto.org/astro73/ Thank you to JosephM for inviting me to Gmail! Have lots of invites. Gmail now has 2GB.
Ed W wrote;
If I'm happy to edit the database can someone talk me through which tables reference the user table and how safe it is to delete these if they have never made any real edits to any content
I had a similar problem, at one point I had ~250 'real' users and ~250 'bot-fakes. Not sure what the 'bot created users were about, but they seemed to have come in three waves with a decreasing number of fake users created per wave. I excised all of the 'bot created users from my database about two weeks ago and have had no new ones created since. I'll try and go over the steps I took to excise the fakes, but please keep in mind that A) I didn't write things down, so I might forget something, B) I know diddly about SQL, C)I know diddly about MediaWiki, D) It is never, ever, ever a good idea to follow my advice on anything. Oh, and make sure you back up your database before doing anything. Maybe back it up twice. Or maybe three times. Just in case.
1) Using phpMyAdmin I went into the user table and deleted all of the fake users (hit browse, check the ones you want gone, hit delete).
2) Some users had signed up during and after the waves of bot created phonies. I renumbered the user_ids of these users to make things consecutive again, being careful to make a list of 'before' and 'after' numbers.
3) I changed the user_id auto_increment to new highest user_id number +1.
4) I went into the user_rights table and deleted all entries past the new highest user_id number.
5) I went into the watchlist and checked to see if there were any entries with a user_id that had been changed and updated those (id est, if I had changed user_id 428 to 250 in the user table, I changed it in watchlist if there was an entry for that user_id).
6) Two of the users with a changed user_id number had done editing on the wiki, so I went through and searched tables that seemed likely (ignoring tables like brokenlinks, categorylinks, hitcounter, and others that seemed unlikely to have user entries or data in them) for those user_ids, changing them where they appeared to the updated user_id.
While something of a PITA, it was easier to do than I expected. The 'bot created users are gone from my database and everything seems to be running fine.
I hope that's of some help.
Myria
On 23 Jun 2005, at 13:35, Myria wrote:
I had a similar problem, at one point I had ~250 'real' users and ~250 'bot-fakes.
I have had problems with bots on my "stock" sites, but I added an extra field to the login screen of another site, and haven't had a single bot there. I hacked MediaWiki on that site to require first and last names, which are then concatenated into the user name.
:::: In medieval days, a peasant could be executed for stealing a crust of bread, while the lord of the manor could abuse every peasant in sight without fear of legal retribution. Does it strike you that we seem to be reverting more and more to a similar society, one in which the punishments no longer fit the crimes? -- Ed Foster :::: :::: Jan Steinman http://www.IslandSeeds.org ::::
Quoting Myria, from the post of Thu, 23 Jun:
steps I took to excise the fakes, but please keep in mind that A) I didn't write things down, so I might forget something, B) I know diddly about SQL, C)I know diddly about MediaWiki, D) It is never, ever, ever a good idea to follow my advice on anything. Oh, and make sure you back up your database before doing anything. Maybe back it up twice. Or maybe three times. Just in case.
- Using phpMyAdmin I went into the user table and deleted all of the fake
users (hit browse, check the ones you want gone, hit delete).
not a bad idea.
- Some users had signed up during and after the waves of bot created
phonies. I renumbered the user_ids of these users to make things consecutive again, being careful to make a list of 'before' and 'after' numbers.
I advise against that! It messes up the entries in other tables where "user #348 edited this page" points to a user that is no longer there or hasn't even subscribed yet. As you pointed in points 5 and 6.
It's not a major issue since users don't have special rights on their edits in the current mediawiki versions, but in general, renumbering a database table's key is a potetial source for a mess.
I changed the user_id auto_increment to new highest user_id number +1.
I went into the user_rights table and deleted all entries past the new
highest user_id number.
lucky all of them had the same rights I guess. otherwise you might have ended with the rights of the old user given to a new one after the renumbering...
- I went into the watchlist and checked to see if there were any entries
with a user_id that had been changed and updated those (id est, if I had changed user_id 428 to 250 in the user table, I changed it in watchlist if there was an entry for that user_id).
that's the mess I meant.
might as well have left the numbers and just deleted the bots from the list... so the users' table has gaps in it, who cares?
- Two of the users with a changed user_id number had done editing on the
wiki, so I went through and searched tables that seemed likely (ignoring
...which would have been a non-issue had you just left the users with their numbers.
last but not least, one of the dangers of changing a user's UID retroactively are persistant cookies. I'm not sure how sessions are saved in MW, but there is the chance that after you renumber users, one of them will surf to the site and discover that his cookie lets him in connected as someone else (the new owner of his old UID).
in short - do not renumber users...
Ira Abramov wrote:
I advise against that! It messes up the entries in other tables where "user #348 edited this page" points to a user that is no longer there or hasn't even subscribed yet. As you pointed in points 5 and 6.
I'm unclear on why you see this as such a problem. If I change user_id 450 to 250 *and* update every instance of user_id 450 in the database to 250 everything should remain consistent.
lucky all of them had the same rights I guess. otherwise you might have ended with the rights of the old user given to a new one after the renumbering...
Probably something I should have mentioned. Since I'm the only sysop on my wiki, this wasn't an issue. Seems unlikely, if not impossible, that a user created in between the bot creations would have special rights, but it's something to be aware of.
that's the mess I meant.
Yes, but if all of the references are updated, it isn't a mess.
might as well have left the numbers and just deleted the bots from the list... so the users' table has gaps in it, who cares?
Obviously... I do :).
My concern was that at some point in the future those gaps were going to come back and bite me on the bum. Gaps in databases have a bad habit of causing hard-to-diagnose problems a couple of upgrades out.
last but not least, one of the dangers of changing a user's UID retroactively are persistant cookies.
Possible, and, believe it or not, something I did consider, but in the end decided it seemed pretty unlikely. I presume, perhaps incorrectly, that out of user_id, username, and password at least two have to match else I could just manually change my cookie to show UID of 1 on any wiki and likely hijack a sysop account. Even ignoring that, quite frankly 99.99% of my users never do squat so they'd be pretty unlikely to notice all of the awesome evil powers a changed UID could grant them anyway.
Myria
$wgAccountCreationThrottle also limits the number of accounts that can be created by an IP.
-- Jamie ------------------------------------------------------------------- http://endeavour.zapto.org/astro73/ Thank you to JosephM for inviting me to Gmail! Have lots of invites. Gmail now has 2GB.
mediawiki-l@lists.wikimedia.org