Also, don't rely on using MySQL's password functions; the result of the MySQL PASSWORD() function is dependent on the server version, and it's due to be removed. I've used the Portable PHP password library from
http://www.openwall.com/phpass/ in the past, if depending on your PHP version, it may still be worth using, rather than the native functions in the latest PHP version.
Either way, it's pretty straightforward - in your database you store the hashed value, then on a login attempt, you retrieve the row from the database, and use the hash verification function to see if it's a match. In this example, $mydb is a previously set up mysqli database connection.
$verify = $mydb->stmt_init() ;
$validlogin = false ; // by default, don't let people in
if ( $verify->prepare("SELECT hash, userid, otherstuff FROM users WHERE username = ?") ) {
$verify->bind_param('s',$_REQUEST['username']) ;
$verify->execute() ;
$verify->store_result() ;
if ( $verify->num_rows == 1 ) {
// we have found a row matching the user name
$verify->bind_result($hash,$userid,$otherstuff) ;
$verify->fetch() ;
$validlogin = password_verify($_REQUEST['password'],$hash) ;
// if using the phpass library this would be instead
// $hasher = new PasswordHash(8, false ) ;
// $validlogin = $hasher->CheckPassword($_REQUEST['password'],$hash) ;
}
}
if ( $validlogin ) {
// handle success here
} else {
// handle failure
}
Also, for anyone wanting to think about login and security, I really think it's well worth reading this Troy Hunt post, which explains issues around password resets, username enumeration, and so on. Depending on the nature of your site/app, you may want to take this very seriously.
https://www.troyhunt.com/everything-you-ever-wanted-to-know/
A simple example: if your site/service/app involves personal information - dating, sexual health, job hunting, debt advice - do you want people (partners, employers) to be able to find out if someone has an account on it? In some situations, this could be catastrophic to reveal. Yet if you display a different message when someone enters a valid username and an incorrect password, compared to an invalid username, or your password reset system can be used to confirm the existence of an account, that's what you're doing.
Saying "We've sent a password reset email to
xyz@abc.com" pretty much confirms someone has an account with you, especially if it says "We can't find an account with that email" for a different address. Saying "We've checked our records. If you have an account, lookout for our password reset email" regardless reveals nothing.