[Guide] 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.

good..