document.write('');
document.write('login
');
document.write('+---------------+-------------+------+-----+---------+----------------+
');
document.write('| Field | Type |
Null |
Key |
Default | Extra |
');
document.write('+---------------+-------------+------+-----+---------+----------------+
');
document.write('| loginid | int
(11) | NO | PRI |
NULL | auto_increment |
');
document.write('| loginnm | varchar
(64) | NO | | | |
');
document.write('| password | varchar
(32) | NO | | | |
');
document.write('| memberid | int
(11) | NO | | | |
');
document.write('| createddttm | datetime | NO | | | |
');
document.write('| validateddttm | datetime | YES | |
NULL | |
');
document.write('| lastlogindttm | datetime | YES | |
NULL | |
');
document.write('| activeflg | bit
(1) | NO | | | |
');
document.write('| memberlevel | int
(11) | YES | |
NULL | |
');
document.write('+---------------+-------------+------+-----+---------+----------------+
');
document.write('
');
document.write('sessions
');
document.write('+----------------+-------------+------+-----+---------+-------+
');
document.write('| Field | Type |
Null |
Key |
Default | Extra |
');
document.write('+----------------+-------------+------+-----+---------+-------+
');
document.write('| phpsessid | varchar
(32) | NO | PRI | | |
');
document.write('| loginid | int
(11) | YES | |
NULL | |
');
document.write('| lastupdatedttm | datetime | NO | | | |
');
document.write('+----------------+-------------+------+-----+---------+-------+
');
document.write('
');
document.write('
<?');
document.write('
// db.php');
document.write('
// This file is used to connect to the MySQL database');
document.write('
// It also includes some useful/common database functions.');
document.write('
');
document.write('
// hard-coded parameters: replace as needed for your setup');
document.write('
$dbhost =
"mysql.yourDomain.tld";
');
document.write('
$dbuser =
"yourDatabaseUser";
');
document.write('
$dbpass =
"yourPassword";
');
document.write('
');
document.write('
// dbConnect() function: called prior to making a query');
document.write('
function dbConnect
($db=
"yourDatabaseName") {');
document.write('
global $dbhost,
$dbuser,
$dbpass;
');
document.write('
$dbcnx = @
mysql_connect($dbhost,
$dbuser,
$dbpass,
false,
65536)');
document.write(' or
die("The site database appears to be down.");
');
document.write('
if ($db!=
"" and !@
mysql_select_db($db))');
document.write('
die("The site database is unavailable.");
');
document.write('
return $dbcnx;
');
document.write('
}');
document.write('
');
document.write('
// The dbparse function will format input to help');
document.write('
// prevent SQL injection');
document.write('
function dbparse
($value)');
document.write('
{');
document.write('
// Stripslashes');
document.write('
if (get_magic_quotes_gpc()) {');
document.write('
$value=
stripslashes($value);
');
document.write('
}');
document.write('
// Quote if not integer');
document.write('
if (!
is_numeric($value)) {');
document.write('
$value =
"\'" .
mysql_real_escape_string($value) .
"\'";
');
document.write('
}');
document.write('
return $value;
');
document.write('
}');
document.write('
');
document.write('
// The execsql function is how we call queries.');
document.write('
// NOTE: every query I use is designed to return some values.');
document.write('
// Null or empty recordsets will return an error.');
document.write('
// Also note the hack around MySQL\'s inability to return user-defined');
document.write('
// error codes from stored routines (the \'unknown table\' hack)');
document.write('
function execsql
($qry) {');
document.write('
global $errorCode,
');
document.write('
$errorMsg;
');
document.write('
$errorCode=
0;
');
document.write('
$errorMsg=
"";
');
document.write('
');
document.write(' dbConnect
();
');
document.write('
$result =
mysql_query($qry);
');
document.write('
if(mysql_error()!=
"")');
document.write('
{');
document.write('
$errorCode =
-1;
');
document.write('
$errorMsg =
mysql_error();
');
document.write('
if(substr($errorMsg,
0,
15) ==
"Unknown table \'")');
document.write('
{');
document.write('
$errorMsg =
substr($errorMsg,
strpos($errorMsg,
"\'")+1,
(strlen($errorMsg)-
strpos($errorMsg,
"\'"))-2);
');
document.write('
}');
document.write('
unset($result);
');
document.write('
}');
document.write('
else');
document.write('
{');
document.write('
if(!
$result)');
document.write('
{');
document.write(' error
("$_SERVER[PHP_SELF]\\nNo Result from query.");
');
document.write('
}');
document.write('
if(mysql_num_rows($result)==
0)');
document.write('
{');
document.write(' error
("$_SERVER[PHP_SELF]\\nInvalid result set from query.");
');
document.write('
}');
document.write('
}');
document.write('
return $result;
');
document.write('
}');
document.write('
');
document.write('
// The "error" function: pops up a window with the error message.');
document.write('
// I normally don\'t put this here, but I think it will work:');
document.write('
function error
($msg) {');
document.write('
?>');
document.write(' <html><head>
');
document.write('
<script language=
"JavaScript">
');
document.write(' <!--
');
document.write(' alert
("<?=$msg?>");
');
document.write(' history.back
();
');
document.write('
//-->');
document.write('
</script>');
document.write(' </head><body></body></html>
');
document.write('
<?');
document.write('
exit;
');
document.write('
}');
document.write('
?>');
document.write('
');
document.write('
<?');
document.write('
// hereiam.php');
document.write('
// This script maintains entries in the sessions');
document.write('
// table of the database. The sessions are used');
document.write('
// for security.');
document.write('
');
document.write('
include_once \'path/to/db.php\';
');
document.write('
');
document.write('
$myPhpsessid =
isset($_COOKIE[\'PHPSESSID\']) ?
$_COOKIE[\'PHPSESSID\'] :
\'\';
');
document.write('
if($myPhpsessid!=
\'\') {');
document.write(' dbConnect
();
');
document.write('
$qry=
sprintf("call prc_hereiam(%s)",dbparse
($myPhpsessid));
');
document.write('
$result=
mysql_query($qry);
');
document.write('
if(!
$result)');
document.write('
{');
document.write(' error
(\'HereIAm.php\\nNo Result from Database.\');
');
document.write('
}');
document.write('
$row =
mysql_fetch_row($result);
');
document.write('
');
document.write('
$sessionLoginId =
$row[0];
');
document.write('
$sessionMemberLevel =
$row[1];
');
document.write('
');
document.write('
}');
document.write('
?>');
document.write('
');
document.write('begin
');
document.write('
');
document.write('declare outLoginId int;
');
document.write('declare outMemberLevel int;
');
document.write('
');
document.write('DELETE
');
document.write(' FROM sessions
');
document.write(' WHERE lastupdatedttm <= date_add(now(),interval -10 minute);
');
document.write('
');
document.write('IF((SELECT count(*) FROM sessions WHERE phpsessid=varPHPSessID)>0) then
');
document.write(' UPDATE sessions
');
document.write(' SET lastupdatedttm = now()
');
document.write(' WHERE phpsessid = varPHPSessID;
');
document.write('else
');
document.write(' INSERT sessions (
');
document.write(' phpsessid,
');
document.write(' lastupdatedttm
');
document.write(' )
');
document.write(' VALUES (
');
document.write(' varPHPSessID,
');
document.write(' now()
');
document.write(' );
');
document.write('end IF;
');
document.write('
');
document.write('SELECT login.loginid,
');
document.write(' login.memberlevel
');
document.write(' INTO outLoginId,
');
document.write(' outMemberLevel
');
document.write(' FROM login,
');
document.write(' sessions
');
document.write(' WHERE sessions.loginid = login.loginid
');
document.write(' AND sessions.phpsessid=varPHPSessID
');
document.write(' AND login.activeflg = 1;
');
document.write('
');
document.write('IF(isnull(outLoginId)=1) then
');
document.write(' SET outLoginId = 0,
');
document.write(' outMemberLevel = 0;
');
document.write('end IF;
');
document.write('
');
document.write('SELECT outLoginId,
');
document.write(' outMemberLevel;
');
document.write('
');
document.write('commit;
');
document.write('
');
document.write('end
');
document.write('
');
document.write('
<?');
document.write('
// secure1.php');
document.write('
// The basic security: user must be logged in and have a security level');
document.write('
// of 1 or higher. The $securityLevel is a hack - there\'s definately a ');
document.write('
// better way to do this. But for pages that require a higher security');
document.write('
// level, this file could be coppied and $securityLevel changed to');
document.write('
// something higher.');
document.write('
');
document.write('
include_once \'path/to/hereiam.php\';
');
document.write('
include_once \'path/to/processloginform.php\';
');
document.write('
');
document.write('
$securityLevel =
1;
');
document.write('
');
document.write('
if($sessionLoginId==
0 ||
$sessionMemberLevel <
$securityLevel) {');
document.write('
if($errorMsg!=
"") {');
document.write('
?>');
document.write('<div
class=
"error">
');
document.write('
<?=
$errorMsg?>
');
document.write('</div>
');
document.write('
<?');
document.write('
}');
document.write('
?>');
document.write('<!--
');
document.write('This form uses div nodes and CSS to control the layout.
');
document.write('See the CSS
file to adjust the layout of the form.
');
document.write('-->
');
document.write('<div id=
"loginformdiv">
');
document.write(' <form id=
"loginform" name=
"loginform" method=
"post" action=
"<?=$_SERVER[\'PHP_SELF\']?>">
');
document.write(' <div
class=
"insideform">
');
document.write(' <div
class=
"loginformtitle">
');
document.write(' <p>Members Login:</p>
');
document.write(' </div>
');
document.write(' <div
class=
"loginformrow">
');
document.write(' <div
class=
"loginformcolleft">
');
document.write(' Login Name:
');
document.write(' </div>
');
document.write(' <div
class=
"loginformcolright">
');
document.write(' <input type=
"text" name=
"loginname" value=
"<?=$formloginname?>" />
');
document.write(' </div>
');
document.write(' </div>
');
document.write(' <div
class=
"loginformrow">
');
document.write(' <div
class=
"loginformcolleft">
');
document.write(' Password:
');
document.write(' </div>
');
document.write(' <div
class=
"loginformcolright">
');
document.write(' <input type=
"password" name=
"pass" value=
"<?=$formpassword?>"/>
');
document.write(' </div>
');
document.write(' </div>
');
document.write(' <div
class=
"loginformrow">
');
document.write(' <div
class=
"loginformcolleft">
');
document.write(' </div>
');
document.write(' <div
class=
"loginformcolright">
');
document.write(' <input type=
"submit" name=
"submit" value=
"Login" />
');
document.write(' </div>
');
document.write(' </div>
');
document.write(' </div>
');
document.write(' </form>
');
document.write('</div>
');
document.write('
');
document.write('
<?');
document.write('
die;
');
document.write('
// If the user is allowed to see the page, nothing is displayed.');
document.write('
}');
document.write('
?>');
document.write('
');
document.write('#loginformdiv {
');
document.write('float: left;
');
document.write('margin: 20px;
');
document.write('}
');
document.write('#loginformdiv div {
');
document.write('margin: 0px;
');
document.write('padding: 0px;
');
document.write('border: 0px;
');
document.write('}
');
document.write('#loginform{
');
document.write('width: 400px;
');
document.write('border-top: 2px solid #ddd;
');
document.write('border-left: 2px solid #ddd;
');
document.write('border-bottom: 2px solid #999;
');
document.write('border-right: 2px solid #999;
');
document.write('float: left;
');
document.write('}
');
document.write('#loginformdiv .loginformtitle {
');
document.write('background: #346800;
');
document.write('color: #fff;
');
document.write('border-bottom: 1px solid #ddd;
');
document.write('padding-left: 20px;
');
document.write('}
');
document.write('#loginformdiv .loginformrow {
');
document.write('background: #ddd;
');
document.write('float: left;
');
document.write('padding: 3px 0 3px 0;
');
document.write('}
');
document.write('#loginformdiv .loginformcolleft {
');
document.write('width: 150px;
');
document.write('text-align: right;
');
document.write('float: left;
');
document.write('}
');
document.write('#loginformdiv .loginformcolright {
');
document.write('width: 240px;
');
document.write('text-align: left;
');
document.write('float: right;
');
document.write('}
');
document.write('.error {
');
document.write('color: #f00;
');
document.write('background: #eee;
');
document.write('width: 80%;
');
document.write('padding: 2px;
');
document.write('margin: 2px;
');
document.write('border-top: 2px solid #aaa;
');
document.write('border-left: 2px solid #aaa;
');
document.write('border-right: 2px solid #333;
');
document.write('border-bottom: 2px solid #333;
');
document.write('}
');
document.write('
');
document.write('
<?');
document.write('
// processLoginForm.php');
document.write('
// This PHP file is responsible for processing the');
document.write('
// Login form and setting the value in the Sessions table');
document.write('
// It is called from the secure1.php file.');
document.write('
');
document.write('
$postloginname =
isset($_POST[\'loginname\']) ?
$_POST[\'loginname\'] :
\'\';
');
document.write('
$postpassword =
isset($_POST[\'pass\']) ?
$_POST[\'pass\'] :
\'\';
');
document.write('
$postsubmit =
isset($_POST[\'submit\']) ?
$_POST[\'submit\'] :
\'\';
');
document.write('
$myPhpsessid =
isset($_COOKIE[\'PHPSESSID\']) ?
$_COOKIE[\'PHPSESSID\'] :
\'\';
');
document.write('
');
document.write('
$formpassword =
$postpassword;
');
document.write('
$formloginname =
$postloginname;
');
document.write('
');
document.write('
if($postsubmit!=
"" &&
$postloginname!=
"" &&
$postpassword !=
"")');
document.write('
{');
document.write('
$qry =
sprintf("call prc_login(%s, %s, %s)",
');
document.write(' dbparse
($postloginname),
');
document.write(' dbparse
($postpassword),
');
document.write(' dbparse
($myPhpsessid));
');
document.write('
$result=execsql
($qry);
');
document.write('
');
document.write('
if($result)');
document.write('
{');
document.write('
$row =
mysql_fetch_row($result);
');
document.write('
$sessionLoginId =
$row[0];
');
document.write('
$sessionMemberLevel =
$row[1];
');
document.write('
}');
document.write('
}');
document.write('
');
document.write('
?>');
document.write('
');
document.write('begin
');
document.write('
');
document.write('declare varLoginId int;
');
document.write('
');
document.write('SELECT loginid
');
document.write(' INTO varLoginId
');
document.write(' FROM login
');
document.write(' WHERE loginnm = varLoginName
');
document.write(' AND password(varPassword) = password
');
document.write(' AND activeflg = 1
');
document.write(' AND validateddttm IS NOT NULL;
');
document.write('
');
document.write('IF (isnull(varLoginId)) then
');
document.write(' DROP TABLE `Error: Invalid login name or password`;
');
document.write('end IF;
');
document.write('
');
document.write('UPDATE sessions
');
document.write(' SET loginid = varLoginId
');
document.write(' WHERE phpsessid = varPHPSessId;
');
document.write('
');
document.write('IF (isnull(varLoginId)=0) then
');
document.write(' UPDATE login
');
document.write(' SET lastlogindttm = now()
');
document.write(' WHERE loginid = varLoginId;
');
document.write('end IF;
');
document.write('
');
document.write('SELECT loginid,
');
document.write(' memberlevel
');
document.write(' FROM login
');
document.write(' WHERE loginid = varLoginId;
');
document.write('
');
document.write('commit;
');
document.write('end
');
document.write('
');
document.write('');
document.write('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.');
document.write('');
document.write('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.');
document.write('');
document.write('Let me know what ya think. Peace. ');
document.write('
');