In and Around the Coding World

Innvo.com

A Simple PHP/MySQL Authentication Script for Logins

Creating a login area with PHP and MySQL is very easy! The script provided here can be easily customized should you wish to have an area of your site behind a login. The script uses PHP and MySQL and can be adapted to suit your needs.

Apply the following SQL to an existing database:

CREATE TABLE IF NOT EXISTS `userlist` (
  `id` smallint(5) unsigned NOT NULL,
  `username` varchar(32) NOT NULL,
  `password` binary(16) NOT NULL,
  `session` binary(16) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `session` (`session`)
) ENGINE=MyISAM;
 
INSERT INTO userlist (id,username,password,session) VALUES ('','innvo',UNHEX(MD5('innvo')),UNHEX(MD5('innvo')));
 

See the script comments to get an idea of how the code works. I have created a single account where both the username and password are 'innvo'.

<?php
 
// Enter your MySQL username, password and database name here
mysql_connect('localhost','root','root') or die(mysql_error());
mysql_select_db('stuff') or die(mysql_error());
 
function user_login()
{
// This form displays until the user successfully logs in
$form = '<div style="border:1px dotted #CDE;padding:15px;" align="center">
<form method="post">
	<h4>Login Area</h4>
	<p>Username: <input type="text" name="username" size="15" /></p>
	<p>Password: <input type="password" name="password" size="19" /></p>
	<p><input type="submit" value="Login" />
</form>
</div>';
	if(isset($_POST['username'],$_POST['password'])) 
	{
	// username and password submitted, check the database
	$query = mysql_query('SELECT id,UPPER(HEX(password)) AS password
		FROM userlist 
		WHERE username = \''.mysql_real_escape_string($_POST['username']).'\'') or die(mysql_error());
 
		if(($user = mysql_fetch_array($query,MYSQL_ASSOC)) && strtoupper(md5($_POST['password'])) == $user['password'])
		{
		// Create cookie
		$user_cookie = strtoupper(md5(uniqid(rand(),true))); 
		setcookie('user',$user_cookie,false,'/',false);
		// Update database with unique cookie value for further authentication, then re-direct user (who is now logged in)
		mysql_query('UPDATE userlist SET session = UNHEX(\''.$user_cookie.'\') WHERE id = '.$user['id']) or die(mysql_error());
		header('Location: http://'.$_SERVER['SERVER_NAME'].$_SERVER['REQUEST_URI']);
		exit(0);
		}
		else
			die('<p>Invalid Username and password.</p>'.$form); // Invalid login
	}
	elseif(isset($_COOKIE['user']))
	{
	// user cookie exists, check to see if it matches a login session in the database
	$query = mysql_query('SELECT id
		FROM userlist 
		WHERE session = UNHEX(\''.mysql_real_escape_string($_COOKIE['user']).'\')');
		if(!$user = mysql_fetch_array($query,MYSQL_ASSOC))
			die('<p>Previous login has expired.</p>'.$form); // Invalid (or expired) session
	return $user;
	}
	else
		die($form); 
}
 
$user = user_login();
// User is logged in here, you can now reference the users account by using $user['id']
print_r($user);
 
?>
 

Some notes regarding the script:

• The PRIMARY KEY column is a SMALLINT which means a maximum of 65535 values. If you want to increase that, use a MEDIUMINT or INT column.
• Use the HEX() and UNHEX() MySQL functions to switch between binary and text representations of the password and session values.
• A UNIQUE index is present on the username and session columns. This is to prevent more than one user with the same username and/or session cookie. The indexes also make lookups more quick, while converting the table from MyISAM to MEMORY would make it even faster.
• I avoided adding an index on the password and vouched for allowing PHP to authenticate. Adding an INDEX on password would increase overhead and wouldn't be recommended unless lots of login requests are happening (in practice, most users will just login once during a session.)
• If you use an add-on script to create new users, ensure you enter a random string for the password and session fields, otherwise someone sending a blank cookie can gain access to your login area. Evaluate the script to see how you can create random MD5 values.
• If you require the login form within a template, only basic PHP knowledge is required to edit this script.
• Alter the cookie if you wish to change what part of a site and/or how long a user is authenticate for. If you want some kind of timeout within a browser session, you may want to add a MySQL value such as 'lastpageview' to determine how long a user has been inactive for.

Previous Article
A Simple PHP/XML Sitemap GeneratorNext Article
Storing Websites in Memory Using PHP

© Innvo.com | Privacy