Locking a User Account with PHP and MySQL
Locking users out of their accounts after a certain number of unsuccessful login attempts is one of the most basic security measures you can take. I have a handful of personal applications which require a user to log in, and so I needed a way to lock out people who might try brute force attacks.
There are two ways I can think of setting up the database to solve this problem. The first would be to create a separate login table and store user login attempts there. The benefit of that approach is that you can keep track of every historical login attempt. The second approach would be to simply add columns to the user table which can be used to keep track of login attempts. This is the simpler approach, but it comes with the downside of not being able to store every old login attempt.
The first approach is better if you have some reason for needing to keep track of every login attempt. Using that approach on a large system with a lot of users who log in every day could start to fill up the database quickly, so either additional logic would need to be implemented to make sure only a certain numbers of login attempts are stored or the database storage would have to be increased.
For my implementation, I'm sticking with the first approach.
1. Setting Up the Database
I updated the user table by adding a columns to keep track of user locked out status, lock start time, and failed login attempts count.
Modified Table: userbase
new column: is_locked
new column: login_fail_count
new column: lock_start_timestamp
2. Writing the PHP script
This is where the action happens. Here is the basic flow of the lock out logic:
1. Check if the username exists.
2. If the username does not exist, display the login failed message.
3. If the username exists, get check if the user is locked out.
4. If the user is locked out, get the lock start time and check to see if enough time has passed since the user was locked out. If enough time has passed, unlock the user and reset the login failed attempts count to zero. If enough time has not passed, then exit and display a message saying the user account is locked.
5. If the user was not locked out, check to see if the password is correct.
6. If the password is correct, login the user and reset the failed login attempts.
7. If the password is incorrect, check to see if incrementing the failed login attempts count will result in the user being locked out. If so, then lock the user, write the incremented failed login count to the database, and write the lock start time to database. If the user won't be locked out, increment the failed login count on the database and display a message saying how many attempts are remaining.
Before showing the code below, I want to mention that showing the attempts remaining is something you may not want to do on the first or second login attempt. One reason the failed login message usually only says that either the username or password is incorrect is that you don't want someone knowing that a username is valid. It is useful for a real user, though, to know that he or she is getting close to being locked out.
Another thing to note about my solution is that it doesn't consider the time of the last login attempt. In this current version, if I tried to log in 4 times yesterday, I would be locked out the first time I got the password incorrect today. To fix this, all I would need to do is add a column to the database that recorded the timestamp of the last login attempt. Then, on a login attempt, I can use logic that would even decrement the the failed login count by a certain amount based on the elapsed time since the previous login attempt, or just reset the count altogether.
Here is the code:
if (isset($_POST["username"])) {
$loginName = $_POST['username'];
$magicWord = $_POST['password'];
try {
$con = new PDO("mysql:host=". DB_SERVER . ";dbname=" . DB_NAME, DB_USERNAME, DB_PASSWORD);
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT * FROM userbase WHERE username = '$loginName'";
$stmt = $con->prepare($sql);
$stmt->execute();
$data = $stmt->fetchAll();
$username_exists = false;
$lockout_minutes = 5;
$login_fail_max = 5;
$login_fail_count = 0;
$timestamp = date("Y-m-d H:i:s");
if (sizeof($data) == 1) {
$userid = $data[0]['userid'];
$username_exists = true;
if ($data[0]['is_locked'] == 1) {
$lock_start_timestamp = $data[0]['lock_start_timestamp'];
if ($lock_start_timestamp != NULL) {
$dif = (strtotime($timestamp) - strtotime($lock_start_timestamp));
if ($dif > $lockout_minutes * 60) {
$login_fail_count = 0;
$sql = "UPDATE userbase
SET is_locked = 0, login_fail_count = 0, lock_start_timestamp = NULL
WHERE userid = $userid";
$stmt = $con->prepare($sql);
$stmt->execute();
}
}
} else {
$login_fail_count = $data[0]['login_fail_count'];
}
}
if ($username_exists) {
$sql = "SELECT * FROM userbase WHERE username = '$loginName' AND magicword = '$magicWord' LIMIT 1";
$stmt = $con->prepare($sql);
$stmt->execute();
$data = $stmt->fetchAll();
if (sizeof($data) == 1) {
if ($data[0]["is_locked"] == 0) {
$sql = "UPDATE userbase
SET login_fail_count = 0
WHERE userid = $userid";
$stmt = $con->prepare($sql);
$stmt->execute();
// Login Successful
$_SESSION["userID"] = $data[0]["username"];
$_SESSION["userEmail"] = $data[0]["email"];
$_SESSION["userType"] = $data[0]["type"];
$_SESSION["userRole"] = $data[0]["role"];
header("Location: " . LOGIN_SUCCESS_URL);
} else {
// Account is locked. Increment failed login count
$sql = "UPDATE userbase
SET login_fail_count = login_fail_count + 1
WHERE username = '$loginName'";
$stmt = $con->prepare($sql);
$stmt->execute();
echo "<pre>Account is locked.</pre>";
}
} else {
// Not Successful. Increment failed login count
$will_be_locked = ($login_fail_count == $login_fail_max - 1);
$timestamp = date("Y-m-d H:i:s");
if ($will_be_locked) {
$sql = "UPDATE userbase
SET login_fail_count = login_fail_count + 1, is_locked = 1, lock_start_timestamp = '$timestamp'
WHERE userid = $userid";
} else {
$sql = "UPDATE userbase
SET login_fail_count = login_fail_count + 1
WHERE userid = $userid";
}
$stmt = $con->prepare($sql);
$stmt->execute();
if ($will_be_locked) {
echo "<pre>Account is locked.</pre>";
} else {
$attempts_remaining = ($login_fail_max - ($login_fail_count + 1));
if ($attempts_remaining > 0) {
echo "<pre>Incorrect username or password.</pre>";
if ($attempts_remaining <= 3) {
echo "<pre>Attempts remaining: " . ($login_fail_max - ($login_fail_count + 1)) . "</pre>";
}
}
}
}
} else {
echo "<pre>Incorrect username or password.</pre>";
}
} catch (PDOException $e) {
echo "Fail: " . $e->getMessage();
}
}Comments:
Leave a Comment
Submit