Mistaken identity

LordRogaine

I'm using php with mysql. I have a site where members have their information stored in a database, username, password and email. When they forget their password they enter their email address and a new password is created and sent to the user at his email address. My problem is this--Let's suppose we have two memebers with very similar names-- TheBlackKnight" and TheBlackKnght" no letter "i" in "knight" in the second name. Members will request a new password for the second name using an email address, but instead of giving back a new password for the second name, the names are getting mixed up and the mail address for the second member is getting mixed up with the first member. I have not changed anything that I am aware of. Any sugestions for why this is happenning?

ClarkF1

some relevant code would be useful

void_function

In addition to the code, please supply the indices structure on the table with username and email. This sounds like bad indexing to me.

LordRogaine

Here is the structure of the table I am using along with the indexes and the coding I am using to create a new password by submiting the email address of the user.

CREATE TABLE members (
username varchar(25) NOT NULL default '',
password varchar(255) NOT NULL default '',
userid int(25) NOT NULL auto_increment,
country varchar(40) NOT NULL default '',
last_login datetime NOT NULL default '0000-00-00 00:00:00',
email varchar(60) NOT NULL default '',
notification enum('0','1') NOT NULL default '0',
user_level enum('0','1','2','3') NOT NULL default '0',
signup_date datetime NOT NULL default '0000-00-00 00:00:00',
mail_notification datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (userid),
KEY username (username),
KEY last_login (last_login),
KEY email (email)
) ENGINE=InnoDB

function makeRandomPassword() {
	$salt = "abchefghjkmnpqrstuvwxyz0123456789";
	srand((double)microtime()*1000000); 
  	$i = 0;
  	while ($i <= 7) {
    		$num = rand() % 33;
    		$tmp = substr($salt, $num, 1);
    		$pass = $pass . $tmp;
    		$i++;
  	}
  	return $pass;
}

$result = mysql_query("select username from members where email='$email'") or die (mysql_error());
while ($row = mysql_fetch_array($result)){
$username= $row["username"];
}

$random_password = makeRandomPassword();

$db_password = md5($random_password);

$sql = mysql_query("UPDATE members SET password='$db_password' WHERE username ='$username'");

ClarkF1

Why aren't you updating the table with the new password based on the email address.

If you are able to retrieve the correct username using their email address from the database ,wouldn't it make sense that you should be able to store the password back in the database in the correct row based on the email address.

It would also save having to query the database for their username

All this is assuming that you can't have multiple accounts using the same email address which could explain the problem as $username gets overwritten each time it goes through the while loop so the final value is the latest one.

Piranha

In your database structure you can have more than one users with the same username. You should set the username to UNIQUE, not KEY. It is the same for email, many users can have the same email address.

From MySql manual: KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.

In your code you use the following:

$result = mysql_query("select username from members where email='$email'") or die (mysql_error());
while ($row = mysql_fetch_array($result)){
$username= $row["username"];
}

// The above will, if there are many with the same email, select only one of the users.
// It will also use username from the database, instead of the primary key.

$random_password = makeRandomPassword();

$db_password = md5($random_password);

$sql = mysql_query("UPDATE members SET password='$db_password' WHERE username ='$username'");

// Here you set the new password to everyone that have the username you got above. Change to userid here and above.

LordRogaine

I apologize, I should have clarified a few things first. I'm using a query for the person's username because I need this information to send along with the password after the person submits their email address. There is also a check done earlier in the script that prevents duplicate usernames or email addresses so every username, userid and email address are unique in the database table. I thought that perhaps I may have made a mistake by add keys to the table where something like this may hav caused the problem add key username(username(5)), but I am not doing something like that where the name is being cut off after five characters and can be confused with a similarly spelled name. I'm not an expert on kyes or indexes but I feel the problem lies somewhere with that. Am I far off? Thanks again to everyone that is helping.

ClarkF1

If the email addresses are unique and you said the problem was with people having similar usernames, have you tried changing the query so that it updates according to the email address instead of the username?

It might not hurt ot have the data stored as unique in the database as well, just in case something slips through.

LordRogaine

Actually, the query was originally set up so that it updated according to the email address, it's only one day ago that I had made that change to see if it would change anything. I'll make the change back, but it is only going back to the way it was when the problem started.

ragedigital

You have a different 'userid' for each person - it will never repeat - use that for updating.