encrypted passwords in MySQL queries

Anon

hey everyone i have a problem with a login script i'm doing and have narrowed it down to part where when a user signs up for an account and indicates a password, i specifed that field to be encrypted like so:

password('$userpassword');

when entered into the database.

The big problem is that when i pull a query like so:

$query = "SELECT * FROM user WHERE name = '$username' AND password = password('$password')";

it either hangs in the script, i get an error or nothing happens at all.

So what i tried to do is take that password() function out and not have the passwords encrypted i pulled the query like so:

$query = "SELECT * FROM user WHERE name = '$username'";

and it all seems to work. But is there a fix to my problem and i know i would need to encrypt my passwords for security reasons.

any help would be greatly appreciated. Thanks again everyone.

mrmufin

Try your query like this:

$q = "SELECT * FROM user WHERE";
$q .= sprintf(" name=\"%s\" AND",$username);
$q .= sprintf(" password=PASSWORD(\"%s\")",$password);

I validate usernames and passwords building my query that way, and I've never had a problem.

Have fun

Anon

hmm i tried your version of the query and it still seems to not be able to read the encrypted password and it cant read the passwords that werent encrypted.

and at times it still hangs there. hmmm any other suggestions?

let me try stuff out while i wait to see if there are more suggestions.

thanks again

mrmufin

When you do a "SELECT * WHERE user = \"$username\" query. And loop through the data array, is the password field encrypted?

Anon

hmm do you mean in the form or when i created the tables in the database?

here is how the form looks:

<form method=post action="user_auth_4a.php">
<input type="hidden" name="action" value="added">
<table cellpadding=2 cellspacing=0 border=0>
<td>Username:</td><td><input type="text" name="username" size=10></td><tr>
<td>Password:</td><td><input type="password" name="userpassword" size=10></td><tr>
<td> </td><td><input type="submit" name="submit" value="Log In"></td>
</table>

</form>

and when users are entered into the database i just use something like this:

INSERT INTO user VALUES('john doe', password('userpassword'), NULL);

so when the password is popped into the database you get something like this:

+--------+------------------+----+
| name | password | id |
+--------+------------------+----+
| john | 378b243e220ca493| 1|

sorry for the post being so long i just want to be descriptive.

thanks

mrmufin

okay, what I've learned from your code snippets is that the password is, in fact being encrypted as it's being inserted into the user table. So the PASSWORD() function provided by MySQL is doing it's job on when data is inserted.

Do a select from the database command prompt, using the PASSWORD() function, with the password you originally entered, just to make sure something bad isn;t happening to the data.

mysql> SELECT name, id FROM user WHERE name='john' AND password=PASSWORD("John's password");

This will help us determine if MySQL is storing what you think it's storing...

Anon

hmm i checked it out through through the msql> prompt and things seem to be checking just fine. here is a sample code i have been using to test this out:

#<?php

#include "./common_db.inc";

#if($action == added) {

#$connection = mysql_connect($dbhost, #$dbusername, $dbuserpassword) or die #("cannot connect");
#$db = mysql_select_db($default_dbname, #$connection);
#$query = "SELECT * FROM user WHERE name = #'$username' AND password = #password('$userpassword')";
#$result = mysql_query($query);

#while($row = mysql_fetch_array($result)) {
i#f($row["password"] == $userpassword) {
#echo "Logged in";
#} else {
#echo "Not logged in";
#}

}

#?>

now if youtake out the AND password = password($username) and jsut leave the name = $username, the whole script works, but its that part that messes everything up.

thanks again for the help and this little bugger is getting annonying now haha.

mrmufin

Okay, I don't think the problem is in the query, but in the way you're handling the result set. $row["password"] will always be the encrypted mysql password, NOT the equivalent user-supplied un-encrypted password.

Try re-writing the query as follows:

"SELECT COUNT(*) FROM user WHERE name=\"$username\" AND password=PASSWORD("$password");

And then deal with the result as follows, since only
while($row = mysql_fetch_row($result)) {
if($row[0] > 0) {// There was a row that satisfied the query, so the user name and password is valid. End o' story.
}
}

Your select statement is returning * (all stored data which satisfies the criteria specified in the WHERE clause), which means it's returning the ENCRYPTED password, which should never be the same as the user-provided password. Then you're trying to test if that encrypted password matches what the user provided; it doesn't. If a row of data exists that satisfies the original query, there's no need to attempt to re-verify that data; either the row exists or it doesn't. I suspect what's really failing is your if statement, which is not necessary...

Anon

excellent thanks a lot everything seems to work out jsut fine now. weeeeeeeee.

now time to see how i can incorporate sessions.