Simple PHP PDO Login System Tutorial

Hello everyone,

This is tutorial on making a simple PHP PDO login and registration system. We will be touching on PHP PDO, database design and security practices. It will be a bit different from the common tutorials out there as this will only cover certain core concepts and I will then give a final full example that will allow you to study from the code itself. This is because I find that having learnt core concepts and thereafter, learning from examples will make it easier for people to understand it better.

Introduction

So first off, here’s an introduction to PDO:

PDO stands for PHP Data Objects, it is an interface for programmers to safely access databases in PHP. It is a better alternative to PHP MySQL, which is already deprecated, that has many new features such as statement preparation and object-oriented functions. This makes it quite different from PHP MySQL and PHP MySQLi.

To start a database connection, you will need to call a new PDO object.

try {

$dbh = new PDO('mysql:host=localhost;dbname=some_database_name', 'admin_2', '123456'); //establish it

} catch (PDOException $e) {

//seems like that there was an error!
print_r("MySQL Error!: " . $e->getMessage()); //print the error output
exit();

}

If that work well for you, great, it confirms that you have PHP PDO!

So next would be learning how to carry out a SELECT SQL statement.

$stmt = $dbh->prepare("SELECT * FROM `userdata` where `username` = ? AND `password` = ?");
$username = 'lol';
$password = 'verysecuredpassword';
$stmt->execute(array($username, $password);

Excellent! As you can see here, you prepare the SQL statement and when data needs to be specified, you will see this strange thing here – where `username` = ? AND `password` = ?. Very odd isn’t it? Its basically preparing this statement for the data to be called as you can see here – $stmt->execute(array($username, $password);. So the 1st question mark will reference to $username while the 2nd question mark will reference to $password. This would mean that the code is literally being executed as "SELECT * FROM `userdata` where `username` = " . $username . " AND `password` = " . $password.

So far so good? Easy isn’t it? :)

Database Structure

Now that we have covered the PDO part, let us move on to database structure planning.
To have a login system, we will need at least 3 things: PRIMARY id KEY, username and password. The username will be used to create an identity for the user while the password is used by the user to authenticate to the specified user account.

So we should have something like this:

CREATE TABLE userdata (

id int(10) unsigned NOT NULL AUTO_INCREMENT,
username varchar(64) NOT NULL,
password varchar(64) NOT NULL
PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Security Practices

Now that we are done with the database design, let’s move on to some security practices that are worth taking note of.

Data sensitive details such as password should never be stored in plaintext in the database. Therefore, we should always hash these details using the PHP crypt() function. So this is what should be done:

$password = crypt($_REQUEST['password'], CRYPT_BLOWFISH);

Some people will try to attempt to insert script using HTML characters, we need to filter this off as well.

$username = htmlspecialchars($_REQUEST['username'], ENT_QUOTES, "UTF-8");

Although this HTML sanitisation is not required for passwords (since they are going to be encrypted anyway), we should just sanitise the password anyway.

Next, we want to prevent identity hijacking in the event where people modify their cookies. So we need to create 2 cookies, one that contains the username and the other one contains a cookie hash. The cookie hash is used to verify the identity of the person and is unique upon every login. So even if a hijacker has modified their cookie to “GIANT_CRAB”, without the correct cookie hash, the system will reject him. This would also mean that we will need to modify our existing database design to include this new cookie hash.

New database design:

CREATE TABLE userdata (

id int(10) unsigned NOT NULL AUTO_INCREMENT,
username varchar(64) NOT NULL,
password varchar(64) NOT NULL
cookiehash varchar(64) DEFAULT NULL
PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Cookies that will be set:

setcookie('username_cookie', "GIANT_CRAB", 0, "/", "", false, true);
setcookie('login_cookie', "abcd12345", 0, "/", "", false, true);

Identifying if a user is logged in, using both the username cookie and cookie hash:

//$dbh parameter should be the database connection that is started using "$dbh = new PDO();"
//$username parameter should be the username of the user
//$cookiehash paramter should be the cookie hash value
function isLoggedIn($dbh, $username, $cookiehash) {

$stmt = $dbh->prepare("SELECT * FROM `userdata` where `username` = ? AND `cookiehash` = ?");
if($stmt->execute(array($username, $cookiehash))) {

if($stmt->rowCount()) { //check if user exists and the cookiehash is correct

return true;

}

} else {

return false;

}

}
//so to check if a user is logged in, it is done like this
if(isLoggedIn($dbh, $username, $cookiehash)) {

//user is logged in

} else {

//user is not logged in or the cookie hash is incorrect

}

Finally, we want to prevent cross site request forgery (CSRF) attempts too. This means that we need to generate a cookie and also modify the login form in such a way that it will also submit the value of the cookie.

PHP:

$csrf_token = md5(mt_rand() . mt_rand()); //we want it fast, security isn't a major issue here
setcookie("csrf_token", $csrf_token, 0, "/", "", $ssl, true);

HTML:

<form>

<div class="form-group">

<label for="loginUsername">Username</label>
<input type="text" class="form-control" id="loginUsername" placeholder="Enter username">

</div>
<div class="form-group">

<label for="loginPassword">Password</label>
<input type="password" class="form-control" id="loginPassword" placeholder="Password">

</div>
<input id="loginToken" type="hidden" value="<?php echo $csrf_token; ?>"/>
<button type="submit" class="btn btn-primary">Login</button>

</form>

Conclusion

Congratulations! Now you have grabbed hold of the basic important concepts that are needed in handling a PHP login form. I have created a complete PHP PDO example that is available to download on Github, the URL will provided below.

Github Example: https://github.com/GIANTCRAB/Simple-PHP-PDO-Login

Author: Woo Huiren

Howdy, I'm a IT student who is currently serving my full time national service. I do lots of things related to IT but I have an exceptionally great love for the web and opensource, so expect me to blog a lot about it! :)

Leave a Reply