Query about how to retrieve a password from the database and compare to the one the user has entered

[jazzman1]

Here's a pretty simple example how to insert and retrieve a password hashing string using md5() alg.

 

1. My database looks like this:

 

 

mysql> SHOW COLUMNS FROM users;
+---------------+-------------------------+------+-----+---------+----------------+
| Field         | Type                    | Null | Key | Default | Extra          |
+---------------+-------------------------+------+-----+---------+----------------+
| user_id       | int(10) unsigned        | NO   | PRI | NULL    | auto_increment |
| password      | char(32)                | NO   |     | NULL    |                |
| salt_password | char(32)                | NO   |     | NULL    |                |
+---------------+-------------------------+------+-----+---------+----------------+

 

2. then, the php script should be similar like this

<?php

 $_POST['password'] = 'myPassWord'; 
 
 $salt = md5(uniqid(rand(), true));
 
 $pass = md5($_POST['password'].$salt,true); 

 // insert statement 
 $query = "INSERT INTO users(password, salt_password) VALUES ('$pass','$salt')";
 
 // retrieve the data and comparing the salted pass
 
 $query = "SELECT password, salt_password FROM users WHERE user_id = $userID";
 
 $result = mysql_query($query);
 
 $row = mysql_fetch_assoc($result); 
 
 if($row['password'] == md5($_POST['password'].$row['salt'])) {
   echo "passwords match";  
 } else {
   echo "passwords failed";   
 }

[Paul_Withers]

Thank you Jazzman1, sorry for the late reply, I had just had enough yesterday lol.

 

What you have provided has been very helpful and is much appreciated. I have now updated all the relevant files and it all works great.

 

Many thanks for your help

[jazzman1]

Because, I've created this on-the-fly codding yesterday, tomorrow I saw some errors. However, the purpose was to get the principle. 

 

The second parameter of md5() hash is optional providing a boolean value and can be set to false or omitted to return 32-character hexadecimal number rather than to true as I did. 

$salt = md5(uniqid(rand(), true));

$pass = md5($_POST['password'].$salt,true); 

// to 

$salt = md5(uniqid(rand(), true),false);

$pass = md5($_POST['password'].$salt,false); 

also, the column's name in the users table is named salt_password, not salt.

if($row['password'] == md5($_POST['password'].$row['salt']))

// to 

if($row['password'] == md5($_POST['password'].$row['salt_password']))

BTW: Jacques1 is right. You should avoid using md5() hashing in production environment.

[Paul_Withers]

Hi Jazzman, I have successfully registered the user with the encrypted password and the user can also change their password which is also encrypted. However when the user logs in, I get the following errors

 

 

 

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /Applications/XAMPP/xamppfiles/htdocs/login.php on line 40

Notice: Undefined variable: row in /Applications/XAMPP/xamppfiles/htdocs/login.php on line 43

Notice: Undefined variable: row in /Applications/XAMPP/xamppfiles/htdocs/login.php on line 43

There was a problem with your login. It Could be the Wrong Username or Password

 

I have tried to decrypt it using the method you demonstrated, but I think my coding is wrong. Here is what I have

 

<?php
session_start();  //this will start the session for the user

error_reporting(E_ALL);
ini_set('display_errors', '1');

require( 'database.php' );

if ( isset( $_POST['login']) ) {
       loginUser($con);
   } else {

include('includes/overall/header.php');
?>
    <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
    <ul>
    	<li>Username:<br>
    		<input type="text" name="username">
    	</li>
   		<li>Password:<br> 
   		<input type="password" name="password">
   		</li>
   		<li><input type="submit" name="login" value="Login">
  </form>
 <?php 
 
 include('includes/overall/footer.php');
 }

function loginUser($con) {
   $username = mysqli_real_escape_string($con, $_POST['username']);
  	$salt = md5(uniqid(rand(), true),false);
	$pass = md5($_POST['password'].$salt,false); 
   //echo "$username - $password";

   $query = "SELECT password, salt_password FROM users WHERE username = $username";
 
 $result = mysqli_query($con, $query);
 
 $row = mysqli_fetch_assoc($result);
}

