mysql password encryption problems

[wholcomb]

I had a user log in system that used mysql's password encryption. The password was being written to the database encryped but the retrieval of the encrypted password didn't seem to work. For now I just removed the password() function call in all of the functions that used it and the system works fine. Last night I installed Noah's Classified and it looks like that uses the same mysql passwod function. The admin account was created and has the encrypted password but I can't log in. Has anyone else had this problem and if so were you able to fix it?

[burntheman]

wholcomb,

About a year later and I have an answer for you. I was just having the same problem with my password encryption and retrieval on my site. I looked into it further and found the following solution.

Instead of using the password() function, which was designed to be used by the mysql database for its own user management (not for the user management of developers), you should :

1. when creating the table that will store your username and passwords,
(if using BH phpmyAdmin interface)
define the password field as type:char and coallition:binary

(if using mySQL command line)
mysql> create table users (
user VARCHAR(20) not null primary key,
password CHAR(32) binary not null );

note: it is important that you define the password field to be a binary char string ... I assume this works because it prevents formatting of the password string which may cause for problems when encoding/decoding.

2. When inserting a new row
(if using BH phpmyAdmin interface)
when entering the username value, select no function to apply to the value (default value for function is blank, leave it that way for username) enter the username into the value field
when entering the password value, select MD5 in the dropdown function list then just enter the password as is into the value field.

(if using mySQL command line)
mysql> INSERT INTO users
values ("user", MD5("pass") );

3. to check if user entered correct password from your script
you should use the following in your PHP code:

//this formulates the query
$sql = "SELECT *
FROM users
WHERE user='$_POST[user]' and password = MD5('$_POST[pass]) ";

//this executes the query
$result = mysql_query($sql);

//this checks for number of results returned by query
$num = mysql_numrows($result);

//$num = 0 if the query returned no results ... therefore meaning the user inputted the wrong username/password combo
//$num = 1 if the query returned 1 result, meaning the user passed in the correct username/password information.

if($num != 0)
{
//log the user in however you want to
}
else
{
//tell the user to try again
}


I hope the wait was worth it ...

Someone please tell me if this is not the best method to implement a secure user login form.