Not finding entry in SQL database - help please

[jancooper]

Hi

 

I'm very much a newbie to PHP and am struggling with a registration and login problem.

 

The registration part is fine - the password is emailed out fine, I check the database and the entry is there, the password is encrypted. but when the user tries to log in....

 

if (mysql_num_rows($result) == 0) .... it returns 0? and the access denied page is shown.

 

I'm thinking it is something to do with the sql database encryption? 

 

I'm completely lost here and would appreciate some guidance

 

Thanks in advance

Jan

[denno020]

You're going to have to specify what sort of encryption you're using.

 

Are you encrypting the password as you insert it into the database?

 

I haven't had much experience with encrypted data (using something likd md5), so I don't know how you're supposed to read it back out... But I'm sure other people will want to know the above to be able to answer your question.

 

Denno

[jancooper]

Hi Denno - thanks for responding.

 

I'm using this to automatically create the password:

 

$newpass = substr(md5(time()),0,6);

 

- it sends an email to the user with a password like this:

 

userid: fred

password: cf0c2b

 

and when I look in the sql database it has saved this: 10 fred *624E362173B0745 fred bloggs jan-c@o2.co.uk 

 

I'm completely lost and very new to all this - I think the database encrypts automatically and I don't know how to turn it off.

 

I used 'Managing Users with PHP Sessions and MySQL' as a guide for these functions and followed it to the letter, the link is here http://articles.sitepoint.com/article/users-php-sessions-mysql.

 

I don't think I need the encryption but I don't know how to turn it off in the database - or whether that is where the problem lies?

 

Jan

 

 

 

 

 

 

 

[revraz]

Frist off, md5 is a hash and not encryption.

Second, why are you only reading in the first 6 chars of the md5 hash?

Third, post the code where you write the hash to the pw field in the database.

[jancooper]

Hi Revaz

 

Yes, I the encryption seems to happen as it goes into the database.

It's just shortened to make it less cumbersome I guess.

I hope this helps

 

    $newpass = substr(md5(time()),0,6);

   

    $sql = "INSERT INTO user SET

              userid = '$_POST[newid]',

              password = $newpass, 

              fullname = '$_POST[newname]',

              email = '$_POST[newemail]',

              notes = '$_POST[newnotes]'";

    if (!mysql_query($sql))

        error('A database error occurred in processing your '.

              'submission.\\nIf this error persists, please '.

              'contact jan-c@o2.co.uk.\\n' . mysql_error());

             

    // Email the new password to the person.

    $message = "Hi

 

Your personal account for the Web Site

has been created! To log in, proceed to the

following address:

 

    http://www.avisiweb.co.uk

 

Your personal login ID and password are as

follows:

 

    userid: $_POST[newid]

    password: $newpass 

 

 

thanks

Jan

[jancooper]

Sorry,

 

I've just put the code back to what it was it should read:

 

 

  $newpass = substr(md5(time()),0,6);

   

    $sql = "INSERT INTO user SET

              userid = '$_POST[newid]',

              password = PASSWORD('$newpass'), 

              fullname = '$_POST[newname]',

              email = '$_POST[newemail]',

              notes = '$_POST[newnotes]'";

    if (!mysql_query($sql))

        error('A database error occurred in processing your '.

              'submission.\\nIf this error persists, please '.

              'contact jan-c@o2.co.uk.\\n' . mysql_error());

             

    // Email the new password to the person.

    $message = "Hi

 

Your personal account for the Web Site

has been created! To log in, proceed to the

following address:

 

    http://www.avisiweb.co.uk

 

Your personal login ID and password are as

follows:

 

    userid: $_POST[newid]

    password: $newpass 

 

You aren't stuck with this password! Your can

change it at any time after you have logged in.

 

If you have any problems, feel free to contact me at

<jan-c@o2.co.uk>.

 

-Your Name

Your Site Webmaster

";

 

    mail($_POST['newemail'],"Your Password for the Project Website",

        $message, "From:Your Name <jan-c@o2.co.uk>");

       

thanks

Jan

[PFMaBiSmAd]

password = PASSWORD('$newpass'), 

 

^^^ The mysql PASSWORD() function should NOT be used by applications to hash your user passwords.

 

