PHP MSQLI Forgot Password Reset page

Question

I am setting up a PHP lost password page for my website (www.qbstaxsubmission.co.uk) and the code for creating a lost password email which is sent to a user is working just fine. However when the user clicks on the email link he arrives at a new password php page. It's the script on this page which produces a error message 'Registration failure in updating recovery key: INSERT' which fires up my styled error page to transfer the user back to my standard login page.

So my problem is I cannot see what's wrong with my new password2.php. Can anyone help with this?

Here's the full new password2.php code:

<?php
ob_start();
include ('config.php');
include ('function.php');
$error_msg = "";

$token = $_GET['token'];
$userID = UserID($email);
$verifytoken = verifytoken($userID, $token);

// Sanitize and validate the data passed in
if (isset($_POST['submit'],$_POST['username'], $_POST['email'],       $_POST['p'])) {   
$email = filter_input(INPUT_POST, 'email', FILTER_SANITIZE_STRING);
$username = filter_input(INPUT_POST, 'username', FILTER_SANITIZE_STRING);   
$new_password = filter_input(INPUT_POST, 'new_password', FILTER_SANITIZE_STRING);
$retype_password = filter_input(INPUT_POST, 'retype_password', FILTER_SANITIZE_STRING);
$id = filter_input(INPUT_POST, 'id', FILTER_SANITIZE_STRING);}

$new_password = filter_input(INPUT_POST, 'p', FILTER_SANITIZE_STRING);
if (strlen($new_password) != 128) {
// The hashed pwd should be 128 characters long.
// If it's not, something really odd has happened
$error_msg .= '<p class="error">Invalid password configuration.</p>';
}

$prep_stmt = "SELECT id FROM members WHERE email = ? LIMIT 1";
$stmt = $db ->prepare($prep_stmt);    
if ($stmt) {
$stmt->bind_param('s', $email);
$stmt->execute();
$stmt->store_result();

if ($stmt->num_rows == 1) {
// A user with this email address already exists
$error_msg .= '<p class="error">A user with this email address already exists.</p>';
}
} else {
$error_msg .= '<p class="error">Database error</p>';
}

if($new_password != $retype_password) {
// Create a random salt
$salt = hash('sha512', uniqid(openssl_random_pseudo_bytes(16), TRUE));
// Create salted password 
$new_password = hash('sha512', $random_salt . $salt);

}

// Insert the new hashed password into the database
if ($insert_stmt = $db->prepare("UPDATE members SET password = ? WHERE id = ? ")) {
$insert_stmt->bind_param('si', $newpassword, $id);
// Execute the prepared query.
if (!$insert_stmt->execute()) {
header('Location: ../error.php?err=Database Registration failure: INSERT');
}

// Update recovery key      
if ($insert_stmt = $db->prepare("UPDATE recovery_keys SET valid = 0 WHERE  id = ?  AND token = ? ")); 
$insert_stmt->bind_param('is', $id, $token);
// Execute the prepared query.
if ($insert_stmt->execute())  
$msg = 'Your password has changed successfully. Please login with your new password.';

}else
{


header('Location: ../error.php?err=Registration failure in updating recovery key: INSERT'); }

{exit();}


?>

When the code above is run I get a blank page with the the correct token code shown in the site link.

This password2.php page has an include to a functions page which is shown below.

function checkUser($email)
{
global $db;

$query = mysqli_query($db, "SELECT id FROM members WHERE email = '$email'");

if(mysqli_num_rows($query) > 0)
{
return 'true';
}else
{
return 'false';    }
}

function id($email)
{
global $db;

$query = mysqli_query($db, "SELECT id FROM members WHERE email = '$email'");
$row = mysqli_fetch_assoc($query);

return $row['id'];
}


function generateRandomString($length = 25) {
// This function has taken from stackoverflow.com

$characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
$charactersLength = strlen($characters);
$randomString = '';
for ($i = 0; $i < $length; $i++) {
$randomString .= $characters[rand(0, $charactersLength - 1)];
}
return md5($randomString);
}

function send_mail($to, $token)
{
require 'PHPMailer/PHPMailerAutoload.php';
$mail = new PHPMailer;
//$mail->SMTPDebug = 3;     

$mail->isSMTP();  
$mail->Host = '';
$mail->SMTPAuth = true;
$mail->Username = '';
$mail->Password = '';
$mail->SMTPSecure = 'ssl';
$mail->Port = 465; 
$mail->SetFrom = '';
$mail->FromName = '';
$mail->addAddress($to);
$mail->addReplyTo('', 'Reply');
$mail->isHTML(true);    
$mail->Subject = 'Company Password Recovery Instruction';
$link = 'x.php?email='.$to.'&token='.$token;
$mail->Body  = "<b>Hi</b><br><br>You have just requested a new password for your company account with QBS Tax Submission. <a href='$link' target='_blank'>Click here</a> to reset your password. If you are unable to click the link then copy the hyper link below and paste into your browser to reset your password.<br><i>". $link."</i>";

$mail->AltBody = 'This is the body in plain text for non-HTML mail clients';
if(!$mail->send()) {
return 'fail';
} else {
return 'success';
}
}

