Retrieving Passwords

[AlGale]

Hi,

Very basic bit of MySQL querying for some of you experts here I guess. I am trying to retrieve an encrypted password from my database, so that I can then email it to the correct person, in the case that they have forgotten it. All is working fine, with the exception of getting the password itself. As I said, it has been encrypted, so I know I need to use the format password($password), but the syntax below must be slightly wrong:

$password = mysql_query( "select passwd = password('$password') from user
                        where username = '$username'");

I get sent a password of: Resource id #2

The query doesn`t fail, it just does not result in me getting sent the correct password. (Wonder if I could have used the word “password” more in one simple question?)

[tserbis]

1. Don't use PASSWORD(). Use ENCODE(), DECODE().
   2.
   $passwordResID = mysql_query("
   select DECODE(crypted_pwd, 'secretstring') from user
   where username = '$username'
   ");
   $passwordRes = mysql_fetch_row($passwordResID);
   $password = $passwordRes[0];

[AlGale]

Thanks for the reply. I presume I then replace (crypted_pwd, 'secretstring') with (passwd, '$password') in my case?

Also, I now have to change my registration part. It was:

$result = mysql_query("insert into user values
('$username', password('$password'), '$email')");

Do I now change that to:

$result = mysql_query("insert into user values
('$username', ENCODE('$password'), '$email')");

???

Many thanks for your help.

[tserbis]

define('SECRET_STRING', 'bla1bla2'); # never to change (length <= 32)

select uncrypted pwd

"select DECODE(passwd, '" . SECRET_STRING . "') from ... "

insert with crypting

$result = mysql_query("insert into user values
('$username', ENCODE('$password', '" . SECRET_STRING . "'), '$email')");

mysql field type

passwd varchar(32) binary

[AlGale]

Thanks. I THINK I get that. It is going to take a few changes to my coding. Can you explain the last line for me?

[tserbis]

http://www.mysql.com/doc/en/Miscellaneous_functions.html

ENCODE(str,pass_str)
Encrypt str using pass_str as the password. To decrypt the result, use DECODE(). The results is a binary string of the same length as string. If you want to save it in a column, use a BLOB column type (tserbis: or attribute BINARY for CHAR and VARCHAR :-).

[AlGale]

Sorry, I thought I was with you al the way up until that last bit. I have changed the insertion of a new user and password to use the ENCODE, and even without the

passwd varchar(32) binary

It works. Actually, it doesn`t work if I do put it in!

However the DECODE is not working. No error, but once again I am not geting the correct password emailed back. Is it because of the absence of

passwd varchar(32) binary

??? If so, is the syntax correct?

Thanks for your continuing help!

[tserbis]

1. Field for password must be declared using BINARY.
2. Use (all the time!!!) THE SAME second parameter for ENCODE and DECODE.
   3.

is the syntax correct?
Again, the syntax for ENCODE and DECODE you can check here
http://www.mysql.com/doc/en/Miscellaneous_functions.html .
Besides use echo mysql_error() for debugging.
4. Don't use your new "Forgot password"-system with old client password, inserted with PASSWORD().

[AlGale]

Yeah, I think I have all that right. But the syntax must be wrong on the decode side.

To ENCODE, I have:

insert with crypting

$result = mysql_query("insert into user values
('$username', ENCODE('$password', '" . SECRET_STRING . "'), '$email')");

And that works fine.

To DECODE, I have:

$passwordResID = mysql_query("DECODE(passwd, '" . SECRET_STRING . "') from user
where username = '$username'");

And that is email me rubbish. The SECRET_STRING has been defined once only.

Any ideas?

[tserbis]

1.
Have you access to MySQL console?
Try
SELECT DECODE(
ENCODE('AlGale', 'qwertyyyy'), 'qwertyyyy'
);
Is it AlGale result? :-)

2. What is the name of your second field in use? It must be passwd.

3.

$passwordResID = mysql_query("DECODE(passwd, '" . SECRET_STRING . "') from user
where username = '$username'");
What?!
It must be
$passwordResID = mysql_query("SELECT DECODE(passwd, '" . SECRET_STRING . "') from user
where username = '$username'");

[AlGale]

Hi,

Yeah, I completely understand that if you encode any word with a set string, then decode it with the same string, you should get your original word. That does seem logical.

Yes, my second field is passwd. I have no database errors (in fact, I have no actual errors)

You are right, I had forgotten to put SELECT in there. I have done that now, but still no change!

[tserbis]

1. Please (don't type!) Copy&Paste herethe lines with your mysql_query function with DECODE.
2. Insert after this lines
   echo mysql_error();
   and Copy&Paste here the result.

[AlGale]

Wait! Sorry, it was the absence of the SELECT. Just uploaded it again, and it does work. Thanks ever so much for your help, and your patience!!