title: PHP MSQLI Forgot Password Reset page
body: <p>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.</p>
  
  <p>So my problem is I cannot see what's wrong with my new password2.php. Can anyone help with this?</p>
  
  <p>Here's the full new password2.php code:</p>
  
  <pre><code>&lt;?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 .= '&lt;p class="error"&gt;Invalid password configuration.&lt;/p&gt;';
  }
  
  $prep_stmt = "SELECT id FROM members WHERE email = ? LIMIT 1";
  $stmt = $db -&gt;prepare($prep_stmt);    
  if ($stmt) {
  $stmt-&gt;bind_param('s', $email);
  $stmt-&gt;execute();
  $stmt-&gt;store_result();
  
  if ($stmt-&gt;num_rows == 1) {
  // A user with this email address already exists
  $error_msg .= '&lt;p class="error"&gt;A user with this email address already exists.&lt;/p&gt;';
  }
  } else {
  $error_msg .= '&lt;p class="error"&gt;Database error&lt;/p&gt;';
  }
  
  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-&gt;prepare("UPDATE members SET password = ? WHERE id = ? ")) {
  $insert_stmt-&gt;bind_param('si', $newpassword, $id);
  // Execute the prepared query.
  if (!$insert_stmt-&gt;execute()) {
  header('Location: ../error.php?err=Database Registration failure: INSERT');
  }
  
  // Update recovery key      
  if ($insert_stmt = $db-&gt;prepare("UPDATE recovery_keys SET valid = 0 WHERE  id = ?  AND token = ? ")); 
  $insert_stmt-&gt;bind_param('is', $id, $token);
  // Execute the prepared query.
  if ($insert_stmt-&gt;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();}
  
  
  ?&gt;
  </code></pre>
  
  <p>When the code above is run I get a blank page with the the correct token code shown in the site link.</p>
  
  <p>This password2.php page has an include to a functions page which is shown below.</p>
  
  <pre><code>function checkUser($email)
  {
  global $db;
  
  $query = mysqli_query($db, "SELECT id FROM members WHERE email = '$email'");
  
  if(mysqli_num_rows($query) &gt; 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 &lt; $length; $i++) {
  $randomString .= $characters[rand(0, $charactersLength - 1)];
  }
  return md5($randomString);
  }
  
  function send_mail($to, $token)
  {
  require 'PHPMailer/PHPMailerAutoload.php';
  $mail = new PHPMailer;
  //$mail-&gt;SMTPDebug = 3;     
  
  $mail-&gt;isSMTP();  
  $mail-&gt;Host = '';
  $mail-&gt;SMTPAuth = true;
  $mail-&gt;Username = '';
  $mail-&gt;Password = '';
  $mail-&gt;SMTPSecure = 'ssl';
  $mail-&gt;Port = 465; 
  $mail-&gt;SetFrom = '';
  $mail-&gt;FromName = '';
  $mail-&gt;addAddress($to);
  $mail-&gt;addReplyTo('', 'Reply');
  $mail-&gt;isHTML(true);    
  $mail-&gt;Subject = 'Company Password Recovery Instruction';
  $link = 'x.php?email='.$to.'&amp;token='.$token;
  $mail-&gt;Body  = "&lt;b&gt;Hi&lt;/b&gt;&lt;br&gt;&lt;br&gt;You have just requested a new password for your company account with QBS Tax Submission. &lt;a href='$link' target='_blank'&gt;Click here&lt;/a&gt; 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.&lt;br&gt;&lt;i&gt;". $link."&lt;/i&gt;";
  
  $mail-&gt;AltBody = 'This is the body in plain text for non-HTML mail clients';
  if(!$mail-&gt;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) &gt; 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-&gt;prepare("SELECT id, username, email, password, salt
                FROM members 
                                WHERE email = ? LIMIT 1")) {
  $stmt-&gt;bind_param('s', $email);  // Bind "$email" to parameter.
  $stmt-&gt;execute();    // Execute the prepared query.
  $stmt-&gt;store_result();
  // get variables from result.
  $stmt-&gt;bind_result($id, $username, $db_password, $salt);
  $stmt-&gt;fetch();
  // hash the password with the unique salt.
  $password = hash('sha512', $password . $salt);
  if ($stmt-&gt;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-&gt;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-&gt;prepare("SELECT time 
                                FROM login_attempts 
                                WHERE id = ? AND time &gt; '$valid_attempts'")) {
  $stmt-&gt;bind_param('i', $id);
  // Execute the prepared query. 
  $stmt-&gt;execute();
  $stmt-&gt;store_result();
  // If there have been more than 5 failed logins 
  if ($stmt-&gt;num_rows &gt; 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-&gt;prepare("SELECT password 
                    FROM members 
                    WHERE id = ? LIMIT 1")) {
  // Bind "$id" to parameter. 
  $stmt-&gt;bind_param('i', $id);
  $stmt-&gt;execute();   // Execute the prepared query.
  $stmt-&gt;store_result();
  if ($stmt-&gt;num_rows == 1) {
  // If the user exists get variables from result.
  $stmt-&gt;bind_result($password);
  $stmt-&gt;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-~+_.?#=!&amp;;,/:%@$\|*\'()\\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;amp;', '&amp;#038;', $url);
  $url = str_replace("'", '&amp;#039;', $url);
  if ($url[0] !== '/') {
      // We're only interested in relative links from $_SERVER['PHP_SELF']
      return '';
  } else {
      return $url;
  }
  }
  </code></pre>