<?
// 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">

</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>
<?
}
?>

<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