function verifytoken($id, $token)
{   
global $db;
$query = mysqli_query($db, "SELECT valid FROM recovery_keys WHERE id = $id AND token = '$token'");
$row = mysqli_fetch_assoc($query);  
if(mysqli_num_rows($query) > 0)
{
if($row['valid'] == 1)
{
return 1;
}else
{
return 0;
}
}else
{
return 0;
}

}

function login($email, $password, $mysqli) {
// Using prepared statements means that SQL injection is not possible. 
if ($stmt = $mysqli->prepare("SELECT id, username, email, password, salt
              FROM members 
                              WHERE email = ? LIMIT 1")) {
$stmt->bind_param('s', $email);  // Bind "$email" to parameter.
$stmt->execute();    // Execute the prepared query.
$stmt->store_result();
// get variables from result.
$stmt->bind_result($id, $username, $db_password, $salt);
$stmt->fetch();
// hash the password with the unique salt.
$password = hash('sha512', $password . $salt);
if ($stmt->num_rows == 1) {
// If the user exists we check if the account is locked
// from too many login attempts 
if (checkbrute($id, $mysqli) == true) {
// Account is locked 
// Send an email to user saying their account is locked 
return false;
} else {
// Check if the password in the database matches 
// the password the user submitted.
if ($db_password == $password) {
// Password is correct!
// Get the user-agent string of the user.
$user_browser = $_SERVER['HTTP_USER_AGENT'];
// XSS protection as we might print this value
$id = preg_replace("/[^0-9]+/", "", $id);
$_SESSION['id'] = $id;
// XSS protection as we might print this value
$username = preg_replace("/[^a-zA-Z0-9_\-]+/", "", $username);
$_SESSION['username'] = $username;
$_SESSION['login_string'] = hash('sha512', $password . $user_browser);
// Login successful. 
return true;
} else {
// Password is not correct 
// We record this attempt in the database 
$now = time();
if (!$mysqli->query("INSERT INTO login_attempts(id, time) 
VALUES ('$id', '$now')")) {
header("Location: ../error.php?err=Database error: login_attempts");
exit();
}
return false;
}
}
} else {
// No user exists. 
return false;
}
} else {
// Could not create a prepared statement
header("Location: ../error.php?err=Database error: cannot prepare statement");
exit();
}
}
function checkbrute($id, $mysqli) {
// Get timestamp of current time 
$now = time();
// All login attempts are counted from the past 2 hours. 
$valid_attempts = $now - (2 * 60 * 60);
if ($stmt = $mysqli->prepare("SELECT time 
                              FROM login_attempts 
                              WHERE id = ? AND time > '$valid_attempts'")) {
$stmt->bind_param('i', $id);
// Execute the prepared query. 
$stmt->execute();
$stmt->store_result();
// If there have been more than 5 failed logins 
if ($stmt->num_rows > 5) {
return true;
} else {
return false;
}
} else {
// Could not create a prepared statement
header("Location: ../error.php?err=Database error: cannot prepare statement");
exit();
}
}
function login_check($mysqli) {
// Check if all session variables are set 
if (isset($_SESSION['id'], $_SESSION['username'],      $_SESSION['login_string'])) {
$id = $_SESSION['id'];
$login_string = $_SESSION['login_string'];
$username = $_SESSION['username'];
// Get the user-agent string of the user.
$user_browser = $_SERVER['HTTP_USER_AGENT'];
if ($stmt = $mysqli->prepare("SELECT password 
                  FROM members 
                  WHERE id = ? LIMIT 1")) {
// Bind "$id" to parameter. 
$stmt->bind_param('i', $id);
$stmt->execute();   // Execute the prepared query.
$stmt->store_result();
if ($stmt->num_rows == 1) {
// If the user exists get variables from result.
$stmt->bind_result($password);
$stmt->fetch();
$login_check = hash('sha512', $password . $user_browser);
if ($login_check == $login_string) {
// Logged In! 
return true;
} else {
// Not logged in 
return false;
}
} else {
// Not logged in 
return false;
}
} else {
// Could not prepare statement
header("Location: ../error.php?err=Database error: cannot prepare statement");
exit();
}
} else {
// Not logged in 
return false;
}
}
function esc_url($url) {
if ('' == $url) {
    return $url;
}
$url = preg_replace('|[^a-z0-9-~+_.?#=!&;,/:%@$\|*\'()\\x80-\\xff]|i', '', $url);

$strip = array('%0d', '%0a', '%0D', '%0A');
$url = (string) $url;

$count = 1;
while ($count) {
    $url = str_replace($strip, '', $url, $count);
}

$url = str_replace(';//', '://', $url);
$url = htmlentities($url);

$url = str_replace('&amp;', '&#038;', $url);
$url = str_replace("'", '&#039;', $url);
if ($url[0] !== '/') {
    // We're only interested in relative links from $_SERVER['PHP_SELF']
    return '';
} else {
    return $url;
}
}

as is `if ($insert_stmt = $db->prepare("UPDATE recovery_keys SET valid = 0 WHERE userID = $userID AND token ='$token'")); $insert_stmt->bind_param('ss', $userID, $token);` - check for errors, you have a few. http://php.net/manual/en/mysqli.error.php --- http://php.net/manual/en/function.error-reporting.php — Funk Forty Niner, Feb 21 '17 at 15:45
Don't roll your own hashing. Use [password_hash](http://php.net/manual/en/function.password-hash.php). It's much easier to manage — Machavity, Feb 21 '17 at 15:45
`if (strlen($password) != 128) {` Where is the variable `$password` set? — Chris Forrence, Feb 22 '17 at 13:45
Check http://php.net/manual/en/function.strlen.php to see how to use this — C Blyther, Feb 24 '17 at 13:22

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

I believ this is where the issue is:

    if (! $insert_stmt->execute())  
    $msg = 'Your password has changed successfully. Please login with your new password.';

}else{..}

if (! $insert_stmt->execute()) this means if the query fails echo that password fails.....

the reason your code always display Registration failure in updating recovery key: INSERT its because you instructed your code that when the query does not fail it must produce that.

And your code all of it is in mess, you need to clean it up.

Thi is how this should look.

 if ($insert_stmt = $db->prepare("UPDATE recovery_keys SET valid = 0 WHERE userID =  ?  AND token = ? ")); 
    $insert_stmt->bind_param('ss', $userID, $token);
    // Execute the prepared query.
    if ($insert_stmt->execute())  
    $msg = 'Your password has changed successfully. Please login with your new password.';
    
    }else
    {
    $msg = "Password doesn't match";
    
    header('Location: ../error.php?err=Registration failure in updating recovery key: INSERT'); }
    
    {exit();}

Edit

Tried to clean up some of the mess in your code. Now this how should look.

<?php
ob_start();
include('config.php');
include('function.php');
$error_msg = "";

$token       = $_GET['token'];
$userID      = UserID($email);
$verifytoken = verifytoken($userID, $token);

// Sanitize and validate the data passed in
if (isset($_POST['submit'], $_POST['username'], $_POST['email'], $_POST['p'])) {
    $email           = filter_input(INPUT_POST, 'email', FILTER_SANITIZE_STRING);
    $username        = filter_input(INPUT_POST, 'username', FILTER_SANITIZE_STRING);
    $new_password    = filter_input(INPUT_POST, 'new_password', FILTER_SANITIZE_STRING);
    $retype_password = filter_input(INPUT_POST, 'retype_password', FILTER_SANITIZE_STRING);
    $id              = filter_input(INPUT_POST, 'id', FILTER_SANITIZE_STRING);
}

$new_password = filter_input(INPUT_POST, 'p', FILTER_SANITIZE_STRING);
if (strlen($password) != 128) {
    // The hashed pwd should be 128 characters long.
    // If it's not, something really odd has happened
    $error_msg .= '<p class="error">Invalid password configuration.</p>';
}

$prep_stmt = "SELECT id FROM members WHERE email = ? LIMIT 1";
$stmt      = $db->prepare($prep_stmt);

if ($stmt) {
    $stmt->bind_param('s', $email);
    $stmt->execute();
    $stmt->store_result();
    
    if ($stmt->num_rows == 1) {
        // A user with this email address already exists
        $error_msg .= '<p class="error">A user with this email address already exists.</p>';
    }
} else {
    $error_msg .= '<p class="error">Database error</p>';
}

if ($new_password != $retype_password) {
    // Create a random salt
    $salt         = hash('sha512', uniqid(openssl_random_pseudo_bytes(16), TRUE));
    // Create salted password 
    $new_password = hash('sha512', $random_salt . $salt);
    
}

// Insert the new hashed password into the database
if ($insert_stmt = $db->prepare("UPDATE members SET password = ? WHERE id = ?")) {
    $insert_stmt->bind_param('si', $new_password, $userID);
    // Execute the prepared query.
    if (!$insert_stmt->execute()) {
        header('Location: ../error.php?err=Database Registration failure: INSERT');
        exit();
    }
    
    // Update recovery key      
    if ($insert_stmt = $db->prepare("UPDATE recovery_keys SET valid = 0 WHERE userID = ? AND token = ? "));
    $insert_stmt->bind_param('is', $userID, $token);
    // Execute the prepared query.
    if ($insert_stmt->execute())
        $msg = 'Your password has changed successfully. Please login with your new password.';
    
} else {
    $msg = "Password doesn't match";
    
    header('Location: ../error.php?err=Registration failure in updating recovery key: INSERT');
    exit();
}

?>

Important things you need to learn proper.

Update : here's the link

Prepared statements : link here

password hashing : link here

Your hashing method is very easy php does provide better and secure ways, please follow the links above.

I think `{exit();}` which is unneeded would produce an error. — Mikey, Feb 22 '17 at 13:47
@Mikey I could see an argument where it's misplaced (it should really go after each `header('Location: ...')` statement), but it won't cause an error. — Chris Forrence, Feb 22 '17 at 13:49
After changing to the suggested password2.php layout I end up with a completely blank page rather than an error message - Chris — C Blyther, Feb 22 '17 at 16:13

PHP MSQLI Forgot Password Reset page

1 Answers1