PHP/MySQL Authentication

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.

So that's it. If you include secure1.php on any php page, the user must login to see the page. The user is logged out after 10 minutes of inactivity (the browser won't update after 10 mintues, but if they try to view a secure page, they'll be asked to login again). A secure2.php (or 3.php or 10.php) may also be created and included instead of secure1.php. A user may view pages with a security level equal to or below his own security level. I know it's a lot. I've seen other people put up their "authentication" methods to be cut to shreds. SQL Injection, etc - everyone has their own concerns. This, I feel, is a tidy way to keep things secure. The session cookie is used, but holds no user-identifiable information. You'd need to read the database to find out what the session value means, and even then, you won't get a useful password. Let me know what ya think. Peace.



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