Advisory: Duplicate Account Problems

Ok.. ull probably wondering why i made this into an advisory.. let me explain.

for the last couple of weeks. i have been pretty busy with my other project so i didnt have time to update any of my game works,. then i notice a rather hi amount of people get there password change in my website... which i totaly doubt due to i cant imagine how they would do it.

Becoz:
1. they can't blast my server
2. they can't sql inject the server

to i was left to buggle on this problem for a few hours ago. then i came to realize that the problem was with duplicate accounts.

As you might know or not know the current MU database design is not very good. it allow duplicate rows to be created, which would result into numerous number of problem,. one of them being that two separate accounts can be created with different information. I have notice this a long time ago, but didnt give much though about until i got fed up with the complains and i finally gotten around to create some queries to erase all this duplicates.

the harm this duplicate rows cause can range from character loss or ur character being hijacked by another person, and for you noobs, this would mean ur character is not your anymore.

here is some queries that might prove useful.

to check how many of the accounts are duplicated

Quote:

---

select a.memb___id as NEW_FAKE, a.mail_addr as NEW_FAKE_MAIL, b.memb___id as ORIGINAL, b.mail_addr
from memb_info a join
(select memb___id, mail_addr from memb_info
group by memb___id , mail_addr
having count(*) > 1) b
on a.memb___id = b.memb___id ;

---

u can also add where email address is not equal to each other to check for people who are listening to account changes. this useful if your wbsite send email notification of password changes and new accounts. here is the new code to check for different email

Code:

---

select a.memb___id as NEW_FAKE, a.mail_addr as NEW_FAKE_MAIL, b.memb___id as ORIGINAL, b.mail_addr
from memb_info a join
    (select memb___id, mail_addr from memb_info
            group by memb___id , mail_addr
            having count(*) > 1) b
      on a.memb___id = b.memb___id and a.mail_addr != b.mail_addr;

---

now ur probably saying how to delete all this new accounts that are being created.

if ur using Mutoolz this task would be easily done by doing a DELETE query using the appl_days field which is the field that says wat day and time the account was created.

Quote:

---

delete from a
from memb_info a join
(select memb___id, min(appl_days) max_tran_date from memb_info
group by memb___id
having count(*) > 1) b
on a.memb___id = b.memb___id and a.appl_days > b.max_tran_date;

---

tada no more.. duplicate accounts. heheheehe.. but if ur not using Mutoolz or any other similar registration that has a working date field. this would prove to be difficult and time consuming.

be back later.. i need to cook :))

This is it, finally somebody post a thread that would solve my problems. Last two weeks I wonder why my players keep complaining about there accounts being stolen. You continually rocks John. :thumbup:

i also notice that the database doesnt enforce strick capitalization of characters. for example if you registered jhiggs then logon inside the game as Jhiggs it will allow so to do so, but the mu-characters will not show on the selection screen. meaning it thinks of it as a seperate account.

i also have a variation of the solution.. which delete all duplicates ( is this specially needed if the duplicate accounts are or have the same entries, which makes it hard to delete), but the only problem i had with this new solution is that it doesnt know which is the real one or the the fake.

so i suggest u run the old account query first.

this solution is for people who dont have real time check of account. meaning ur not using Mutoolz.


ok here is the solution:
first thing we need is to create a a new field in the memb_info which will be UNIQUE, like number seed generator

Quote:

---

alter table memb_info
add seq_seed int identity

---

where seq_seed will be a new column of the memb_info that will have a count to all entries. :) we will use this to delete the those darn stupid duplicates.

Quote:

---

delete from a
from memb_info a join
(select memb___id, max(seq_seed) max_seq_seed from memb_info
group by memb___id
having count(*) > 1) b
on a.memb___id = b.memb___id and
a.seq_seed < b.max_seq_seed;

---

there u go.. all duplicates deleted.

Please explain more further how this happens?

Q: is it done upon registration?

If so, then we can just make a form that will not
accept capitalized characters in account id to prevent duplications of accounts.

Gladly, I don't have any capitalized accountids in my database.

Make web registration allow only use digits for login, then wont be duplicate accounts

SQL query dude.

im thinking of redesigning the Mu databases.. while not killing my server in the processes.. hehehehe

lol! you're going to port everything? :icon6:

Btw, the duplicated account in MEMB_INFO will be easily deleted with different email or date join, etc, but in warehouse....U know, in warehouse, there is no difference between duplicated account...Its should be no problem cuz the duplicated cant join game, but the prob is putting a warehouse password. Yes, cant put a password to duplicated account...

it can be easily be done.. if u want i can teach u to make a ur warehouse duplicate free..

all u have to do is create a new column.. IDENTITY (int 4) it will create a count of all ur warehouse ... thus creating a UNIQUE field.. so u can use that to delete the newer record.

Yeah thanks john_d, i managed to finish this annoy. Im sorry i didnt read ur 2nd guide :D

Quote:

---

Originally Posted by jhiggs

i also notice that the database doesnt enforce strick capitalization of characters. for example if you registered jhiggs then logon inside the game as Jhiggs it will allow so to do so, but the mu-characters will not show on the selection screen. meaning it thinks of it as a seperate account.

---

If you would to this in my server it shows you: "Your account is Invalid!"

nothing realyl just moved to the guides forum..

If you 4got to put ina check for suplicate information then quick way to just check for duplicates is to do a inner join across all the tables with the accountid/memb___id in them u can use the count aggregate function on character.name so it just returns a number which shuld be the total amount of characters in the game if u have duplicates the error returned will give an indication of where to look, mostly this only occurs in memb_info <-> memb_stat as if they can register with multiple accounts but it wont let the (shuldnt let) the second account to register to log in at all so no data is inserted into memb_stat.

once uve done the above deleting just remeber that you have set the registration to query the database before hand, its alsoa good idea to use email verification username and email adress are entered first they then get the verification email saying they can continue with registration, if its a dissalowed ip/username contains bad characters it can tel lthem straight away, saves players getting stressed out thinking of the name, to stop them skipping that step and just bypassing for your login page use a unique key generated from username and email adress and send that in with the link for the login page for the check.

john_d can you make this guide more user friendly :) I don`t understand everything. my mu server not protected from dupers and sql is not my first love. many thanks in advance :)

Good Job John_D