I'm sorry, but the tutorial link you found and are trying to use is out of date and won't work as is on current versions of mysql.

 

If you alter the the user table password column so that it is VARCHAR(32) and change any use of the mysql PASSWORD() function to the mysql MD5() function, the code may work, barring any other problems in it.

 

You will need to delete and re-register any users/passwords after you make the above change.

[jancooper]

Thanks for the information,

 

As I'm a complete newbie could you please advise how I should amend this, presumably I can keep the md5 hash just to create the password

 

$newpass = substr(md5(time()),0,6);

 

and change the password line to this?

   

    $sql = "INSERT INTO user SET

              userid = '$_POST[newid]',

              password = '$newpass', 

              fullname = '$_POST[newname]',

              email = '$_POST[newemail]',

              notes = '$_POST[newnotes]'";

    if (!mysql_query($sql))

        error('A database error occurred in processing your '.

              'submission.\\nIf this error persists, please '.

              'contact jan-c@o2.co.uk.\\n' . mysql_error());

 

If this is not right then please advise.

 

Thanks in advance

Jan

[PFMaBiSmAd]

$newpass = substr(md5(time()),0,6);

 

^^^ That line of code is only producing the random 6 character user password. It has nothing to do with the problem or the hashed value being stored in the user table or the code trying to match the entered password with the stored hashed value.

 

My post above told you what you need to alter with the column definition and in the code-

If you alter the the user table password column so that it is VARCHAR(32) and change any use of the mysql PASSWORD() function to the mysql MD5() function, the code may work, barring any other problems in it.

 

You will need to delete and re-register any users/passwords after you make the above change.

.

[jancooper]

Sorry to be such a numpty - I can, and will change the user table password column so that it is VARCHAR(32)

 

but I didn't understand what you meant by "change any use of the mysql PASSWORD() function to the mysql MD5() function" because I can't see that they are connected.

 

The MD5() is only used in the hash as far as I can determine, once that is done, it's done.

 

If the PASSWORD() function is what is encrypting it in the user table and possibly causing the problem, then how would I write the code to simply write the password to the user table as is without any encryption? 

 

 

[BlueSkyIS]

do not store the password encrypted or un-encrypted. store as an md5/hash. when the user visits the site and enters their password, md5/hash the value they enter and compare the 2 hashes to determine if there is a match. long story short: there should be no way to retrieve the actual password from the database. if the user forgets their password, allow them to create a new one or create a new one for them. do NOT expect to retrieve their password and give it to them.

[beegro]

Conversations about user authentication systems and how to hash credentials can go on forever.  Here's a brief summary as I understand it.

 

PFMaBiSmAd is saying to simply use the MD5() function in MySQL to hash the password you create.  However you will also need to use the MD5() function when you SELECT from the table as well. 

 

Your INSERT

 

   $newpass = substr(md5(time()),0,6);
   
    $sql = "INSERT INTO user SET
              userid = '$_POST[newid]',
              password = MD5($newpass), 
              fullname = '$_POST[newname]',
              email = '$_POST[newemail]',
              notes = '$_POST[newnotes]'";
    if (!mysql_query($sql))
        error('A database error occurred in processing your '.
              'submission.\\nIf this error persists, please '.
              'contact jan-c@o2.co.uk.\\n' . mysql_error());

 

Your SELECT (for retrieving user information) assuming the information comes from a POSTed form

 

$entered_password = $_POST['password_field'];
$user_id = $_POST['user_id_field'];
$sql = "SELECT * FROM user  WHERE userid = '$user_id'  AND password = MD5($entered_password)";
$res = mysql_query($sql);
$row = mysql_fetch_row($res);
if ($row != FALSE) {
    // $row should have the user info
}
else {
    // no user info was found for that user+password combination
}

 

One other thing to note is that you should probably clean any GET, POST, COOKIE information before inserting it into a query with mysql_real_escape_string() to help prevent SQL injection.  See http://us2.php.net/manual/en/function.mysql-real-escape-string.php

[jancooper]

Thank you chaps

 

It all starts to make sense ..... and even better, it's actually working now thanks to you.

 

 

Jan