Secure Authentication against a MySQL database

Code Wizard, 12-30-2006

This is a secure method of authenticating users with a username and password against a mysql database.

also has scripts for simple user management, very simple to modify to your liking.

I did not include an add user script with this as it is a very simple thing to do and the one I have adds things to two seperate databases.

ok first of all I split common things used on a number on scripts into into includes which I give a .inc extention to and tell apache to treat them as php so you may want to do things a little differently but this will give you a good idea of what to do.

I have to mention here that the actuall auth.inc file was written by a friend.
I just wrote my stuff around it.

-- begin auth.inc --

<?php

$id = "SplatNET";

if(!isset($PHP_AUTH_USER)) {
Header("WWW-Authenticate: Basic realm="$id"");
Header("HTTP/1.0 401 Unauthorized");
require('error.inc');
exit;
}

$name = $PHP_AUTH_USER;
$pass = $PHP_AUTH_PW;
require("connect.inc");
$query = "select * from auth where username='$name' && realm='$id'";
$result = mysql_db_query("splat", $query);

if(mysql_num_rows($result) == 0) {
Header("WWW-Authenticate: Basic realm="$id"");
Header("HTTP/1.0 401 Unauthorized");
require('error.inc');
exit;
}

$active = mysql_result($result,0,"active");
if($active == 'no') {

?>
<HTML><HEAD>
<TITLE>404 Not Found</TITLE>
</HEAD><BODY>
<H1>Not Found</H1>
The requested URL
<? echo $REQUEST_URI; ?>
was not found on this server.
</BODY></HTML>
<?php
exit;
}
?>

-- end auth.inc --

of course the database name would need to be modified to suit your own database.

and you can change the html code to spit out something else if the user exists but is not active.

-- begin connect.inc --

<?php mysql_connect("localhost", "webuser", ""); ?>

-- end connect.inc --

you may need to change the host and user to suit your server.. and possibly add a password in the empty ""

-- begin error.inc --

Error: Unautherised Access

-- end error.inc --

as you can see that is just an error message, you can put whatever you like in there.

-- begin table structure for auth table --

CREATE TABLE auth (
id smallint(6) DEFAULT '0' NOT NULL auto_increment,
username varchar(16) DEFAULT '' NOT NULL,
lastname tinyblob,
firstname tinyblob,
password varchar(16),
realm varchar(16),
active char(3),
PRIMARY KEY (id),
UNIQUE id (id),
UNIQUE username (username)
);

-- end table structure for auth table --

the reason for the realm in the table is so that you can have one table in one database for a number of realms.. or more to the point you might want to have a different realm for the admin users as opposed to member type users and that would allow you to store the users in the one table.

the id field is just something I always add so I can remove entries with the id number for that entry.

-- example entry for auth table --

insert into auth (username, lastname, firstname, password, realm, active) values ('splat','Ski','Splat','mypassword','SplatNET','ye s');

-- end example entry --

-- begin usermanage.php --
<?php include("auth.inc"); ?>
<?php

if ($PHP_AUTH_USER != "splat") {
Header("WWW-Authenticate: Basic realm="PastaNET Secure"");
Header("HTTP/1.0 401 Unauthorized");
echo "Access Denied!";
exit;
}

if ($PHP_AUTH_PW != "mypassword") {
Header("WWW-Authenticate: Basic realm="PastaNET Secure"");
Header("HTTP/1.0 401 Unauthorized");
echo "Access Denied!";
exit;
}

if ($activate) {
include("connect.inc");
$query1 = "UPDATE auth SET active='yes' where id='$id'";
$result1 = mysql_db_query("splat", $query1);

if ($result1) {
echo "";
echo "$user activated";
echo " <a href="$PHP_SELF">Back</a>";
echo "";
exit;
} else {
echo "";
echo "Error: Unknown Error";
echo " <a href="$PHP_SELF">Back</a>";
echo "";
exit;
}
}

if ($deactivate) {

include("connect.inc");
$query2 = "UPDATE auth SET active='no' where id='$id'";
$result2 = mysql_db_query("splat", $query2);

if ($result2) {
echo "";
echo "$user deactivated";
echo " <a href="$PHP_SELF">Back</a>";
echo "";
exit;
} else {
echo "";
echo "Error: Unknown Error";
echo " <a href="$PHP_SELF">Back</a>";
echo "";
exit;
}

}

if ($delete) {

include("connect.inc");
$query3 = "delete from auth where id='$id'";
$result3 = mysql_db_query("splat", $query3);

if ($result3) {
echo "";
echo "$user deleted";
echo " <a href="$PHP_SELF">Back</a>";
echo "";
exit;
} else {
echo "";
echo "Error: Unknown Error";
echo " <a href="$PHP_SELF">Back</a>";
echo "";
exit;
}

}

echo "<html>";
echo "<head>";
echo "<title>User Management</title>";
echo "</head>";
echo "<body>";
echo "<form method="post" action="$PHP_SELF">";
echo "<table border="1">";
echo "<tr><th>Username</th><th>Real Name</th><th>Activated</th></tr>";

include("connect.inc");
$query = "SELECT * FROM auth";
$result = mysql_db_query("splat", $query);

if ($result) {
while ($r = mysql_fetch_array($result)) {
$id = $r["id"];
$username = $r["username"];
$lastname = $r["lastname"];
$firstname = $r["firstname"];
$activated = $r["active"];
if ($activated == "yes") {
echo "<tr><td>$username</td><td>$lastname, $firstname</td><td>$activated</td><td><a href="$PHP_SELF?deactivate=yes&id=$id&user=$userna me">Deactivate</a></td><td><a href="$PHP_SELF?delete=yes&id=$id">Delete</a></td></tr>";
} elseif ($activated == "no") {
echo "<tr><td>$username</td><td>$lastname, $firstname</td><td>$activated</td><td><a href="$PHP_SELF?activate=yes&id=$id">Activate</a></td><td><a href="$PHP_SELF?delete=yes&id=$id">Delete</a></td></tr>";
}
}
}

mysql_free_result($result);
echo "</table>";
echo "</body>";
echo "</html>";

?>

-- end usermanage.php --

if there are any problems with any of that email me and I'll sort it out..

Splat

Enjoyed this post?

Subscribe Now to receive new posts via Email as soon as they come out.

Comments

Post your comments

About Us | Contact us | Privacy Policy | Terms & Conditions