PHP MySQL Login Script

Categories

Related Articles

• PHP Pagination Script
• Prevent Duplicate Form Submission
• PHP MySQL Basics Tutorial

Syndicate

PHP MySQL Login Script

Listed under PHP & MySQL

Login and user authentication is the most common feature in any dynamic website. Before we go any further, Download the PHP Login Script. The zip file contains the complete PHP source code of our authentication script and a SQL file to create and populate the required tables. Download the file, extract its contents and run the SQL file to create the members table.

Update (14 Aug 2008): Moved database connection details to config.php file. Edit config.php file to specify your own database connection details.

Update (05 Feb 2008): Fixed a bug in the registration form.

Update: A simple registration form has been added to the download package.

login-form.php page simply contains a form with two fields: login and password, and should be self-explanatory in what it does. login-exec.php script is where all the action is.

PHP Tip : Always try to keep the form and the submission/action page separate. Unless necessary, don’t make pages post information to themselves. I normally append “form” to pages containing the form and append “exec” to the page which handles the submission. This makes the application easier to manage when the application logic gets more complex.

Lets go through the code step by step. In the first few lines of login-exec.php, we simply start a session and open a connection to mysql database.

• PHP sessions tutorial

• PHP MySQL tutorial

Escape special characters like ‘,”,\

In the simplest case, special characters may simply break your query. In a more extreme case, a hacker might use SQL injections to gain access to your application. So it is important that we escape these special characters with a \ (backslash). That is, insert a backslash before each special character.

We can escape special characters (prepend backslash) using mysql_real_escape_string or addslashes functions. In most cases PHP will this do automatically for you. But PHP will do so only if the magic_quotes_gpc setting is set to On in the php.ini file. We first check whether this setting is on or not. If the setting is off, we use mysql_real_escape_string function to escape special characters. If you are using PHP version less that 4.3.0, you can use the addslashes function instead.

A MySQL connection is required before using mysql_real_escape_string() otherwise an error of level E_WARNING is generated.

If the magic quotes setting is on, we do not need escape special characters since PHP has already done it for us. We can check the magic_quotes_gpc by using get_magic_quotes_gpc function.

<?php
if(!get_magic_quotes_gpc()) {
	$login=mysql_real_escape_string($_POST['login']);
}else {
	$login=$_POST['login'];
}
?>

A simple function to escape special characters

You can use the function below to clean and prepare data for queries. The function goes through the following steps:

1. Trims the string to remove leading and trailing spaces
2. If you set the second parameter as true, it will also encode all characters which have HTML character entity equivalents.
3. The function then checks for PHP version. If version is greater than or equal to 4.3.0, its uses the mysql_real_escape_string() function. Otherwise it uses addslashes() function.

1. Since the mysql_real_escape_string() only works if there is a connection to the MySQL server, we first check whether we are connected to MySQL server by using the mysql_ping() function.

<?php
function clean($str, $encode_ent = false) {
	$str  = @trim($str);
	if($encode_ent) {
		$str = htmlentities($str);
	}
	if(version_compare(phpversion(),'4.3.0') >= 0) {
		if(get_magic_quotes_gpc()) {
			$str = stripslashes($str);
		}
		if(@mysql_ping()) {
			$str = mysql_real_escape_string($str);
		}
		else {
			$str = addslashes($str);
		}
	}
	else {
		if(!get_magic_quotes_gpc()) {
			$str = addslashes($str);
		}
	}
	return $str;
}
?>

Query the database

Next we formulate the query which will test whether a user with this login and password exists.

Note that we are not storing passwords in the database as plain text. Instead we are storing the md5 hash of the password. Use md5 function to create a 32 character hash of any string. md5 is one way encryption. That is, once the password is encrypted, there is no way to decrypt it.

So if a md5 hash can not be decrypted, how do we compare the user submitted password with the one in the database? The answer is that we simply generate a md5 hash of the user submitted password and then compare this hash to the one stored in the database.

<?php
$qry="SELECT member_id FROM members WHERE login='$login' 
AND passwd='".md5($_POST['password'])."'";
$result=mysql_query($qry);
?>

The query will return a result set with a single row if the login details are correct and zero rows if the login details are incorrect. Use mysql_num_rows to find out the number of rows in the result set and hence determine whether the login details were correct or not.

Store authentication status in session

Once we know that the login details are correct, we need to store this information somewhere, so that the subsequent pages know that the user has been authenticated successfully. We use PHP session for this purpose.

Retrieve the member’s ID from the result set and store it in the session as SESS_MEMBER_ID. Subsequent pages will just need to test for the existence of SESS_MEMBER_ID in the session to verify the authentication status of the user. After storing the member ID in the session, redirect the user to the member-index.php page.

<?php
if(mysql_num_rows($result)>0) {
	//Login Successful
	//Regenerate session ID to
	//prevent session fixation attacks
	session_regenerate_id();
	$member=mysql_fetch_assoc($result);
	$_SESSION['SESS_MEMBER_ID']=$member['member_id'];
	//Write session to disc
	session_write_close();
	header("location: member-index.php");
	exit();
}
?>

If the login fails, redirect the user to login-failed.php page.

Preventing session fixation attacks

Once we have ascertained that the user supplied login details are correct, we store his ID in a session variable named SESS_MEMBER_ID. But we before we do that, we call the session_regenerate_id() function. This function generates a new session ID while keeping intact any information stored in the session.

How to authenticate individual pages

As mentioned above, the presence or absence of SESS_MEMBER_ID in the session will tell us whether the user is logged in or not. If a variable names SESS_MEMBER_ID exists in the session, then the user has been logged in and authenticated. I have moved this logic to a separate PHP script, auth.php

<?php
//Start session
session_start();
//Check whether the session variable
//SESS_MEMBER_ID is present or not
if(!isset($_SESSION['SESS_MEMBER_ID']) || 
	(trim($_SESSION['SESS_MEMBER_ID'])=='')) {
	header("location: access-denied.php");
	exit();
}
?>

Now we can just include the auth.php file in any page we want to password protect. See member-index.php and member-profile.php page for examples.

How to logout the user

To logout the user, simply unset the SESS_MEMBER_ID variable. See the logout.php script for example.

<?php
//Start session
session_start();
//Unset the variable SESS_MEMBER_ID stored in session
unset($_SESSION['SESS_MEMBER_ID']);
?>

PHP login script

Download the Login Script to get the full source code for the above examples.

Here is a brief description of the main files:

• login-form.php – The login form
• login-exec.php – This script queries the database to check the login credentials
• auth.php – This script checks whether the user is logged in or not. Include this script at the top of any page you want to password protect.
• member-index.php – Sample password protected page
• member-profile.php – Sample password protected page
• register-form.php – Registration form for creating new user accounts.

• register-exec.php – Performs input validation and create new user account.