HTTP AUTH with PHP and mySQL

thestudent · 12-05-2005, 02:18 AM

I saw that someone had posted a JavaScript login script topic on the forums, and quite a few people suggested that PHP and mySQL is a much better way to handle user logins. However, no-one posted anything about HOW to do it, so I figured I’d give it a bash.

So here’s a small tutorial on using HTTP AUTH with PHP and mySQL.
Get out your favourite text editor, fire up your local webserver, get a nice drink, and get ready to see just how simple it is.

First we need to set up the database with the info we’re going to need for the scripts, so log into your mySQL admin tool (phpMyAdmin, etc, etc) on your local server and set up a new database (doesn’t really matter what you call it. I usually use “test_db”).

Once that’s done, open up the SQL Query window and put in the following query

CODE
DROP TABLE IF EXISTS Users;
CREATE TABLE Users (
 ID int(11) NOT NULL auto_increment,
 FullName varchar(255) NOT NULL default '',
 Email varchar(255) NOT NULL default '',
 Username varchar(8) NOT NULL default '',
 Password varchar(20) NOT NULL default '',
 LastLoginDateTime text NOT NULL,
 LastLoginAddress varchar(255) NOT NULL default '',
 LastLoginIP text NOT NULL,
 PRIMARY KEY  (ID)
) TYPE=MyISAM;

I won’t be using all those variables in this tutorial, but I find it’s better to have extra fields in case you want to add functionality later on.
Right, now that’s done, we need to put user login information into the mySQL table so our script will work.
So open up the SQL Query window again and put in the following query.

CODE
INSERT INTO Users VALUES
(1,'TestUser','test@localhost','test','test','1212120', '127.0.0.1', '127.0.0.1');

This will create a user with the username “test” and the password “test”.

OK, now for the scripts.
Make a new file and call it config.php (any name will do, just remember what it’s called).
In that file put the following.

CODE
<?php
$dbHost = "localhost";  //change this to the database host
$dbUser = "root";   //change this to the database username
$dbPass = "root";  //change this to the database password
$dbName = "test_db";  //change this to the database name
$userTable = "Users";  
$userField = "Username";
$passField = "Password";
?>

Remember to change anything that needs changing.
Save that file and then create another one called login.php.
In login.php put the following code.

CODE
<?php
 include ("config.php");
 function authenticate() {
  Header("WWW-Authenticate: Basic realm=\"secure login\"");
  echo ("Authentication Failed!\n");
  exit();
 }
 if(!isset($PHP_AUTH_USER)) {
   authenticate();
   echo ("Authorization Failed!\n");
   exit();
 } else {
   $checkLogin = "SELECT ID FROM $userTable WHERE
   $userField='$PHP_AUTH_USER' AND
   $passField='$PHP_AUTH_PW'";
   $db = mysql_pconnect($dbHost, $dbUser, $dbPass);
   mysql_select_db($dbName, $db);
   $result = mysql_query($checkLogin, $db);
   $numrows = mysql_num_rows($result);
   $myrow = mysql_fetch_array($result);
   if ($numrows == 0) {
     authenticate();  
   } else {
     setcookie("UserID", $myrow["ID"]);
     $UserID = $myrow["ID"];
   }
 }
?>

Save that file and create another one called login_test.php.
In login_test.php put the following code

CODE
<?php
include ("login.php");
?>
<html>
<head>
<title>Login Test Page</title>
</head>
<body>
<center>
Login successful.
</center>
</body>
</html>

And that’s it.
To test that it’s all working, crank up your browser and access the login_test.php page you just made. You should be prompted for a username and password. Just type in “test” and “test” and you should see a page that says “Login successful.”
For any PHP page that you want to protect, just add the following code at the very beginning of the file.

CODE
<?php
include ("login.php");
?>

Just in closing, the scripts are a little bit messy and could be cleaned up some.

And if you want to secure your pages even more you could encrypt the passwords in the database.

The basic script is from someone else's tutorial (not on B2L) but i can't for the life of me track it down, and i've added some bits and pieces as well. There are quite a few versions out there and they all pretty much use the same code, so don't be surprised if you see a similar script somewhere else.

Hope this comes in handy for anyone who wants to have user logins on their site.

I might do another tut on making the user add, edit and delete forms for this script, as well as adding the LastLoginIP, LastLoginAddress and LastLoginDateTime updating.

dongan · 12-09-2005, 02:10 AM

Thank you. anyway, can u make it non-bold. hard to read. Only make some points highlighted.

andyl · 01-09-2006, 09:09 AM

ugh, nobody follow this guide, I'm writing up some changes but it will take me a while to finish.