I know the official line is that one should never need to delete a user, but, as with a few others here, my wiki recently got 'registration spammed' (if you can call it that). There are roughly 200 users with names like "0021fb" and "34a5c8" (see http://wolfandturtle.net/Indigo/index.php?title=Special:Listusers&limit=... offset=0). Since my site attracts surprisingly few cyborgs from the future (they have names like that, dontchaknow...), I assume this was all done by a bot. Why a bot would do this, I have no idea, but it almost certainly wasn't to further the goals of my wiki. And, since these 200-odd machine annoyances represent not quite half my 'real' registered user base and make user statistics, the user list, and god knows what else essentially useless or annoying to use, and since they bloody well annoy me to no end, I'd like to delete them... With extreme prejudice.

Unfortunately this proves to be something far more easily said than done. I can delete the little buggers out of the User table, which excises those faux-cyborgs from the user list, but the wiki still shows the same number of users, regardless. Poking around the database I see numerous references to those user_id's in places like user_rights (Users have rights? Why doesn't anyone ever tell me these things...?), and I assume that the number of users on the statistics page is either coming from one of those or from, perhaps even more simply, the highest user number.

All of which leads me to my question(s). First, if I delete these users from the user table and then troll through the rest of the database deleting out all of the places those particular user_id's show up, are those user_id blank spaces going to cause me problems later? Second, is there any way to renumber the non-bot created users that remain (there having been people who signed up during and after the couple of bouts of 'bot user creation) -- mind you, 98% or so of the legitimate users on my wiki have not actually done squat (In fact I can't imagine why they signed up in the first place, but what do I know?) so renumbering them, I should imagine, shouldn't cause huge issues. Lastly, is there any other option for getting rid of these, short of wiping the whole bloody thing as having been an excruciatingly bad idea in the first place, that I haven't thought of? I really don't relish the thought of going through and manually changing the /nicks of 200-odd users to Annoyingbot1, Annoyingbot2, Annoyingbot3... Annoyingbot201. Not to mention that it really doesn't solve the problem.

Any suggestions... Well, any suggestions short of filling the server room with C4, as I'm already contemplating that one, would be welcome...

Myria

------------------------- IndigoWiki URL : http://wolfandturtle.net/Indigo/ MediaWiki Version : 1.4.3 PHP Version : 4.3.10 MySQL Version : 4.0.16 Sanity Version : .1alpha

Hi,

http://www.fxparlant.net/Category:Mediawiki#Captcha On my wiki, visitors can only edit the discussion pages: but as long as you log on, you can try my captcha plugin on any Talk page.

Sincerely Fxparlant (François)

Jama Poulsen wrote:

On Sun, May 29, 2005 at 12:07:05PM -0400, Myria wrote:

...

Any suggestions... Well, any suggestions short of filling the server room with C4, as I'm already contemplating that one, would be welcome...

Seeing all these complaints about wikibot spam and bogus accounts, I'm considering using an image verification tool for the account creation process on Wikicompany. Eg. using a short string of digits in image form as acceptable proof of direct human interaction.

Has anyone already used an image verification tool with mediawiki? If not, I'll try to setup something like like this, and hope it can be made modular enough to be usable with a mediawiki configuration switch.

Then I'll ask for evil-bot detection and good-bot repair tools :)

Jama Poulsen

Hi Dori,

You are absolutely right Dori, captcha are totaly against the disabilities act for webdesigners. The option was also to give a mp3 solution for the words or letters given in the image.

I haven't got much time yet, but I think we could find a way to implement simple questions protection.

I guess your math-captcha will drasticaly increase the arithmetic level of teenager posting night long on forums... or it's the death of teenage internet :-)

Why not geographic questions too ? :-o)

François

Dori wrote:

On 5/29/05, Jama Poulsen jama@debianlinux.net wrote:

...

On Sun, May 29, 2005 at 12:07:05PM -0400, Myria wrote:

...

Any suggestions... Well, any suggestions short of filling the server room with C4, as I'm already contemplating that one, would be welcome...

Seeing all these complaints about wikibot spam and bogus accounts, I'm considering using an image verification tool for the account creation process on Wikicompany. Eg. using a short string of digits in image form as acceptable proof of direct human interaction.

Has anyone already used an image verification tool with mediawiki? If not, I'll try to setup something like like this, and hope it can be made modular enough to be usable with a mediawiki configuration switch.

Then I'll ask for evil-bot detection and good-bot repair tools :)

I am not sure how many blind people participate on wikis, but why not go with a simple [math] question that can be answered easily and unambiguously by a person but not by simple bots. I'm not suggesting a full blown Turing test, something as simple as "1 plus 1 is ?" Have a series of these and throw one out randomly.

Quoting Jan Steinman, from the post of Sun, 29 May:

On 29 May 2005, at 10:51, FxParlant wrote:

...

Why not geographic questions too ? :-o)

Seriously! I think bot writers would figure out math questions pretty quickly -- just pipe it to bc(1), for instance.

I think simple questions about current events would both be fair to blind people, AND discriminate against those who don't keep up with things -- two desirable features! :-)

Hey, this actually beats the plan I was putting together, infanticide in order to raise the average netizen age once more to where it was 12-15 years ago :)

much better than the silly captcha... but now themath-dyslexic people will hate you, and there are more of those than blind people, I think :)

How about "Are you a bot? yes/no", but it's a trick question, you see? Anyone answering he's not a bot is obviously a bot trying to hide its identity and you filter them out. case closed.

Jamie Bliss wrote:

On 5/29/05, Dori slowpoke@gmail.com wrote:

...