if($row['password'] == md5($_POST['password'].$row['salt_password'])) {    
  
   if ($row) {
   	//echo "Reached Matching Row<br/>";

       $_SESSION['loggedin'] = 1;
       $_SESSION['loggedinuser'] = $row['username']; 

   	   if($row['level'] == '1'){  //level 1 user (Normal User)
   	   	//echo "Reached level 1 check<br/>";
       $_SESSION['adminuser'] = 0;  //not an admin set value to false/0
       header ('location: main.php');
       } 
       elseif ($row['level'] == '9'){  //level 9 user (Admin User)
       	//echo "Reached level 9 check<br/>";
       $_SESSION['adminuser'] = 1; //yes is an admin set value to true/1
       header ('location: main.php');
       }
          	
   	  
   }
   mysqli_free_result($result);
}
// decide what to do if the login is unsuccessful
else{
include('includes/overall/header.php');
echo "There was a problem with your login. It Could be the Wrong Username or Password";
include('includes/overall/footer.php');
}

 // end function

/* close connection */
mysqli_close($con);
?>

Can you tell me where I have gone wrong please?

 

Many Thanks

 

 

[Paul_Withers]

Hi I have changed the function to the following but get the following errors

 

 

 

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /Applications/XAMPP/xamppfiles/htdocs/login.php on line 39

Notice: Undefined variable: row in /Applications/XAMPP/xamppfiles/htdocs/login.php on line 42

Notice: Undefined variable: row in /Applications/XAMPP/xamppfiles/htdocs/login.php on line 42

 

 

 

here is the code

function loginUser($con) {
   $username = mysqli_real_escape_string($con, $_POST['username']);
  	 
   //echo "$username - $password";

   $query = "SELECT password, salt_password FROM users WHERE username = $username";
 
 $result = mysqli_query($con, $query);
 
 $row = mysqli_fetch_assoc($result);
}

if($row['password'] == md5($_POST['password'].$row['salt_password'])) {    
  
   if ($row) {
   	//echo "Reached Matching Row<br/>";

       $_SESSION['loggedin'] = 1;
       $_SESSION['loggedinuser'] = $row['username']; 

   	   if($row['level'] == '1'){  //level 1 user (Normal User)
   	   	//echo "Reached level 1 check<br/>";
       $_SESSION['adminuser'] = 0;  //not an admin set value to false/0
       header ('location: main.php');
       } 
       elseif ($row['level'] == '9'){  //level 9 user (Admin User)
       	//echo "Reached level 9 check<br/>";
       $_SESSION['adminuser'] = 1; //yes is an admin set value to true/1
       header ('location: main.php');
       }
          	
   	  
   }
   mysqli_free_result($result);
}
// decide what to do if the login is unsuccessful
else{
include('includes/overall/header.php');
echo "There was a problem with your login. It Could be the Wrong Username or Password";
include('includes/overall/footer.php');
}

 // end function

/* close connection */
mysqli_close($con);
?>

any help is much appreciated

[jazzman1]

it's a beautiful day today in Toronto

 

So, do you know how the programming functions work?

 

Try, this and tell me what result you get:

<?php

session_start();  //this will start the session for the user

error_reporting(E_ALL);

ini_set('display_errors', '1');

require( 'database.php' );

if ( isset( $_POST['login']) ) {
       loginUser($con,$_POST['username'],$_POST['password']);
   } else {

include('includes/overall/header.php');
?>
    <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
    <ul>
    	<li>Username:<br>
    		<input type="text" name="username">
    	</li>
   		<li>Password:<br> 
   		<input type="password" name="password">
   		</li>
   		<li><input type="submit" name="login" value="Login">
  </form>
 <?php 
 
 include('includes/overall/footer.php');
 }

