Basic Login Authentication with PHP and MySQL
Contents
- Abstract
- Where do I begin?
- Create a user database
- Adding users
- Processing Added Users
- Logging In
- Testing for logged in user
- Logging Out
- Summary
Abstract
This tutorial is an attempt to show how to put together a basic user authentication system using PHP and MySQL. To begin with you will need to have the PDO MySQL drivers configured into your PHP build as we will be using this to interface with the database, rather than the old PHP MySQL extension. PDO provides a standard OO interface for databases. Read more on PDO at tutorials/Introduction-to-PHP-PDO.html. This tutorial assumes a basic level of PHP/MySQL knowledge to enable you to make database queries. More can be read on this at http://phpro.org/tutorials/Introduction-to-PHP-and-MySQL.html.
Where do I begin?
Like all things, we begin with the basics. Lets look at the steps we need to complete to enable us to achieve our goal of logging in.
Whiteboard
- Create user database
- Create login form
- Validation of form contents
- Connect to database
- Compare login values to the database values
- Set a session variable if we are successful
Create a user database
Before we can do anything, we need to create a database to hold the user login username and password. So lets begin with a simple database schema to hold these values. We will also need a database to hold our table. Create a database named "phpro_auth".
mysqladmin create phpro_auth -u root -p
Next, create user to use the database so you dont need to use root.
mysql phpro_auth -u root -p
When you have logged into the database, use this command to create a generic username and password.
GRANT ALL ON phpro_auth TO mysql_username@localhost IDENTIFIED BY 'mysql_password'
Of course, you can change the mysql_username and mysql_password to whatever you like, but these will be the values PHP uses to access to the database. With that accomplished we can now create the table that will hold the login information.
phpro_user_id int(11) NOT NULL auto_increment,
phpro_username varchar(20) NOT NULL,
phpro_password char(40) NOT NULL,
PRIMARY KEY (phpro_user_id),
UNIQUE KEY phpro_username (phpro_username)
);
The above schema can be loaded directly from the mysql prompt, or if you wish to store it as phpro_auth.sql then load it with the command line
mysql phpro_auth < phpro_auth -u db_username -p
Adding Users
Now we have the database set up to hold the information, we need to add a user or two. This is no more than a simple HTML form with two fields but the form is also your first line of defence against malicious users as we will see. The form will have a text field for the db_username, and a password field for the db_password. We will call this adduser.php and it will look like this:
<?php
/*** begin our session ***/
session_start();
/*** set a form token ***/
$form_token = md5( uniqid('auth', true) );
/*** set the session form token ***/
$_SESSION['form_token'] = $form_token;
?>
<html>
<head>
<title>PHPRO Login</title>
</head>
<body>
<h2>Add user</h2>
<form action="adduser_submit.php" method="post">
<fieldset>
<p>
<label for="phpro_username">Username</label>
<input type="text" id="phpro_username" name="phpro_username" value="" maxlength="20" />
</p>
<p>
<label for="phpro_password">Password</label>
<input type="text" id="phpro_password" name="phpro_password" value="" maxlength="20" />
</p>
<p>
<input type="hidden" name="form_token" value="<?php echo $form_token; ?>" />
<input type="submit" value="→ Login" />
</p>
</fieldset>
</form>
</body>
</html>
Note that in the form above, the names of the fields, correspond exactly to the names of the fields in the database. It is recommended for this convention to be applied to all form and database interactions to avoid confusion. This is to avoid confusion particularly if somebody else has to edit your code at a later date. Quite often experienced developers forget this and apply "clever" short names that third party find meaningless (you know who you are!). Another point to notice is the maxlength attribute has been set to the same value as its corresponding database field. This is to protect users from themselves if they try to enter a value greater than the database field will hold. It will not, however, stop a malicious user who may user thier own form to enter values. This must be taken care of at the processing level when the form is submitted.
From a security aspect this form does the basic needs of checking for type and length. More importantly however is the setting of the form token in both the form and as a session variable. The ensures that the form that is being posted in, in fact, OUR form and not one used by some malicious user. It also prevents multiple postings so our database is not flooded by somebody hitting the refresh button ten thousand times. It is a simple addition to your forms and will go a long way to securing them. It should be noted, that to username and password from the form is sent in clear text from the clien to the server,and to secure this, you must use HTTPS rather than the standard HTTP
Processing Added Users
Now that we have a form to add the users, we need to be able to process the information they have submitted. PHP provides the ability to filter data from users with the filter extention. Here we create a file called adduser_submit.php that will be used to process the adduser data that is submitted from the form. The process here is to check the values of the fields are alphanumeric, and that the string lengths are no longer than expected twenty(20) chars for a username. Although the password field maxlength is set to twenty(20) also, the database field to hold it is forty chars long. This is because we will be sha1 encrypting the password and the sha1 encryption creates a forty character encrypted string. We should also check the username is not already in use as having two usernames the same would rather defeat the whole purpose.
If the user data is ok, we can then add the username and password to the database. Here is the code that will do it for us:
<?php
/*** begin our session ***/
session_start();
/*** first check that both the username, password and form token have been sent ***/
if(!isset( $_POST['phpro_username'], $_POST['phpro_password'], $_POST['form_token']))
{
$message = 'Please enter a valid username and password';
}
/*** check the form token is valid ***/
elseif( $_POST['form_token'] != $_SESSION['form_token'])
{
$message = 'Invalid form submission';
}
/*** check the username is the correct length ***/
elseif (strlen( $_POST['phpro_username']) > 20 || strlen($_POST['phpro_username']) < 4)
{
$message = 'Incorrect Length for Username';
}
/*** check the password is the correct length ***/
elseif (strlen( $_POST['phpro_password']) > 20 || strlen($_POST['phpro_password']) < 4)
{
$message = 'Incorrect Length for Password';
}
/*** check the username has only alpha numeric characters ***/
elseif (ctype_alnum($_POST['phpro_username']) != true)
{
/*** if there is no match ***/
$message = "Username must be alpha numeric";
}
/*** check the password has only alpha numeric characters ***/
elseif (ctype_alnum($_POST['phpro_password']) != true)
{
/*** if there is no match ***/
$message = "Password must be alpha numeric";
}
else
{
/*** if we are here the data is valid and we can insert it into database ***/
$phpro_username = filter_var($_POST['phpro_username'], FILTER_SANITIZE_STRING);
$phpro_password = filter_var($_POST['phpro_password'], FILTER_SANITIZE_STRING);
/*** now we can encrypt the password ***/
$phpro_password = sha1( $phpro_password );
/*** connect to database ***/
/*** mysql hostname ***/
$mysql_hostname = 'localhost';
/*** mysql username ***/
$mysql_username = 'mysql_username';
/*** mysql password ***/
$mysql_password = 'mysql_password';
/*** database name ***/
$mysql_dbname = 'phpro_auth';
try
{
$dbh = new PDO("mysql:host=$mysql_hostname;dbname=$mysql_dbname", $mysql_username, $mysql_password);
/*** $message = a message saying we have connected ***/
/*** set the error mode to excptions ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
/*** prepare the insert ***/
$stmt = $dbh->prepare("INSERT INTO phpro_users (phpro_username, phpro_password ) VALUES (:phpro_username, :phpro_password )");
/*** bind the parameters ***/
$stmt->bindParam(':phpro_username', $phpro_username, PDO::PARAM_STR);
$stmt->bindParam(':phpro_password', $phpro_password, PDO::PARAM_STR, 40);
/*** execute the prepared statement ***/
$stmt->execute();
/*** unset the form token session variable ***/
unset( $_SESSION['form_token'] );
/*** if all is done, say thanks ***/
$message = 'New user added';
}
catch(Exception $e)
{
/*** check if the username already exists ***/
if( $e->getCode() == 23000)
{
$message = 'Username already exists';
}
else
{
/*** if we are here, something has gone wrong with the database ***/
$message = 'We are unable to process your request. Please try again later"';
}
}
}
?>
<html>
<head>
<title>PHPRO Login</title>
</head>
<body>
<p><?php echo $message; ?>
</body>
</html>
This submit for takes a simple approach to dealing with the submission by checking each possibility of an error and assigning an error message, or a success message if the user is added. A check is added within the exception code to check if the username already exists. This works because we have applied a unique index to the phpro_username field. If a username already exists within the database, an 23000 SQL error is generated such as this
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'kevin' for key 2
Using the built in exception handling of PHP and PDO we can catch this error and generate our own value for the message. It is worth noting here that error messages such as those generated by exceptions should not be shown to the end users. Such messages should be used for debugging code by the developers. You can see the error messages generated by exceptons using $e->getMessage(). System errors should never be shown to the user and it is recommended you change this to a more user friendly error message such as "We are unable to process your request. Please try again later" or something.
Logging In
Now we can get to the part where a user can finally log in to the system. Like most database interactions, it begins with a form. In this form we need to supply a username and password just as we did with the adduser.php form. Lets take a look:
<head>
<title>PHPRO Login</title>
</head>
<body>
<h2>Login Here</h2>
<form action="login_submit.php" method="post">
<fieldset>
<p>
<label for="phpro_username">Username</label>
<input type="text" id="phpro_username" name="phpro_username" value="" maxlength="20" />
</p>
<p>
<label for="phpro_password">Password</label>
<input type="text" id="phpro_password" name="phpro_password" value="" maxlength="20" />
</p>
<p>
<input type="submit" value="→ Login" />
</p>
</fieldset>
</form>
</body>
</html>
This is a very generic form and this time we POST the form to the login_submit.php file where we will carry out similar checks on the data that we carried out when adding the users. Once again we will check the type and length of the variables submitted, just in case somebody is trying to send us 2 meg text file, or shell code to try to login with. Can you imagine sending that sort of information to the database. When the username and password are successfully validated we check in the database to see if the username and password are correct, if they are we set a SESSION variable and if the values are incorrect, no action is taken. Lets jump into some code...
<?php
/*** begin our session ***/
session_start();
/*** check if the users is already logged in ***/
if(isset( $_SESSION['user_id'] ))
{
$message = 'Users is already logged in';
}
/*** check that both the username, password have been submitted ***/
if(!isset( $_POST['phpro_username'], $_POST['phpro_password']))
{
$message = 'Please enter a valid username and password';
}
/*** check the username is the correct length ***/
elseif (strlen( $_POST['phpro_username']) > 20 || strlen($_POST['phpro_username']) < 4)
{
$message = 'Incorrect Length for Username';
}
/*** check the password is the correct length ***/
elseif (strlen( $_POST['phpro_password']) > 20 || strlen($_POST['phpro_password']) < 4)
{
$message = 'Incorrect Length for Password';
}
/*** check the username has only alpha numeric characters ***/
elseif (ctype_alnum($_POST['phpro_username']) != true)
{
/*** if there is no match ***/
$message = "Username must be alpha numeric";
}
/*** check the password has only alpha numeric characters ***/
elseif (ctype_alnum($_POST['phpro_password']) != true)
{
/*** if there is no match ***/
$message = "Password must be alpha numeric";
}
else
{
/*** if we are here the data is valid and we can insert it into database ***/
$phpro_username = filter_var($_POST['phpro_username'], FILTER_SANITIZE_STRING);
$phpro_password = filter_var($_POST['phpro_password'], FILTER_SANITIZE_STRING);
/*** now we can encrypt the password ***/
$phpro_password = sha1( $phpro_password );
/*** connect to database ***/
/*** mysql hostname ***/
$mysql_hostname = 'localhost';
/*** mysql username ***/
$mysql_username = 'mysql_username';
/*** mysql password ***/
$mysql_password = 'mysql_password';
/*** database name ***/
$mysql_dbname = 'phpro_auth';
try
{
$dbh = new PDO("mysql:host=$mysql_hostname;dbname=$mysql_dbname", $mysql_username, $mysql_password);
/*** $message = a message saying we have connected ***/
/*** set the error mode to excptions ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
/*** prepare the select statement ***/
$stmt = $dbh->prepare("SELECT phpro_user_id, phpro_username, phpro_password FROM phpro_users
WHERE phpro_username = :phpro_username AND phpro_password = :phpro_password");
/*** bind the parameters ***/
$stmt->bindParam(':phpro_username', $phpro_username, PDO::PARAM_STR);
$stmt->bindParam(':phpro_password', $phpro_password, PDO::PARAM_STR, 40);
/*** execute the prepared statement ***/
$stmt->execute();
/*** check for a result ***/
$user_id = $stmt->fetchColumn();
/*** if we have no result then fail boat ***/
if($user_id == false)
{
$message = 'Login Failed';
}
/*** if we do have a result, all is well ***/
else
{
/*** set the session user_id variable ***/
$_SESSION['user_id'] = $user_id;
/*** tell the user we are logged in ***/
$message = 'You are now logged in';
}
}
catch(Exception $e)
{
/*** if we are here, something has gone wrong with the database ***/
$message = 'We are unable to process your request. Please try again later"';
}
}
?>
<html>
<head>
<title>PHPRO Login</title>
</head>
<body>
<p><?php echo $message; ?>
</body>
</html>
Testing For Logged In Users
Now that we can log in, we can test to see if the user is logged in or not when accessing a page on our site. This might be an admin page or a members only page that subscribers only have access to. Lets create a file called members.php and see if we can access it.
<?php
/*** begin the session ***/
session_start();
if(!isset($_SESSION['user_id']))
{
$message = 'You must be logged in to access this page';
}
else
{
try
{
/*** connect to database ***/
/*** mysql hostname ***/
$mysql_hostname = 'localhost';
/*** mysql username ***/
$mysql_username = 'mysql_username';
/*** mysql password ***/
$mysql_password = 'mysql_password';
/*** database name ***/
$mysql_dbname = 'phpro_auth';
/*** select the users name from the database ***/
$dbh = new PDO("mysql:host=$mysql_hostname;dbname=$mysql_dbname", $mysql_username, $mysql_password);
/*** $message = a message saying we have connected ***/
/*** set the error mode to excptions ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
/*** prepare the insert ***/
$stmt = $dbh->prepare("SELECT phpro_username FROM phpro_users
WHERE phpro_user_id = :phpro_user_id");
/*** bind the parameters ***/
$stmt->bindParam(':phpro_user_id', $_SESSION['user_id'], PDO::PARAM_INT);
/*** execute the prepared statement ***/
$stmt->execute();
/*** check for a result ***/
$phpro_username = $stmt->fetchColumn();
/*** if we have no something is wrong ***/
if($phpro_username == false)
{
$message = 'Access Error';
}
else
{
$message = 'Welcome '.$phpro_username;
}
}
catch (Exception $e)
{
/*** if we are here, something is wrong in the database ***/
$message = 'We are unable to process your request. Please try again later"';
}
}
?>
<html>
<head>
<title>Members Only Page</title>
</head>
<body>
<h2><?php echo $message; ?></h2>
</body>
</html>
With the members.php file above, the SESSION variable is checked and if it exists, and if it does, we attempt to get the username from the database. Upon success we can send a greeting to the user or do anything else we like, the important piece is that we have successfully checked if the users is logged in or not.
Logging Out
Up to now, provision is provided to log in and check if a user is logged in. Now the user needs to be able to log out.
Logging out is simply a matter of destroying the session variable which contains the user_id.
Here the same code is taken directly from the PHPRO sessions tutorial Can I destroy a session section.
Create a file called logout.php and insert the following code.
<?php
// Begin the session
session_start();
// Unset all of the session variables.
session_unset();
// Destroy the session.
session_destroy();
?>
<html>
<head>
<title>Logged Out</title>
</head>
<body>
<h1>You are now logged out. Please come again</h1>
</body>
</htl>
With the logout file in place, the job is now to create a Log Out link to the page. Here the initial form file is used.
<html>
<head>
<title>PHPRO Login</title>
</head>
<body>
<?php if( isset( $_SESSION['user_id'] ) ): ?>
<h2>Login Here</h2>
<form action="login_submit.php" method="post">
<fieldset>
<p>
<label for="phpro_username">Username</label>
<input type="text" id="phpro_username" name="phpro_username" value="" maxlength="20" />
</p>
<p>
<label for="phpro_password">Password</label>
<input type="text" id="phpro_password" name="phpro_password" value="" maxlength="20" />
</p>
<p>
<input type="submit" value="→ Login" />
</p>
</fieldset>
</form>
<?php else: ?>
<h2>Logout Here</h2>
<p<a href="logout.php">Log Out Link</p>
<?php endif; ?>
</body>
</html>
In the code above, a simple check has been put in place to check if the user is logged in. If the user is not logged in, the login for is displayed. If the user is already logged in, then the Log Out link is displayed. By clicking on the logout link, the user will now be taken to the logout page.
Summary
With the files above, you should be able to implement a basic, yet secure, PHP and MySQL authentication for your site. You may have noticed the implementation as it stands could use many improvements as there is much code that is duplicated, for example, the database connections, and much of the variable validation. The database connection could be put into its own db_conn.php file and included where needed, this would eliminate re-typing many lines of code and if a change is made to the database settings, only a single file needs to be editted. It is left as an exercise for the user to implement thier own approaches to these issues as the solutions are as varied as the number of coders who implement them. Good Luck and remember these few important final points..