PHP/MySQL Authentication
Snippet Menu
14
Mon. Jan. 15th, 2007 5:30 PM
MySQL 5.x Stored Routines, PHP Session Values, a little CSS oh my! Complex to set up but easy to impliment. Geared toward security, simplicity (of use) and convenience.
login
+---------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+----------------+
| loginid | int(11) | NO | PRI | NULL | auto_increment |
| loginnm | varchar(64) | NO | | | |
| password | varchar(32) | NO | | | |
| memberid | int(11) | NO | | | |
| createddttm | datetime | NO | | | |
| validateddttm | datetime | YES | | NULL | |
| lastlogindttm | datetime | YES | | NULL | |
| activeflg | bit(1) | NO | | | |
| memberlevel | int(11) | YES | | NULL | |
+---------------+-------------+------+-----+---------+----------------+
sessions
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| phpsessid | varchar(32) | NO | PRI | | |
| loginid | int(11) | YES | | NULL | |
| lastupdatedttm | datetime | NO | | | |
+----------------+-------------+------+-----+---------+-------+
<?
// db.php
// This file is used to connect to the MySQL database
// It also includes some useful/common database functions.
// hard-coded parameters: replace as needed for your setup
$dbhost = "mysql.yourDomain.tld";
$dbuser = "yourDatabaseUser";
$dbpass = "yourPassword";
// dbConnect() function: called prior to making a query
function dbConnect($db="yourDatabaseName") {
global $dbhost, $dbuser, $dbpass;
$dbcnx = @mysql_connect($dbhost, $dbuser, $dbpass, false, 65536)
or die("The site database appears to be down.");
if ($db!="" and !@mysql_select_db($db))
die("The site database is unavailable.");
return $dbcnx;
}
// The dbparse function will format input to help
// prevent SQL injection
function dbparse($value)
{
// Stripslashes
if (get_magic_quotes_gpc()) {
$value=stripslashes($value);
}
// Quote if not integer
if (!is_numeric($value)) {
$value = "'" . mysql_real_escape_string($value) . "'";
}
return $value;
}
// The execsql function is how we call queries.
// NOTE: every query I use is designed to return some values.
// Null or empty recordsets will return an error.
// Also note the hack around MySQL's inability to return user-defined
// error codes from stored routines (the 'unknown table' hack)
function execsql ($qry) {
global $errorCode,
$errorMsg;
$errorCode=0;
$errorMsg="";
dbConnect();
$result = mysql_query($qry);
if(mysql_error()!="")
{
$errorCode = -1;
$errorMsg = mysql_error();
if(substr($errorMsg,0,15) == "Unknown table '")
{
$errorMsg = substr($errorMsg,strpos($errorMsg,"'")+1,(strlen($errorMsg)-strpos($errorMsg,"'"))-2);
}
unset($result);
}
else
{
if(!$result)
{
error("$_SERVER[PHP_SELF]\\nNo Result from query.");
}
if(mysql_num_rows($result)==0)
{
error("$_SERVER[PHP_SELF]\\nInvalid result set from query.");
}
}
return $result;
}
// The "error" function: pops up a window with the error message.
// I normally don't put this here, but I think it will work:
function error($msg) {
?>
<html><head>
<script language="JavaScript">
<!--
alert("<?=$msg?>");
history.back();
//-->
</script>
</head><body></body></html>
<?
exit;
}
?>
<?
// hereiam.php
// This script maintains entries in the sessions
// table of the database. The sessions are used
// for security.
include_once 'path/to/db.php';
$myPhpsessid = isset($_COOKIE['PHPSESSID']) ? $_COOKIE['PHPSESSID'] : '';
if($myPhpsessid!='') {
dbConnect();
$qry=sprintf("call prc_hereiam(%s)",dbparse($myPhpsessid));
$result=mysql_query($qry);
if(!$result)
{
error('HereIAm.php\\nNo Result from Database.');
}
$row = mysql_fetch_row($result);
$sessionLoginId = $row[0];
$sessionMemberLevel = $row[1];
}
?>
begin
declare outLoginId int;
declare outMemberLevel int;
DELETE
FROM sessions
WHERE lastupdatedttm <= date_add(now(),interval -10 minute);
IF((SELECT count(*) FROM sessions WHERE phpsessid=varPHPSessID)>0) then
UPDATE sessions
SET lastupdatedttm = now()
WHERE phpsessid = varPHPSessID;
else
INSERT sessions (
phpsessid,
lastupdatedttm
)
VALUES (
varPHPSessID,
now()
);
end IF;
SELECT login.loginid,
login.memberlevel
INTO outLoginId,
outMemberLevel
FROM login,
sessions
WHERE sessions.loginid = login.loginid
AND sessions.phpsessid=varPHPSessID
AND login.activeflg = 1;
IF(isnull(outLoginId)=1) then
SET outLoginId = 0,
outMemberLevel = 0;
end IF;
SELECT outLoginId,
outMemberLevel;
commit;
end
<?
// secure1.php
// The basic security: user must be logged in and have a security level
// of 1 or higher. The $securityLevel is a hack - there's definately a
// better way to do this. But for pages that require a higher security
// level, this file could be coppied and $securityLevel changed to
// something higher.
include_once 'path/to/hereiam.php';
include_once 'path/to/processloginform.php';
$securityLevel = 1;
if($sessionLoginId==0 || $sessionMemberLevel < $securityLevel) {
if($errorMsg!="") {
?>
<div class="error">
<?=$errorMsg?>
</div>
<?
}
?>
<!--
This form uses div nodes and CSS to control the layout.
See the CSS file to adjust the layout of the form.
-->
<div id="loginformdiv">
<form id="loginform" name="loginform" method="post" action="<?=$_SERVER['PHP_SELF']?>">
<div class="insideform">
<div class="loginformtitle">
<p>Members Login:</p>
</div>
<div class="loginformrow">
<div class="loginformcolleft">
Login Name:
</div>
<div class="loginformcolright">
<input type="text" name="loginname" value="<?=$formloginname?>" />
</div>
</div>
<div class="loginformrow">
<div class="loginformcolleft">
Password:
</div>
<div class="loginformcolright">
<input type="password" name="pass" value="<?=$formpassword?>"/>
</div>
</div>
<div class="loginformrow">
<div class="loginformcolleft">
</div>
<div class="loginformcolright">
<input type="submit" name="submit" value="Login" />
</div>
</div>
</div>
</form>
</div>
<?
die;
// If the user is allowed to see the page, nothing is displayed.
}
?>
#loginformdiv {
float: left;
margin: 20px;
}
#loginformdiv div {
margin: 0px;
padding: 0px;
border: 0px;
}
#loginform{
width: 400px;
border-top: 2px solid #ddd;
border-left: 2px solid #ddd;
border-bottom: 2px solid #999;
border-right: 2px solid #999;
float: left;
}
#loginformdiv .loginformtitle {
background: #346800;
color: #fff;
border-bottom: 1px solid #ddd;
padding-left: 20px;
}
#loginformdiv .loginformrow {
background: #ddd;
float: left;
padding: 3px 0 3px 0;
}
#loginformdiv .loginformcolleft {
width: 150px;
text-align: right;
float: left;
}
#loginformdiv .loginformcolright {
width: 240px;
text-align: left;
float: right;
}
.error {
color: #f00;
background: #eee;
width: 80%;
padding: 2px;
margin: 2px;
border-top: 2px solid #aaa;
border-left: 2px solid #aaa;
border-right: 2px solid #333;
border-bottom: 2px solid #333;
}
<?
// processLoginForm.php
// This PHP file is responsible for processing the
// Login form and setting the value in the Sessions table
// It is called from the secure1.php file.
$postloginname = isset($_POST['loginname']) ? $_POST['loginname'] : '';
$postpassword = isset($_POST['pass']) ? $_POST['pass'] : '';
$postsubmit = isset($_POST['submit']) ? $_POST['submit'] : '';
$myPhpsessid = isset($_COOKIE['PHPSESSID']) ? $_COOKIE['PHPSESSID'] : '';
$formpassword = $postpassword;
$formloginname = $postloginname;
if($postsubmit!="" && $postloginname!="" && $postpassword !="")
{
$qry = sprintf("call prc_login(%s, %s, %s)",
dbparse($postloginname),
dbparse($postpassword),
dbparse($myPhpsessid));
$result=execsql($qry);
if($result)
{
$row = mysql_fetch_row($result);
$sessionLoginId = $row[0];
$sessionMemberLevel = $row[1];
}
}
?>
begin
declare varLoginId int;
SELECT loginid
INTO varLoginId
FROM login
WHERE loginnm = varLoginName
AND password(varPassword) = password
AND activeflg = 1
AND validateddttm IS NOT NULL;
IF (isnull(varLoginId)) then
DROP TABLE `Error: Invalid login name or password`;
end IF;
UPDATE sessions
SET loginid = varLoginId
WHERE phpsessid = varPHPSessId;
IF (isnull(varLoginId)=0) then
UPDATE login
SET lastlogindttm = now()
WHERE loginid = varLoginId;
end IF;
SELECT loginid,
memberlevel
FROM login
WHERE loginid = varLoginId;
commit;
end
Brian Stork
Comments
Voting
Votes Up
bertheymans
ColdKeyboard
dannyboy
explode
i_kenneth
jmardones
lolfejs
Pio
SecondV
shachi
Sonsam
sundaramkumar
wiz1705
zsnoop
Votes Down
-SV
Keep doing the good work!
I am implementing your code and I would know if you could show some information about how you encrypt the password to the database?
My email address is zsnoop@gmail.com
Thanks and best regards
Zs
INSERT INTO login (loginname,password)
VALUES ('$loginname',password('$pass'))
SELECT loginid
FROM login
WHERE loginname = '$loginname'
AND password = password('$pass')
Please see the implementation:
$activation_hash = md5( $username . $password . time() );
// Insert query (has to be adapted for php)
INSERT INTO login (username,password,activation_hash)
VALUES ('$username', MD5('$password') , '$activation_hash' )
// Select query (has to be adapted for php)
SELECT loginid, password, activation_hash
FROM login
WHERE username = '$username'
//Check if the password entered is correct
if (md5($password) == $activation_hash)
{
echo "User Authenticated Correctly";
}
what arguments the code need?
Im realy new in stored procedured.
Stored Routines were introduced in MySQL 5.0, so you need that version or later. The snippet shows the "extra stuff" you need to create the procedure prc_hereiam.
Additional input parameters would be separated by commas.
DROP PROCEDURE if EXISTS `prc_hereiam`;
delimiter //
CREATE PROCEDURE prc_hereiam (varPHPSessID VARCHAR(32))
BEGIN
DECLARE outLoginId INT;
DECLARE outMemberLevel INT;
... rest of the code goes here
COMMIT;
END;
//
delimiter ;
CREATE PROCEDURE prc_procedurename (inputValue INT, inputAnother INT)