Home    posts    mysql login system

Posted on: October 12, 2017Updated: November 24, 2017

PHP and MYSQL login system using PBKDF2

Today we are going to create a secure login system using PHP and MYSQL. It will use PBDKF2 hashing method with username as a salt and 100k iteration rounds to authenticate the user. Another feature we will make is to count login attempts and ban the ip address for 10 minutes after a few unsuccessful attempts. We will use sessions to create condition structure, one for successfuly authenticated user and one for banned status. The login form will appear as default when none of the above conditions are true. But first the following tables need to be added into your database.

CREATE TABLE `login_users` (
  `login_user` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `login_pass` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL

CREATE TABLE `login_attempts` (
  `username` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `ip_address` varchar(45) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

CREATE TABLE `login_banned` (
  `username` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `ip_address` varchar(45) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

The actual code will be done with procedural PHP so make sure you have the right config file ready. We will include it into our script. Another thing we need to do is to insert an example user into the login_users table so we'll be able test the script. For the purpose of this tutorial, the below user's credentials will consist of username and password(hashed with PBKDF2). But you can use the methods here to generate your own hash.

INSERT INTO `login_users` (`id`, `login_users`, `login_pass`) VALUES
(1, 'username', 'db24d69f94bc903bf0024cd7783f58cfb06722eee23e8b041fd4e38eed374814');

PART 1 - Create authentication system

First we need to start a session and include our database config file and then check against a databse whether the ip is banned for the period of time. Of course there is none but that's how the script will always start. If it is banned, then the script will set a session called banned to true and activate the ban part of the code(Relevant to PART2). If it is not, then it will generate a hash from input and a defined $rounds variable and compare it along with username against the login_users table rows. If it finds a match, it will create a session for the username. If not, attempts will be added into the login_attempts table and if there were more than 3 failed attempts from the same IP in the last ten minutes, a row with user, IP address and timestap will be added to the login_banned table which will activate the first part of the code and display ban part.


// Start a session and include the mysql config file that uses procedural PHP mysqli function.

// Get IP address and save it to a variable

// $mysqli variable is from the include file.
// SQL query is to select everything from login_banned table where user's ip above matches ip in database in the last 10 minutes.
$ipcheck = mysqli_query($mysqli, "SELECT * FROM login_banned WHERE ip_address = '$ip' AND timestamp > (now() - interval 10 minute)");

// Count how many rows, results matches the above query
$row = mysqli_num_rows($ipcheck);

// If row is equal to 1, make session banned true
if($row === 1) {$_SESSION['banned'] = true;}

else {

	// else make session banned false
	$_SESSION['banned'] = false;

	// When login button is clicked on, got from the login form, then execute below..
	if(isset($_POST['auth'])) {

		// Prevent SQL injection by sanitizing $_POST input
		$user = mysqli_real_escape_string($mysqli, $_POST['username']);

		// We only need to do that for $user because $pass variable won't be checked in SQL query, Instead it will be $passhash
		$pass = $_POST['password'];

		// Create a password hash using PBKDF2 with SHA-512, 100k rounds as 32 byte HEX output
		$rounds = 100000;
		$passhash = hash_pbkdf2("sha512", $pass, $user, $rounds, 64);

		// Query to check if user posted matches user in database and if password hash generated from above variables matches the one in DB
		$query = mysqli_query($mysqli, "SELECT * FROM login_users WHERE login_user = '$user' AND login_pass = '$passhash'");
		$rowuser = mysqli_fetch_array($query);
		$count = mysqli_num_rows($query);

		// if there is exactly one row that matches authentication process, execute below...
		if($count === 1) {

			// Session cookie id is regenerated first to prevent session fixation
			// Session userid is created to contain unique id from DB, session name to be displayed below in part 2
			// Session usercheck is created from IP and user agent specification to be compared to session user below
			$_SESSION['userid'] = $rowuser['id'];
			$_SESSION['name'] = htmlentities($user);
			$_SESSION['usercheck'] = hash('sha256', $identifier);

		else {

			// Create a string variable to be displayed below login form(see PART 2)
			$incorrect = "Your login combination is incorrect! Please try again...";

			// Insert user, ip and timestamp of the attempt into the login_attempts table
			mysqli_query($mysqli, "INSERT INTO login_attempts (username, ip_address ,timestamp) VALUES ('$user', '$ip',CURRENT_TIMESTAMP)");

			// Get rows on how many attempts were recorded from the same ip in the last 10 minutes
			$countresult = mysqli_query($mysqli, "SELECT * FROM login_attempts WHERE ip_address = '$ip' AND timestamp > (now() - interval 10 minute)");
			$attempts = mysqli_num_rows($countresult);

			// If there were more than 3 attempts recorded from the same ip in the last 10, insert user, ip and timestamp into login_banned table
			if($attempts > 3) {mysqli_query($mysqli, "INSERT INTO login_banned (username, ip_address ,timestamp) VALUES ('$user', '$ip', CURRENT_TIMESTAMP)");}

PART 2 - Wrap it all up inside the condition structure

This step should be pretty simple. Here, three different parts will be displayed. First part(ADMIN) if SESSION['userid'] is set and SESSION['usercheck'] passes the current user's IP and user agent, second(BAN) if SESSION['banned'] is set and third(LOGIN FORM) if none of these previous parts are true. For the first part we will also create a logout button that will redirect to another file(relevant to PART 3).

<!-- 1. If User SESSION['userid'] is successfully created and client's IP and user agent corresponds to the one created when authenticated, the admin will be displayed.
<!-- PHP(7.0) one liner enables to create html code, that responds to the condition, outside of PHP tags. The key is to end it with colon(:) -->
<?php if(isset($_SESSION['userid']) && ($_SESSION['usercheck'] === hash('sha256', $_SERVER['HTTP_USER_AGENT'].$_SERVER['REMOTE_ADDR']))): ?>

<!-- To keep a clean record in attempts table, we will delete attempts(upon success) from the same IP for the same username that failed before -->
<!-- This means that attempts for different username will still be kept from the same IP -->
<?php mysqli_query($mysqli, "DELETE FROM login_attempts WHERE ip_address = '$ip' AND username = '$user'"); ?>

<p>Login successful</p>
<?php echo "Welcome ".$_SESSION['name']; ?>
<!-- Logout button to take action to another file, logout.php in this case. -->
<form action="logout.php"><input type="submit" name="logout" value="logout"/></form>

<!-- 2. SESSION['banned'] is true, create a string variable to be displayed below. -->
<?php elseif($_SESSION['banned'] === true): {$banned = 'Maximum attempts exceeded! You are banned for 10 minutes. Try again later...';} ?>
<p><?php echo $banned;?></p>

<!-- 3. There was no SESSION created, yet, so display the login form. -->
<?php else: ?>
<form name="login" action="" method="post">
<div>Username:<br/><input name="username" type="text" id="username" size="17"></div>
<div>Password:<br/><input name="password" type="password" id="password" size="17"></div><br/>
<input type="submit" name="auth" value="Login"/>
<!-- Display the authentication failure message(defined in PART 1). -->
<?php echo "<br/>".$incorrect; ?>

<!-- End of the condition structure. -->
<?php endif; ?>

This is pretty basic but you can easily include your own HTML doctype, headers and body elements under each of the conditions.

PART 3 - Create logout.php file

Same as with the cookie login system, we will create logout.php file to start and destroy the session to log out successfuly. After, you may redirect wherever desired.


// After being redirected, start a new session, again regenerate id(because of the privilege change) and destroy it to successfully log out.
// Redirect to the index.page. 

Alternatively, the script can be downloaded here.


Be the first to comment.

Add a comment:

I have read and agree with the Privacy terms and conditions.