Jump to content

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


Paul_Withers
Go to solution Solved by Paul_Withers,

Recommended Posts

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 by jazzman1
Link to comment
Share on other sites

  • Replies 56
  • Created
  • Last Reply

Top Posters In This Topic

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 by jazzman1
Link to comment
Share on other sites

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

 

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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);
?>
 
 
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by jazzman1
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by jazzman1
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by jazzman1
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by jazzman1
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Paul_Withers
Link to comment
Share on other sites

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 by Paul_Withers
Link to comment
Share on other sites

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 by jazzman1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.