How to maintain the password history using php and mysql

In this tutorial I will explain how to maintain password history using php and mysql. This means a user who must change their password can’t reuse the password they just had. This tutorial explains how user new Password should not be same as any of the prevoius 3 Passwords.

This tutorial include three pages

Index.php
Change-password.php
Config.php

And includes two tables

tblregistration
tblpasswordhistory

Structure of table tblregistration

CREATE TABLE IF NOT EXISTS `tblregistration` (
`id` int(11) NOT NULL,
`FullName` varchar(120) DEFAULT NULL,
`EmailId` varchar(120) DEFAULT NULL,
`Password` varchar(150) DEFAULT NULL,
`RegDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `tblregistration` (

`id` int(11) NOT NULL,

`FullName` varchar(120) DEFAULT NULL,

`EmailId` varchar(120) DEFAULT NULL,

`Password` varchar(150) DEFAULT NULL,

`RegDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

Structure of table tblpasswordhistory

CREATE TABLE IF NOT EXISTS `tblpasswordhistory` (
`id` int(11) NOT NULL,
`UserEmail` varchar(150) DEFAULT NULL,
`password` varchar(200) DEFAULT NULL,
`PostingDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `tblpasswordhistory` (

`id` int(11) NOT NULL,

`UserEmail` varchar(150) DEFAULT NULL,

`password` varchar(200) DEFAULT NULL,

`PostingDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

index.php page include registration and login form

Code for Registration-

if(isset($_POST['submit']))
{
$fullname=$_POST['fname'];
$email=$_POST['email'];
$password=md5($_POST['password']);
// Code for check email availability
$rt="SELECT * from tblregistration where EmailId=:email";
$query2= $dbh -> prepare($rt);
$query2->bindParam(':email', $email, PDO::PARAM_STR);
$query2-> execute();
$results = $query2->fetchAll(PDO::FETCH_OBJ);
if($query2->rowCount() > 0)
{
$error="Email id already registered ";
}
else{
$sql="INSERT INTO  tblregistration(FullName,EmailId,Password) VALUES(:fullname,:email,:password)";
$query = $dbh->prepare($sql);
$query->bindParam(':fullname',$fullname,PDO::PARAM_STR);
$query->bindParam(':email',$email,PDO::PARAM_STR);
$query->bindParam(':password',$password,PDO::PARAM_STR);
$query->execute();
$lastInsertId = $dbh->lastInsertId();
if($lastInsertId)
{
// code for insert password into passhistory table.
$ret="INSERT INTO tblpasswordhistory(UserEmail,password) VALUES(:email,:password)";
$query1 = $dbh->prepare($ret);
$query1->bindParam(':email',$email,PDO::PARAM_STR);
$query1->bindParam(':password',$password,PDO::PARAM_STR);
$query1->execute();
$msg="Your info submitted successfully";
}
else
{
$error="Something went wrong. Please try again";
}
}}

if(isset($_POST['submit']))

{

$fullname=$_POST['fname'];

$email=$_POST['email'];

$password=md5($_POST['password']);

// Code for check email availability

$rt="SELECT * from tblregistration where EmailId=:email";

$query2= $dbh -> prepare($rt);

$query2->bindParam(':email', $email, PDO::PARAM_STR);

$query2-> execute();

$results = $query2->fetchAll(PDO::FETCH_OBJ);

if($query2->rowCount() > 0)

{

$error="Email id already registered ";

}

else{

$sql="INSERT INTO tblregistration(FullName,EmailId,Password) VALUES(:fullname,:email,:password)";

$query = $dbh->prepare($sql);

$query->bindParam(':fullname',$fullname,PDO::PARAM_STR);

$query->bindParam(':email',$email,PDO::PARAM_STR);

$query->bindParam(':password',$password,PDO::PARAM_STR);

$query->execute();

$lastInsertId = $dbh->lastInsertId();

if($lastInsertId)

{

// code for insert password into passhistory table.

$ret="INSERT INTO tblpasswordhistory(UserEmail,password) VALUES(:email,:password)";

$query1 = $dbh->prepare($ret);

$query1->bindParam(':email',$email,PDO::PARAM_STR);

$query1->bindParam(':password',$password,PDO::PARAM_STR);

$query1->execute();

$msg="Your info submitted successfully";

}

else

{

$error="Something went wrong. Please try again";

}

}}

Code for login

if(isset($_POST['login']))
{
$email=$_POST['emailid'];
$password=md5($_POST['password']);
$sql ="SELECT EmailId,Password,FullName FROM tblregistration WHERE EmailId=:email and Password=:password";
$query= $dbh -> prepare($sql);
$query-> bindParam(':email', $email, PDO::PARAM_STR);
$query-> bindParam(':password', $password, PDO::PARAM_STR);
$query-> execute();
$results=$query->fetchAll(PDO::FETCH_OBJ);
if($query->rowCount() > 0)
{
foreach ($results as $result) {
$_SESSION['fname']=$result->FullName;
$_SESSION['login']=$_POST['emailid'];
echo "<script type='text/javascript'> document.location ='change-password.php'; </script>";
}
}
else{
echo "<script>alert('Invalid Details');</script>";
}
}

if(isset($_POST['login']))

{

$email=$_POST['emailid'];

$password=md5($_POST['password']);

$sql ="SELECT EmailId,Password,FullName FROM tblregistration WHERE EmailId=:email and Password=:password";

$query= $dbh -> prepare($sql);

$query-> bindParam(':email', $email, PDO::PARAM_STR);

$query-> bindParam(':password', $password, PDO::PARAM_STR);

$query-> execute();

$results=$query->fetchAll(PDO::FETCH_OBJ);

if($query->rowCount() > 0)

{

foreach ($results as $result) {

$_SESSION['fname']=$result->FullName;

$_SESSION['login']=$_POST['emailid'];

echo "<script type='text/javascript'> document.location ='change-password.php'; </script>";

}

else{

echo "<script>alert('Invalid Details');</script>";

}

Code for change password

// full Code for change password
if(isset($_POST['change']))
{
$email=$_SESSION['login'];
$newpass=md5($_POST['newpass']);
// Code for vefify current Password
$query2 = $dbh->prepare("SELECT Password FROM  tblregistration WHERE EmailId =:email");
$query2->bindParam(':email', $email, PDO::PARAM_STR);
$query2-> execute();
$results = $query2->fetchAll(PDO::FETCH_OBJ);
if($query2->rowCount() > 0)
{
//Code for check last 3 password by using array_push and in_array
$query=$dbh->prepare("SELECT * FROM tblpasswordhistory WHERE UserEmail=:email order by id desc limit 3");
$query->bindParam(':email', $email, PDO::PARAM_STR);
$query-> execute();
$resultss = $query->fetchAll(PDO::FETCH_OBJ);
$cnt=1;
$passwrd=array();
foreach($resultss as $rt)
{
array_push($passwrd,$rt->password);
}
if(in_array($newpass,$passwrd))
{
$error="Your new Password should not be same as any of the prevoius 3 Passwords";
}
else {
// code for update the password
$con="update tblregistration set Password=:cmppass where EmailId=:email";
$chngpwd1 = $dbh->prepare($con);
$chngpwd1->bindParam(':cmppass', $newpass, PDO::PARAM_STR);
$chngpwd1->bindParam(':email', $email, PDO::PARAM_STR);
$chngpwd1->execute();
//Code for insertion new password in tblpassword history
$sql="INSERT INTO tblpasswordhistory(UserEmail,password) VALUES(:email,:newpassrd)";
$query = $dbh->prepare($sql);
$query->bindParam(':email',$email,PDO::PARAM_STR);
$query->bindParam(':newpassrd',$newpass,PDO::PARAM_STR);
$query->execute();
$lastInsertId = $dbh->lastInsertId();
if($lastInsertId)
{
$msg="Password changed successfully ";
}
}
}
else{
$error="Current password not matched ";
}
}