I want to lock out a particular user from trying to login after multiple failed attempts for 10 min. The aim is to user pNumber as the locking criteria not the IP address because the IP address is used by multiple users.
When I perform a select based on ip, the code is able to check if the user has entered a wrong pNumber and password, and if that is true. It prints the error indicating the first attempt was wrong and at the same time it stores the failed attempt in a db_table. So when the total failed attempt is >= 3, it locks.
This is not what I want, because I want it to lock for a specific amount of time and it should lock based on users pNumber and not IP address. The IP address is share by many users.
session_start();
include_once 'dbconnect.php';
if(isset($_SESSION['user'])!="")
{
header("Location: home.php");
}
if(isset($_POST['btn-login']))
{
//check login attempts
$userIP = $_SERVER['REMOTE_ADDR'];
$attempt_id = NULL;
$when = date('m/d/Y h:i:s', time());
$aptSql = mysql_query("SELECT COUNT(ip) AS failed_log FROM attempts WHERE pNumber='$pNumber'");
$row_count = mysql_fetch_assoc($aptSql);
$failed_attempt = $row_count['failed_log'];
$aptSql = mysql_free_result();
?>
<script>alert('<?php echo $failed_attempt;?>');</script>
<?php
if($failed_attempt >= 3)
{
$time = new Datetime();
?>
<script>alert('Sorry, you have exceeded numbers of attempts allowed. Please see your department manager');</script>
<?php
}
else
{
//Users login details
$pNumber = mysql_real_escape_string($_POST['pNumber']);
$upass = mysql_real_escape_string($_POST['pass']);
//check the details entered by user
$res=mysql_query("SELECT users.*, employees.* FROM users NATURAL JOIN employees WHERE users.pNumber='$pNumber'");
$row=mysql_fetch_array($res);
if($row['password']==md5($upass))
{
$_SESSION['user'] = $row['user_id'];
header("Location: home.php");
}
else
{
//Insert login attempts to table
$insertSql = mysql_query("INSERT INTO `employees`.`attempts` (`id`, `ip`, `when`, `pNumber`) VALUES ('$attempt_id', '$userIP', '$when', '$pNumber')");
//result
if($insertSql != false)
{
?>
<script>alert('You entered an invalid username or password, your attempt has been stored.');</script>
<?php
}
else
{
?>
<script>alert('Error Inserting your details. Please, see your department manager');</script>
<?php
}
}
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta name="description" content="">
<meta name="Lee & Micheal" content="">
<link rel="icon" href="../favicon.ico">
<title>Employee Time Stamp System</title>
<!-- Bootstrap core CSS -->
<link href="../dist/css/bootstrap.min.css" rel="stylesheet">
<!-- Custom styles for this template -->
<link href="signin.css" rel="stylesheet">
<!-- debug and js -->
<script src="../assets/js/ie-emulation-modes-warning.js"></script>
</head>
<body>
<div class="container">
<tr>
<td><center><h1>EMPLOYEE LOGIN</h1></center><br></td>
</tr>
<form method="post" class="form-signin" ><br>
<h2 class="form-signin-heading">LOGIN</h2>
<label for="inputEmail" class="sr-only">Personal ID</label>
<input type="text" name="pNumber" id="inputEmail" class="form-control" placeholder="Personal ID" required autofocus>
<label for="inputPassword" class="sr-only">Password</label>
<input type="password" name="pass" id="inputPassword" class="form-control" placeholder="Password" required>
<div class="checkbox">
<label>
<input type="checkbox" value="remember-me"> Remember me
</label>
</div>
<button class="btn btn-lg btn-primary btn-block" type="submit" name="btn-login">Login in</button>
</form>
</div> <!-- /container -->
<!-- IE10 viewport hack for Surface/desktop Windows 8 bug -->
<script src="../../assets/js/ie10-viewport-bug-workaround.js"></script>
</body>
</html>
Should i change these code too?
$pNumber = mysql_real_escape_string($_POST['pNumber']);
$upass = mysql_real_escape_string($_POST['pass']);
To:
$pNumber = mysqli_real_escape_string($_POST['pNumber']);
$upass = mysqli_real_escape_string($_POST['pass']);
This is how the code look like now
session_start();
include_once 'dbconnect.php';
if(isset($_SESSION['user'])!="")
{
header("Location: home.php");
}
if(isset($_POST['btn-login']))
{
//check login attempts
$userIP = $_SERVER['REMOTE_ADDR'];
$attempt_id = NULL;
$when = date('m/d/Y h:i:s', time());
$aptSql = mysqli_query($con, "SELECT COUNT(ip) AS failed_log FROM attempts WHERE pNumber='$pNumber'");
row_count = mysqli_fetch_assoc($aptSql);
$failed_attempt = $row_count['failed_log'];
$aptSql = mysqli_free_result();
?>
<script>alert('<?php echo $failed_attempt;?>');</script>
<?php
if($failed_attempt >= 3)
{
$time = new Datetime();
?>
<script>alert('Sorry, you have exceeded numbers of attempts allowed. Please see your department manager');</script>
<?php
}
else
{
//Users login details
$pNumber = mysqli_real_escape_string($_POST['pNumber']);
$upass = mysqli_real_escape_string($_POST['pass']);
//check the details entered by user
$res=mysqli_query($con, "SELECT users.*, employees.* FROM users NATURAL JOIN employees WHERE users.pNumber='$pNumber'");
$row=mysqli_fetch_array($res);
if($row['password']==phpass($upass))
{
$_SESSION['user'] = $row['user_id'];
header("Location: home.php");
}
else
{
//Insert login attempts to table
$insertSql = mysqli_query($con, "INSERT INTO `employees`.`attempts` (`id`, `ip`, `when`, `pNumber`) VALUES ('$attempt_id', '$userIP', '$when', '$pNumber')");
//result
if($insertSql != false)
{
?>
<script>alert('You entered an invalid username or password, your attempt has been stored.');</script>
<?php
}
else
{
?>
<script>alert('Error Inserting your details. Please, see your department manager');</script>
<?php
}
}
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta name="description" content="">
<meta name="Lee & Micheal" content="">
<link rel="icon" href="../favicon.ico">
<title>Employee Time Stamp System</title>
<!-- Bootstrap core CSS -->
<link href="../dist/css/bootstrap.min.css" rel="stylesheet">
<!-- Custom styles for this template -->
<link href="signin.css" rel="stylesheet">
<!-- debug and js -->
<script src="../assets/js/ie-emulation-modes-warning.js"></script>
</head>
<body>
<div class="container">
<tr>
<td><center><h1>EMPLOYEE LOGIN</h1></center><br></td>
</tr>
<form method="post" class="form-signin" ><br>
<h2 class="form-signin-heading">LOGIN</h2>
<label for="inputEmail" class="sr-only">Personal ID</label>
<input type="text" name="pNumber" id="inputEmail" class="form-control" placeholder="Personal ID" required autofocus>
<label for="inputPassword" class="sr-only">Password</label>
<input type="password" name="pass" id="inputPassword" class="form-control" placeholder="Password" required>
<div class="checkbox">
<label>
<input type="checkbox" value="remember-me"> Remember me
</label>
</div>
<button class="btn btn-lg btn-primary btn-block" type="submit" name="btn-login">Login in</button>
</form>
</div> <!-- /container -->
<!-- IE10 viewport hack for Surface/desktop Windows 8 bug -->
<script src="../../assets/js/ie10-viewport-bug-workaround.js"></script>
</body>
</html>
What is the problem now
session_start();
include_once 'dbconnect.php';
if(isset($_SESSION['user'])!="")
{
header("Location: home.php");
}else
if(isset($_POST['btn-login']))
{
//check login attempts
$pNumber = mysqli_real_escape_string($_POST['pNumber']);
$upass = mysqli_real_escape_string($_POST['pass']);
$userIP = $_SERVER['REMOTE_ADDR'];
$attempt_id = NULL;
$when = date('m/d/Y h:i:s', time());
$aptSql = mysqli_query($con, "SELECT COUNT(ip) AS failed_log FROM attempts WHERE pNumber='$pNumber'");
$row_count = mysqli_fetch_assoc($aptSql);
$failed_attempt = $row_count['failed_log'];
$lastlocked = date('m/d/Y h:i:s', time());
$query = mysqli_query ($con, "SELECT id, pNumber, UNIX_TIMESTAMP(lastlocked) as lockDatetimestamp ROM manage_users HERE (id = $attempt_id) and (lastlocked IS NOT NULL) and
(lastlocked <= DATE_SUB(now(), INTERVAL 10 MINUTE))");
$new_row = mysqli_fetch_array($query);
$aptSql = mysqli_free_result();
?>
<script>alert('<?php echo $failed_attempt;?>');</script>
<?php
if($failed_attempt >= 3)
{
?>
<script>alert('Sorry, you have exceeded numbers of attempts allowed. Please see your department manager');</script>
<?php
}
else
{
//Users login details
//check the details entered by user
$res=mysqli_query($con, "SELECT users.*, employees.* FROM users NATURAL JOIN employees WHERE users.pNumber='$pNumber'");
$row=mysqli_fetch_array($res);
if($row['password']==phpass($upass))
{
$_SESSION['user'] = $row['user_id'];
header("Location: home.php");
}
else
{
//Insert login attempts to table
$insertSql = mysqli_query($con, "INSERT INTO `employees`.`attempts` (`id`, `ip`, `when`, `pNumber`, `lastlocked`) VALUES ('$attempt_id', '$userIP', '$when', '$pNumber', '$lastlocked')");
//result
if($insertSql != false)
{
?>
<script>alert('You entered an invalid username or password, your attempt has been stored.');</script>
<?php
}
else
{
?>
<script>alert('Error Inserting your details. Please, see your department manager');</script>
<?php
}
}
}
}
there is a problem with this code an idel
$row_count = mysqli_fetch_assoc($aptSql);
$failed_attempt = $row_count['failed_log'];
$lastlocked = date('m/d/Y h:i:s', time());
$query = mysqli_query ($con, "SELECT id, pNumber, UNIX_TIMESTAMP(lastlocked) as lockDatetimestamp ROM manage_users HERE (id = $attempt_id) and (lastlocked IS NOT NULL) and
(lastlocked <= DATE_SUB(now(), INTERVAL 10 MINUTE))");
$new_row = mysqli_fetch_array($query);
$lockedtime = $mysqli_fetch_array['lockDatetimestamp'];
$query=mysqli_free_result();
$aptSql = mysqli_free_result();
Some updates has been made. First I had to revert to the old mysql statement to first ensure that the code does what it has to do. When the problem gets solved I will change it back to mysqli statement.
The problem now after lock down of attempts that are more than >=3. Its difficult comparing the MAX(lastlocked) with the current time which is 30 minutes over to unlock the login form again. So my problem is how to go do this and should I use switch loop and secondly the if($failed_attempt >= 3) is blocking all user, which is not what I want.
session_start();
include_once 'dbconnect.php';
if(isset($_SESSION['user'])!="")
{
header("Location: home.php");
}
if(isset($_POST['btn-login']))
{
//prevents SQL injecions
$pNumber = mysql_real_escape_string($_POST['pNumber']);
$upass = mysql_real_escape_string($_POST['pass']);
//used for failed attempts
$userIP = $_SERVER['REMOTE_ADDR'];
$attempt_id = NULL;
$aptSql = mysql_query("SELECT COUNT(pNumber) AS failed_log FROM attempts WHERE ip='$userIP'");
$row_count = mysql_fetch_assoc($aptSql);
$failed_attempt = $row_count['failed_log'];
$lastlocked = mysql_query("SELECT MAX(lastlocked) FROM attempts WHERE pNumber='$pNumber'");
$yeah = mysql_fetch_array($lastlocked);
if($failed_attempt >= 3)
{
?>
<script>alert('Sorry, you have exceeded numbers of attempts allowed. Please see your department manager');</script>
<?php
}
elseif(strtotime($lastlocked) < time())
{
?>
<script>alert('<?php echo $lastlocked['lastlocked'];?>');</script>
<?php
}
else
{
$res=mysql_query("SELECT users.*, employees.* FROM users NATURAL JOIN employees WHERE users.pNumber='$pNumber'");
$row=mysql_fetch_array($res);
if($row['password']==md5($upass))
{
$_SESSION['user'] = $row['user_id'];
header("Location: home.php");
}
else
{
//Insert login attempts to table
$insertSql = mysql_query("INSERT INTO `employees`.`attempts` (`id`, `ip`, `pNumber`) VALUES ('$attempt_id', '$userIP', '$pNumber')");
//result
if($insertSql != false)
{
?>
<script>
alert('You entered an invalid username or password, your attempt has been stored.');
</script>
<?php
}
else
{
?>
<script>
alert('Error Inserting your details. Please, see your department manager');
</script>
<?php
}
}
}
}
Finally, solve the problem and the contribution I got from here helped me. Below is the working code for anyone having the same problem.
session_start();
include_once 'dbconnect.php';
if(isset($_SESSION['user'])!="")
{
header("Location: home.php");
}
if(isset($_POST['btn-login']))
{
//prevents SQL injecions
$pNumber = mysql_real_escape_string($_POST['pNumber']);
$upass = mysql_real_escape_string($_POST['pass']);
//used for failed attempts
$userIP = $_SERVER['REMOTE_ADDR'];
$attempt_id = NULL;
$aptSql = mysql_query("SELECT COUNT(pNumber) AS failed_log FROM attempts WHERE pNumber='$pNumber'");
$row_count = mysql_fetch_assoc($aptSql);
$failed_attempt = $row_count['failed_log'];
$locked_time = mysql_query("SELECT LAST_INSERT_ID(), DATE_ADD(lastlocked, INTERVAL 2 MINUTE) AS cheknow FROM `attempts` ORDER BY id DESC LIMIT 1");
$show_row_res = mysql_fetch_array($locked_time);
$convert_time= strtotime($show_row_res['cheknow']);
$current_time = time();
?>
<script>alert('The time now is : <?php echo $current_time; ?>') </script>
<script>alert('The converted time : <?php echo $convert_time['cheknow']; ?>') </script>
<?php
//check attempts and lock out user not ip address
if($failed_attempt >= 3 and $convert_time > $current_time)
{
?>
<script>alert('Sorry, you have exceeded numbers of attempts allowed. Please see your department manager');</script>
<?php
}
else
{
$res=mysql_query("SELECT users.*, employees.* FROM users NATURAL JOIN employees WHERE users.pNumber='$pNumber'");
$row=mysql_fetch_array($res);
if($row['password']==md5($upass))
{
$_SESSION['user'] = $row['user_id'];
header("Location: home.php");
}
else
{
//Insert login attempts to table
$insertSql = mysql_query("INSERT INTO `employees`.`attempts` (`id`, `ip`, `pNumber`) VALUES ('$attempt_id', '$userIP', '$pNumber')");
//result
if($insertSql != false)
{
?>
<script>
alert('You entered an invalid username or password, your attempt has been stored.');
</script>
<?php
}
else
{
?>
<script>
alert('Error Inserting your details. Please, see your department manager');
</script>
<?php
}
}
}
}
