PHP/MySQL: Changing user passwords problems

T

Thank The Cheese

Registered
Hello!

I am a huge PHP/MySQL n00b, so this is probably a simple thing, but I can't figure it out.

I created this script to change user passwords (which I am very proud of :))
It works great:

Code: 
...
[COLOR="Red"]
$query="
SELECT user_id
FROM users
WHERE (username = '$username' AND password='$password')
";
[/COLOR]
$result = @mysql_query($query);
$num = mysql_num_rows($result);

	if($num == 1)  
	{
	$row = mysql_fetch_array($result, MYSQL_NUM);
[COLOR="Red"]
	$query="
	UPDATE users
	SET password = '$newpassword'
	WHERE user_id = $row[0]
	";
[/COLOR]
	$result = @mysql_query($query);

		if(mysql_affected_rows() == 1)
		{
		echo '<p>Password changed.</p>';
		exit();
		}else{
		$message = '<p>An error occured:'.mysql_error().'</p>';
		}

	}else{
	$message = '<p>Your usename and/or password do not match our records</p>';
	}

mysql_close();

...

However I have since learnd that you can encrypt passwords that are sent to MySQL using the PASSWORD('password) syntax. This is a pretty important thing to do, so I tried to implement that by changing the queries above to:



$query="
SELECT user_id
FROM users
WHERE (username = '$username' AND password=PASSWORD('$password'))
";



and


$query="
UPDATE users
SET password = PASSWORD('$newpassword')
WHERE user_id = $row[0]
";


but this stops it from working (always returns "does not match our records"). Can't figure it out. I thought maybe it only works if the password it's changing is already encrypted, so I encrypted a few passwords and trie changing those but the same thing happens.

Am I doing something wrong?

cheers! :D
 
Hmm... this is just a shot in the dark, but you're comparing a plain-text password to an encrypted password, which, 999,999,999 out of 1,000,000,000 will always be different.

My logic would dictate that you need to encrypt the password stored in the database, and compare that to the encrypted password.

It is late, though, and the wine may be making me think I'm smarter than I really am... ;)
 
Hey ElDiablo, thanks for the reply.

forgive me if I misunderstoof you, but...

To test that, I went into Terminal and manually changed the record using this command:

UPDATE users
SET password = PASSWORD('password')
WHERE user_id = 1;

So that particular record was encrypted. (made sure by selecting the record after). Then I tested the PHP script again, making sure I tried to change the password of the user that has the user_id of 1. still didn't work :(
 
Thank The Cheese said:
Hey ElDiablo, thanks for the reply.

forgive me if I misunderstoof you, but...

To test that, I went into Terminal and manually changed the record using this command:

UPDATE users
SET password = PASSWORD('password')
WHERE user_id = 1;

So that particular record was encrypted. (made sure by selecting the record after). Then I tested the PHP script again, making sure I tried to change the password of the user that has the user_id of 1. still didn't work :(
Click to expand...


Sorry for the late reply. That update you did changed user_id 1's plain text password to the encrypted version of the string 'password'. The query you should have run was:

UPDATE users
SET password = PASSWORD(password)
WHERE user_id = 1;

Note without the ''.
 
thanks for the help yakasha,

I must be really thick. I manually set the new password using the corrected syntax you suggested in Terminal:

UPDATE users
SET password = PASSWORD(password)
WHERE user_id = 1;

But it still didn't work :( So I'm gong post the entire php code that handles the username/password form in the hope that what I've done wrong will be clearer!:

PHP: 
if (isset($_POST['submit'])) { // Handle the form.

require_once ('mysql_connect.php');

	function escape_data ($data) 
	{

	global $dbc;

		if (ini_get('magic_quotes_gbc')) 
		{
		$data = stripslashes($data);
		}

	return mysql_real_escape_string ($data, $dbc);
	}

$message = NULL; // Create an empty new variable.
	
	// Check for a username.
	if (strlen($_POST['username']) > 0) {
	$username = escape_data($_POST['username']);
	} else {
	$username = FALSE;
	$message .= '<p>You forgot to enter your user name!</p>';
	}

	if (strlen($_POST['password']) > 0) {
	$password = escape_data($_POST['password']);
	} else {
	$password = FALSE;
	$message .= '<p>You forgot to enter your password!</p>';
	}
	
	if ($username && $password) { 

	$query="
		SELECT user_id, first_name
		FROM users
		WHERE username='$username' AND password=PASSWORD($password)
		";

	$result = @mysql_query($query);

	$row = mysql_fetch_array($result, MYSQL_NUM);

		if ($row) //if query returned a row
		{

		session_name('YourSessionID');
		session_set_cookie_params(3600);
		session_start(); 

		$_SESSION['first_name'] = $row[1];
		$_SESSOPM['user_id'] = $row[0];
		header ("Location: loggedin.php");

		exit();

		} else {
		$message = '<p>The username and/or password did not match our records.';
		}

	mysql_close();	

	} else {

	$message .= '<p>Please try again'.$username.'</p>';

	}

}

Notice the query near the bottom there. I have tried it with the line:

WHERE username='$username' AND password=PASSWORD($password)

and with

WHERE username='$username' AND password=PASSWORD('$password')

(notice one uses the ' ' and one doesn't use them)

If I remove the password=PASSWORD(password) syntax and just use password=password (ie. not encrypted) then it works, provided the password it is looking for is NOT encrypted already. So, I know the code works, it is clearly the encryption that is causing me problems :/

thanks for the help!
 
Code: 
SET password = PASSWORD(password)

sets the password to be an encrypted hash of the whole password column, which is probably not what you want.

Code: 
SET password = PASSWORD('$password')

sets the password to the PHP string $password.

Is the password column long enough to store the encrypted password? PASSWORD hashes require 41 bytes, e.g. VARCHAR(41)
 
ksv said:
Is the password column long enough to store the encrypted password? PASSWORD hashes require 41 bytes, e.g. VARCHAR(41)
Click to expand...



aaAAAaaaah, that's what it was. I'd set the field to be max 16 characters.

thanks so much for that :D
 
You must log in or register to reply here.
Back
Top