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

jazzman1 · September 5, 2014

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";   
 }

Edited September 5, 2014 by jazzman1

Paul_Withers · September 5, 2014

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 · September 5, 2014

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.

Edited September 5, 2014 by jazzman1

Paul_Withers · September 6, 2014

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 · September 6, 2014

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 · September 6, 2014

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 · September 6, 2014

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 · September 6, 2014

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));

Edited September 6, 2014 by jazzman1

Paul_Withers · September 6, 2014

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 · September 6, 2014

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.

Edited September 6, 2014 by jazzman1

Paul_Withers · September 6, 2014

Is this right?

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

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

jazzman1 · September 6, 2014

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

Paul_Withers · September 6, 2014

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 · September 6, 2014

I have also set the Defaults to NULL too

jazzman1 · September 6, 2014

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.

Edited September 6, 2014 by jazzman1

Paul_Withers · September 6, 2014

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 · September 6, 2014

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???

Edited September 6, 2014 by jazzman1

Paul_Withers · September 6, 2014

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 · September 6, 2014

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 .... ?

Edited September 6, 2014 by jazzman1

Paul_Withers · September 6, 2014

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 myemail@gmail.com myemail@gmail.com Flat 11 Address Chelmsford Essex CM8 345 0 No no 2014-09-07 0 1

13 aquaman12 8 13105e3277f942d69788109936fb7274 Paul Withers myemail@gmail.com myemail@gmail.com Flat 11 Address Chelmsford Essex CM8 345 0 No no 2014-09-07 0 1

And I am using PHPMYADMIN

Edited September 6, 2014 by Paul_Withers

jazzman1 · September 6, 2014

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 · September 6, 2014

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 · September 6, 2014

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.

Edited September 6, 2014 by jazzman1

Paul_Withers · September 6, 2014

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');

}

?>

:confused:

Edited September 6, 2014 by Paul_Withers

jazzman1 · September 6, 2014

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

Edited September 6, 2014 by jazzman1

Sign In

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

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information