I am not sure how many blind people participate on wikis, but why not go with a simple [math] question that can be answered easily and unambiguously by a person but not by simple bots. I'm not suggesting a full blown Turing test, something as simple as "1 plus 1 is ?" Have a series of these and throw one out randomly.

I saw an article, I think in the recent edition of TPJ (the perl journal), on exactly this. Simple question that would otherwise require a bot to be programmed to answer

Of course something as public as wikipedia will need something stronger because it wouldn't be that hard to program a bot to answer the question

For partially sighted users the question still works (if you don't use graphics), but I have noticed people using a parallel audio test as well. This could be useful, but is annoying for me since I disable all the audio on most of my machines.

Please do consider partially sighted users though - in many countries it is now *law* that websites must not discriminate, and it's so easy to just ignore a minority, *because* they are a minority. Website admins will just switch on whatever system you build, so please make it suitable for everyone....

(There must be something already written which can be just plugged in?)

By the way, the serious spammers apparently use a bunch of software which feeds the images to banks of humans to tap in the answers and lets them sign up to accounts that way. So in the face of this level of serious action I think the goal is really only to stop automatic hacking bots run by 14 year olds

Ed W

One thing that could be done is to create a user called "spambot" and reassign any references necessary to that.

Here's places where the ID should just be deleted (where {UID} is a bad ID), described in SQL: * DELETE FROM `user` WHERE `user_id` = {UID}; * DELETE FROM `user_newtalk` WHERE `user_id` = {UID}; * DELETE FROM `user_rights` WHERE `ur_user` = {UID}; * DELETE FROM `watchlist` WHERE `wl_user` = {UID};

Here's places where the ID should be changed (same format as above, except {NewID} is the ID of "spambot"): * UPDATE `validate` SET `val_user` = {NewID} WHERE `val_user` ={UID}; * UPDATE `recentchanges` SET `rc_user` = {NewID}, `rc_user_text` = 'SpamBot' WHERE `rc_user` = {UID} * UPDATE `archive` SET `ar_user` = {NewID} WHERE `ar_user` = {UID} * UPDATE `old` SET `old_user` = {NewID} WHERE `old_user` = {UID} * UPDATE `oldimage` SET `oi_user` = {NewID}, `oi_user_text` = 'SpamBot' WHERE `oi_user` = {UID} * UPDATE `cur` SET `cur_user` = {NewID}, `cur_user_text` = 'SpamBot' WHERE `cur_user` = {UID} * UPDATE `image` SET `image_user` = {NewID}, `image_user_text` = 'SpamBot' WHERE `image_user` = {UID} * UPDATE `logging` SET `log_user` = {NewID} WHERE `log_user` = {UID} (I skipped ipblocks above. If any of these people are blocking IPs, they shouldn't be deleted).

Also, ss_users in site_stats should be adjusted appropriately.

The basic technique is to delete the user and change/remove all references to them.

Disclaimer: I do not recomend just running these queries on your DB. I am not known for my SQL abilities, so it is best to validate them or just user what you need.

On 5/29/05, Myria myria@wolfandturtle.net wrote:

I know the official line is that one should never need to delete a user, but, as with a few others here, my wiki recently got 'registration spammed' (if you can call it that). There are roughly 200 users with names like "0021fb" and "34a5c8" (see http://wolfandturtle.net/Indigo/index.php?title=Special:Listusers&limit=... offset=0). Since my site attracts surprisingly few cyborgs from the future (they have names like that, dontchaknow...), I assume this was all done by a bot. Why a bot would do this, I have no idea, but it almost certainly wasn't to further the goals of my wiki. And, since these 200-odd machine annoyances represent not quite half my 'real' registered user base and make user statistics, the user list, and god knows what else essentially useless or annoying to use, and since they bloody well annoy me to no end, I'd like to delete them... With extreme prejudice.

Unfortunately this proves to be something far more easily said than done. I can delete the little buggers out of the User table, which excises those faux-cyborgs from the user list, but the wiki still shows the same number of users, regardless. Poking around the database I see numerous references to those user_id's in places like user_rights (Users have rights? Why doesn't anyone ever tell me these things...?), and I assume that the number of users on the statistics page is either coming from one of those or from, perhaps even more simply, the highest user number.

All of which leads me to my question(s). First, if I delete these users from the user table and then troll through the rest of the database deleting out all of the places those particular user_id's show up, are those user_id blank spaces going to cause me problems later? Second, is there any way to renumber the non-bot created users that remain (there having been people who signed up during and after the couple of bouts of 'bot user creation) -- mind you, 98% or so of the legitimate users on my wiki have not actually done squat (In fact I can't imagine why they signed up in the first place, but what do I know?) so renumbering them, I should imagine, shouldn't cause huge issues. Lastly, is there any other option for getting rid of these, short of wiping the whole bloody thing as having been an excruciatingly bad idea in the first place, that I haven't thought of? I really don't relish the thought of going through and manually changing the /nicks of 200-odd users to Annoyingbot1, Annoyingbot2, Annoyingbot3... Annoyingbot201. Not to mention that it really doesn't solve the problem.

Any suggestions... Well, any suggestions short of filling the server room with C4, as I'm already contemplating that one, would be welcome...

                                            Myria

---

IndigoWiki URL : http://wolfandturtle.net/Indigo/ MediaWiki Version : 1.4.3 PHP Version : 4.3.10 MySQL Version : 4.0.16 Sanity Version : .1alpha

---

MediaWiki-l mailing list MediaWiki-l@Wikimedia.org http://mail.wikipedia.org/mailman/listinfo/mediawiki-l