problem with query using password() function

[soren]

I've created a simple table called users. This table contains four columns. First_Name, Last_Name, User_Name, Password. I've used the password() function when inserting a user. I have read the documentation on using other types of encryption, but it's killing me that I can't get this to work. Robert has a User_Name of "Bob" and a password of password('tomato').

===============================
HOW I ADDED BOB
insert into users values("Robert","Evans","bob",password('tomato'));

1 row added yada yada

HOW I QUERY BOB
select * from users where User_Name="bob" and password=password('tomato');

Empty Set

===============================

:mad:

That exact same query is in a tutorial for a simple PHP login form, but an empty set is returned even in the command prompt using MySQL by itself, therefore the script cannot move on. Thanks for any help.

soren

[raf]

Welcome here!

should work. make sure you entered the exact same pasword and username (same case and all).

but you realy shouldn't use the mysql PASSWORD() function cause different hashingalgortimes are used in different mysql-version, so you can't upgrade your mysql-version!which makes it completely useless for you. the reason for this is
The PASSWORD() function is used by the authentication system in MySQL Server, you should not use it in your own applications. For that purpose, use MD5() or SHA1() instead.
from http://dev.mysql.com/doc/mysql/en/Encryption_functions.html whare you find all info you need on sha1 --> this is what you should us (not md5)

[soren]

Thank you for your reply and for welcoming me. I can see that I will be spending a lot of time here.

Well, it seems to work ok with sha1 and md5, but still not password(). All of this is not too big of a deal since I didn't plan on using password(). I just wanted to see it work.

However, I am working with a site that is using cPanel. They have MySQL version 4.0.22. Will sha1 and md5 still work? Can I still not worry about password()?

soren

[celestine]

yes. sha1 and md5 are php functions. will work with any MySQL versions, to the best of my knowledge.

[soren]

thanks! i appreciate the the fast reply.

soren

[raf]

They have MySQL version 4.0.22. Will sha1 and md5 still work? Can I still not worry about password()?
The MySQL sha1() and md5() will work in version 4.0.22

There are identical PHP functions that will work regardless of your db-version of format. I personnaly use the PHP function to keep my code db-independent. but there is no real diffenrence between

$hashed = sha1($_POST['pwd']); // the PHP function
$result = mysql_query("SELECT foo FROM bar WHERE user_pwd='". $hashed ."'");

and

$result = mysql_query("SELECT foo FROM bar WHERE user_pwd=sha1('". $_POST['pwd'] ."')"); // the MySQL function