function loginUser($con, $name, $pass) {
    
   $query = sprintf("SELECT password, salt_password FROM users WHERE username ='%s'",
   mysqli_real_escape_string($name));
 
  $result = mysqli_query($con, $query);
  
  if(!$result) {
  echo "Error Result: ".mysqli_error($con);    
  }
  
  $row = mysqli_fetch_assoc($result);
  
  if($row['password'] == md5($pass.$row['salt_password'])) {
      
     echo "passwords match";
     // do 
  } else {
      
    echo "passwords failed";  
    
  }
}

/*
if($row['password'] == md5($_POST['password'].$row['salt_password'])) {    
  
   if ($row) {
   	//echo "Reached Matching Row<br/>";

       $_SESSION['loggedin'] = 1;
       $_SESSION['loggedinuser'] = $row['username']; 

   	   if($row['level'] == '1'){  //level 1 user (Normal User)
   	   	//echo "Reached level 1 check<br/>";
       $_SESSION['adminuser'] = 0;  //not an admin set value to false/0
       header ('location: main.php');
       } 
       elseif ($row['level'] == '9'){  //level 9 user (Admin User)
       	//echo "Reached level 9 check<br/>";
       $_SESSION['adminuser'] = 1; //yes is an admin set value to true/1
       header ('location: main.php');
       }
          	
   	  
   }
   mysqli_free_result($result);
}
// decide what to do if the login is unsuccessful
else{
include('includes/overall/header.php');
echo "There was a problem with your login. It Could be the Wrong Username or Password";
include('includes/overall/footer.php');
}

 // end function

/* close connection
 * 
 * 
 */

mysqli_close($con);
?>
 
 

[Paul_Withers]

Hi Jazzman, night time here now, but very cloudy here today

 

ok I had to change

 $query = sprintf("SELECT password, salt_password FROM users WHERE username ='%s'",
   mysqli_real_escape_string($name));
 

to

$query = sprintf("SELECT password, salt_password FROM user WHERE username ='%s'",
   mysqli_real_escape_string($con, $name));

but I get the 

 

passwords failed

 

message even though the password is correct.

[jazzman1]

Ok, add on line 44 var_dump() to your loginUser function.

function loginUser($con, $name, $pass) {
$query = sprintf("SELECT password, salt_password FROM users WHERE username ='%s'",
mysqli_real_escape_string($name));
$result = mysqli_query($con, $query);
if(!$result) {
echo "Error Result: ".mysqli_error($con);
}
$row = mysqli_fetch_assoc($result);
var_dump($row); // here add this
if($row['password'] == md5($pass.$row['salt_password'])) {
echo "passwords match";
// do
} else {
echo "passwords failed";
}
}

Is there a column named username in your users table?

 

Use my query!

 $query = sprintf("SELECT password, salt_password FROM users WHERE username ='%s'",
   mysqli_real_escape_string($name));

[Paul_Withers]

Great (kind of lol), although

$query = sprintf("SELECT password, salt_password FROM user WHERE username ='%s'",
mysqli_real_escape_string($con, $name));

had to be written this was as the table is called user and not user. Also the $con had to be added otherwise the script will not run.

 

Now the bad part

 

The script returns

 

 

 

array(2) { ["password"]=> string(2) "yÅ" ["salt_password"]=> string(32) "70414af5a489e7abab0ea1f534ac98cb" } passwords failed

 

However both the password and salt_password  match those in the database?

 

Im so confused lol

[jazzman1]

Are you using not ascii characters for password? Then you could set a utf8_general_ci as database collation, also your mysqli driver should be also using utf8 charset link!

 

Just for test, try using an English alphabet word for your password.

[Paul_Withers]

Is this right?

if($row['password'] == md5($pass.$row['salt_password'])) {

where does it take in the $_POST['password']; 

[jazzman1]

Yes, that's correct. You need to carefully re-read my reply #26 

[Paul_Withers]

Ok, I have changed the Collation so the database reads

 

password char(32) utf8_general_ci

salt_password char(32) utf8_general_ci

 

Sorry dumb question but how to I use ascii characters for password?

 

Also 

 

your mysqli driver should be also using utf8 charset link!

 

I am using the newest version of XXAMP is there a setting i need to change?

 

Thanks

[Paul_Withers]

I have also set the Defaults to NULL too

[jazzman1]

See the link - http://php.net/manual/en/mysqli.set-charset.php

 

Example:

<?php

$con = mysqli_connect('localhost', 'my_user', 'my_password', 'test');

mysqli_set_charset($con, "utf8");

What's ascii characters - http://www.asciitable.com/

 

 

Ok, I have changed the Collation so the database reads

 

password char(32) utf8_general_ci

salt_password char(32) utf8_general_ci

 

After that, delete all old records, create a new one and try to retrieve the correct hashed/salted password.

[Paul_Withers]

Ok done that, but just realised, the string returned is

 

array(2) { ["password"]=> string(3) "yÃ…" ["salt_password"]=> string(32) "70414af5a489e7abab0ea1f534ac98cb" } passwords failed

 

but in the database it reads

password as "yÃ"

 

I guess this is what is causing the error but no idea how to fix it.

 

BTW, your help is much appreciated, and is helping me progress on other parts of the site too. So I am ver grateful for your time, help and patience.

 

Paul

[jazzman1]

Run the following sql statement via your GUI db tool, mysql workbench, phpmyadmin or whatever you use and bring me the result back.

 

SHOW FULL COLUMNS FROM db_name.users;

 

I don't know your database name.

 

Most important - you should have to use the same database credentials and charset encoding when inserting the data to mysql. Maybe you're using different one???

[Paul_Withers]

Ok, I done as you followed,

 

database.php

<?php


$con = mysqli_connect("localhost","root","","equatics_lr") 
or die("<p>The database server is not available.</p>");

mysqli_set_charset($con, "utf8");
?>

And deleted and re registered a couple of users, but I get the same message on all of them even though the returned data matches the database

[jazzman1]

Show me the output of var_dump as in the example above!

 

And the output of: SHOW FULL COLUMNS FROM equatics_lr.users;

 

What GUI db tool are you using? PHPMYADMIN or .... ?

[Paul_Withers]

ok the VAR_DUMP reads

 

 

 

array(2) { ["password"]=> string(1) "8" ["salt_password"]=> string(32) "13105e3277f942d69788109936fb7274" } passwords failed

 

the output of SHOW FULL COLUMNS FROM equatics_lr.user;

 

 

 

SQL result

Host: localhost
Database: equatics_lr
Generation Time: Sep 07, 2014 at 12:20 AM
Generated by: phpMyAdmin 4.1.12 / MySQL 5.6.16
SQL query: SELECT * FROM `user` LIMIT 0, 25 ;
Rows: 3

 

id username password salt_password firstname lastname email email_again address_1 address_2 town county postcode country paypal_email business vat_registered vat_number registered last_login level

11 aquaman 3 1451d58ec850a02daa82361d0e82b04b Paul Withers [email protected] [email protected] Flat 11 Address Chelmsford Essex CM8 345     0 No no 2014-09-07 0 1                                          

13 aquaman12 8 13105e3277f942d69788109936fb7274 Paul Withers [email protected] [email protected] Flat 11 Address Chelmsford Essex CM8 345     0 No no 2014-09-07 0 1

 

 

And I am using PHPMYADMIN

[jazzman1]

So, your password it's not hashed and salted. Why? It's just a plain number #8. Re-read again my reply #26 and show me the hashing script before to insert the data to your database.

[Paul_Withers]

Ok, my error was using a test registration page by accident

 

It now reads

 

 

 

array(2) { ["password"]=> string(4) "n3Sv" ["salt_password"]=> string(32) "d66bcdd4b473b910f4e0fe7e43de5ef6" } passwords failed

 

Although this still matches the database

[jazzman1]

Hm...tell me the truth, what you don't understand in my example #26? You're not hashing and salting the user pass at all, that's why you get a false result every time.

[Paul_Withers]

Ok, the script I got to register the user is here. Maybe I am missing something here:

<?php

 

error_reporting(E_ALL);

ini_set('display_errors', '1');

 

// some error checking

/*

if($_POST['reg']){

echo "form submitted";

}else{ echo "form not submitted"; }

*/

 

if (isset($_POST['user'], $_POST['pass'], $_POST['pass_again'], $_POST['firstname'], $_POST['lastname'], $_POST['email'], $_POST['email_again'], $_POST['address_1'], $_POST['address_2'], $_POST['town'], $_POST['county'], $_POST['postcode'], $_POST['country'], $_POST['paypal_email'], $_POST['business'], $_POST['vat_registered'], $_POST['vat_number'] ) ) {

    // echo $_POST['user']." - ".$_POST['pass']." - ".$_POST['email'];

     

    if( strlen( $_POST['user'] ) < 5 )

    {

        include('includes/overall/header.php');

        echo "Username Must Be 5 or More Characters.";

        include('includes/overall/footer.php');

    }

    elseif( strlen( $_POST['pass'] ) < 5 )

    {

        include('includes/overall/header.php');

        echo "Password Must Be 5 or More Characters.";

        include('includes/overall/footer.php');

    }

    elseif( $_POST['pass'] == $_POST['user'] )

    {

        include('includes/overall/header.php');

        echo "Username And Password Can Not Be The Same.";

        include('includes/overall/footer.php');

    }

    elseif( $_POST['pass_again'] == "" )

    {

        

        include('includes/overall/header.php');

        echo "Passwords must match";

        include('includes/overall/footer.php');

    }

    

// CREATE  BETTER EMAIL CHECK

 

  elseif( $_POST['email'] == "" )

    {

        

        include('includes/overall/header.php');

        echo "Email must be valid.";

        include('includes/overall/footer.php');

    }

    elseif( $_POST['email_again'] == "" )

    {

        

        include('includes/overall/header.php');

        echo "Emails must match.";

        include('includes/overall/footer.php');

    }

    elseif( $_POST['address_1'] == "" )

    {

        

        include('includes/overall/header.php');

        echo "Address cannot be empty";

        include('includes/overall/footer.php');

    }

    elseif( $_POST['address_2'] == "" )

    {

        

        include('includes/overall/header.php');

        echo "Address cannot be empty";

        include('includes/overall/footer.php');

    }

     elseif( $_POST['town'] == "" )

    {

        

        include('includes/overall/header.php');

        echo "Town cannot be empty";

        include('includes/overall/footer.php');

    }

     elseif( $_POST['county'] == "" )

    {

        

        include('includes/overall/header.php');

        echo "County cannot be empty";

        include('includes/overall/footer.php');

    }

     elseif( $_POST['postcode'] == "" )

    {

        

        include('includes/overall/header.php');

        echo "Postcode cannot be empty";

        include('includes/overall/footer.php');

    }

     elseif( $_POST['business'] == "" )

    {

        

        include('includes/overall/header.php');

        echo "Business cannot be empty";

        include('includes/overall/footer.php');

    }

     elseif( $_POST['vat_registered'] == "" )

    {

        

        include('includes/overall/header.php');

        echo "VAT Registered cannot be empty";

        include('includes/overall/footer.php');

    }

     elseif( $_POST['vat_number'] == "" )

    {

        

        include('includes/overall/header.php');

        echo "VAT number cannot be empty, please enter N/A if not VAT registered.";

        include('includes/overall/footer.php');

    }

    else

    {

   

        require( 'database.php' );

 

        $username = mysqli_real_escape_string($con, $_POST['user']);

 

        //Remove md5() function if not using encryption i.e. $password = $_POST['pass'];

        $password = mysqli_real_escape_string($con, md5( $_POST['pass']));

 

$salt = md5(uniqid(rand(), true));

 

$pass = md5($_POST['pass'].$salt,true);

 

        $password_again = mysqli_real_escape_string($con, md5( $_POST['pass_again']));

 

        $firstname = mysqli_real_escape_string($con, $_POST['firstname']);

        $lastname = mysqli_real_escape_string($con, $_POST['lastname']);

        

        $email = mysqli_real_escape_string($con, $_POST['email'] );

$email_again = mysqli_real_escape_string($con, $_POST['email_again']);

 

        $address_1 = mysqli_real_escape_string($con, $_POST['address_1']);

        $address_2 = mysqli_real_escape_string($con, $_POST['address_2']);

        $town = mysqli_real_escape_string($con, $_POST['town']);

        $county = mysqli_real_escape_string($con, $_POST['county']);

        $postcode = mysqli_real_escape_string($con, $_POST['postcode']);

        $business = mysqli_real_escape_string($con, $_POST['business']);

        $vat_registered = mysqli_real_escape_string($con, $_POST['vat_registered']);

        $vat_number = mysqli_real_escape_string($con, $_POST['vat_number']);

        $registered = date("Y-m-d");

 

                        

        $sqlCheckForDuplicate = "SELECT username FROM user WHERE username = '". $username ."'";

         //echo "$sqlCheckForDuplicate<br/>";

 

        $result = mysqli_query($con, $sqlCheckForDuplicate);

 

        if(mysqli_num_rows($result) == 0){

       

        //echo "No Duplicates<br/>";

 

        $sqlRegUser = "INSERT INTO user( username, password, salt_password, firstname, lastname, email, email_again, address_1, address_2, town, county, postcode, business, vat_registered, vat_number, registered ) VALUES (

                        '". $username ."',

                        '". $pass ."',

                        '". $salt ."',

                        '". $firstname ."',

                        '". $lastname ."',

                        '". $email ."',

                        '". $email_again ."',

                        '". $address_1 ."',

                        '". $address_2 ."',

                        '". $town ."',

                        '". $county ."',

                        '". $postcode ."',

                        '". $business ."',

                        '". $vat_registered ."',

                        '". $vat_number ."',

                        '". $registered. "'

                        )";

          //echo "$sqlRegUser<br/>";

          

        if( !mysqli_query($con, $sqlRegUser ) )

            {

            printf("Errormessage: %s\n", mysqli_error($con));

                include('includes/overall/header.php');

                echo "You Could Not Register Because Of An Unexpected Error.";

                include('includes/overall/footer.php');

            }

            

            else

            {

/* Note: When using the header function, you cannot send output to the browser

 * before the header function is called. IF you want to echo a message to the

 * user before going back to your login page then you should use the HTML

 * Meta Refresh tag. */

 

 //echo "You Are Registered And Can Now Login";

 //echo " $username";  //this is for error checking

 

   header ('location: login.php');

 

// if using echo then use meta refresh

/*

 *?>

 *<meta http-equiv="refresh" content="2;url= login.php/">

 *<? 

 */

 

            }

        mysqli_free_result($result);  

                    

        }

        else

        {

            include('header.inc');

            echo "The Username You Have Chosen Is Already Being Used By Another User. Please Try Another One.";

            //echo " $username;"  //this is for error checking

            include('footer.inc');

        }

            /* close connection */

            mysqli_close($con);                  

        }      

    }

else

{

    include('header.inc');

    echo "You Could Not Be Registered Because Of Missing Data.";

    include('footer.inc');

}

?>

 

[jazzman1]

Change those two lines:

$salt = md5(uniqid(rand(), true));
 
$pass = md5($_POST['pass'].$salt,true);


// to 

$salt = md5(uniqid(rand(), true),false);
 
$pass = md5($_POST['pass'].$salt,false);

I don't understand these variables too:

$password = mysqli_real_escape_string($con, md5( $_POST['pass']));

$password_again = mysqli_real_escape_string($con, md5( $_POST['pass_again']));
    

Just comment them out for now.

 

This database table structure should be normalized! But this is a different story