Objective : It is very necessary in cluster server environment to develop an application with database based session. Because whenever one server fails and load shifts to another server then session breaks. So, these days database sessions are in existence.
To begin with, we have to create one user (login infromation etc) table and two session tables in the database. In our case we have created these tables in MySQL database. Following are the commands to create table structure for session handler.
In the below mentioned “users” table, we will store user login account information.
Code:
CREATE TABLE users (
user_id int(10) unsigned NOT NULL auto_increment,
login_id varchar(128) NOT NULL,
password char(32) NOT NULL,
acc_creation_date date NOT NULL,
acc_locked_date date default NULL,
pwd_revision_time decimal(3,0) NOT NULL default '30',
PRIMARY KEY (user_id),
UNIQUE KEY login_id (login_id),
);
In the below mentioned “user_session” table, we will store user session related information.
Code:
CREATE TABLE user_session (
id bigint(20) NOT NULL auto_increment,
ascii_session_id varchar(64) default NULL,
logged_in enum('true','false') default NULL,
user_id int(10) unsigned default NULL,
last_impression timestamp NULL default NULL,
created timestamp NOT NULL default '0000-00-00 00:00:00',
invalid_attempts tinyint(2) NOT NULL,
PRIMARY KEY (id),
KEY user_id (user_id)
);
In the below mentioned “session_variable” table, we will store user's run-time variables with their values.
Code:
CREATE TABLE session_variable (
id bigint(20) NOT NULL auto_increment,
session_id bigint(20) NOT NULL,
variable_name varchar(64) character set utf8 default NULL,
variable_value longtext character set utf8,
PRIMARY KEY (id),
KEY session_id (session_id)
);
Now, create a php session class file “e.g class.session.php in our case”. Now create a class “DBSession” in the above file. Declare the following variables in this class as private:-
private $php_session_id; (To declare a php session id)
private $native_session_id; (To declare native session id)
private $dbhandle; (This variable will retain database connection object)
private $logged_in; (This variable will be assigned to decide whether the user login status is true or false)
private $user_id; (This variable will mention the user id of logged in user)
private $session_timeout=600; (This variable will retain the session time out value. This is defined in seconds)
private $session_lifespan=3600; (This variable will be used for total life span of session)
private $invalid_attempts=5; (This variable will count the total number of invalid attempts for login. This will stop spamming or hacking)
Now construct session class with name “DBSession” in the file “class.session.php”. It will now look like
PHP Code:
<?php
class DBSession {
private $php_session_id;
private $native_session_id;
private $dbhandle;
private $logged_in;
private $user_id;
private $session_timeout=600;
private $session_lifespan=3600;
private $invalid_attempts=5;
?>
Now create another php file “mysqlconfig.inc.php” and declare following constants in this file to read database information :-
define(“DBSERVER”, "localhost"); (Mention database ip address here)
define(“DBUSER”, "dbuser"); (Mention database user name here)
define(“DBPASSWORD”, "dbpwd"); (Mention database password here)
define(“DBNAME”, "dbname"); (Mention database name here)
define(“SESSION_TIMEOUT”, 6000); (This variable will retain the session time out value. This is defined in seconds)
define(“SESSION_LIFESPAN”, 36000); (This variable will be used for total life span of session)
define(“INVALID_ATTEMPTS”, 5); (This variable will count the total number of invalid attempts for login. This will stop spamming or hacking)
above file will look like
PHP Code:
<?php
define(“DBSERVER”, "localhost");
define(“DBUSER”, "dbuser");
define(“DBPASSWORD”, "dbpwd");
define(“DBNAME”, "dbname");
define(“SESSION_TIMEOUT”, 6000);
define(“SESSION_LIFESPAN”, 36000);
define(“INVALID_ATTEMPTS”, 5);
?>
Now create the __construct function which will be executed automatically whenever this class will be called
PHP Code:
public function __construct() {
require_once("mysqlconfig.inc.php");
$this->session_timeout=SESSION_TIMEOUT;
$this->session_lifespan=SESSION_LIFESPAN;
$this->invalid_attempts=INVALID_ATTEMPTS;
$this->dbhandle=mysqli_connect(DBSERVER, DBUSER, DBPASSWORD, DBNAME) or die('Can not connect to Database');
$maxlifetime = $this->session_lifespan;
session_set_save_handler(
array(&$this, '_session_open_method'),
array(&$this, '_session_close_method'),
array(&$this, '_session_read_method'),
array(&$this, '_session_write_method'),
array(&$this, '_session_destroy_method'),
array(&$this, '_session_gc_method')
);
if ($_COOKIE["PHPSESSID"]) {
$this->php_session_id=$_COOKIE["PHPSESSID"];
$stmt="SELECT ID FROM user_session WHERE ASCII_SESSION_ID='".$this->php_session_id."' AND ((NOW()-CREATED)<'".$this->session_lifespan."') AND ((NOW()-LAST_IMPRESSION)<='".$this->session_timeout."' OR LAST_IMPRESSION IS NULL)";
$result=mysqli_query($this->dbhandle, $stmt);
if (mysqli_num_rows($result)==0) {
$result=mysqli_query($this->dbhandle, "DELETE FROM user_session WHERE (ASCII_SESSION_ID='".$this->php_session_id."') OR ((NOW()-CREATED)>$maxlifetime)");
$result=mysqli_query($this->dbhandle, "DELETE FROM session_variable WHERE SESSION_ID NOT IN (SELECT ID FROM user_session)");
unset($_COOKIE["PHPSESSID"]);
}
}
session_set_cookie_params($this->session_lifespan);
session_start();
}
In the above function, we used session_set_save_handler() function. This php function reside in server ram and its sub functions are treated as events.
Now, $_COOKIE["PHPSESSID"] is checked, if its true (whether session is already created) then $_COOKIE["PHPSESSID"] value is assigned to php_session_id variable and it will find the ID from user_session table with following command. In the following command, it also checks the session lifespan value and last impression value
PHP Code:
$stmt="SELECT ID FROM user_session WHERE ASCII_SESSION_ID='".$this->php_session_id."' AND ((NOW()-CREATED)<'".$this->session_lifespan."') AND ((NOW()-LAST_IMPRESSION)<='".$this->session_timeout."' OR LAST_IMPRESSION IS NULL)";
If data is not found from above query then it will delete all the session related records which has already completed maximum lifespan and also will delete all the records from session variable table whose sessions do not reside in user_session table and then unset cookies by following commands
PHP Code:
$result=mysqli_query($this->dbhandle, "DELETE FROM user_session WHERE (ASCII_SESSION_ID='".$this->php_session_id."') OR ((NOW()-CREATED)>$maxlifetime)");
$result=mysqli_query($this->dbhandle, "DELETE FROM session_variable WHERE SESSION_ID NOT IN (SELECT ID FROM user_session)");
unset($_COOKIE["PHPSESSID"]);
Now set cookie parameter for max life span value and start the session with following commands
PHP Code:
session_set_cookie_params($this->session_lifespan);
session_start();
Finally our database session class will look like:--
PHP Code:
<?php
class DBSession {
private $php_session_id;
private $native_session_id;
private $dbhandle;
private $logged_in;
private $user_id;
private $session_timeout=600;
private $session_lifespan=3600;
private $invalid_attempts=5;
public function __construct() {
require_once("mysqlconfig.inc.php");
$this->session_timeout=SESSION_TIMEOUT;
$this->session_lifespan=SESSION_LIFESPAN;
$this->invalid_attempts=INVALID_ATTEMPTS;
$this->dbhandle=mysqli_connect(DBSERVER, DBUSER, DBPASSWORD, DBNAME) or die('Can not connect to Database');
$maxlifetime = $this->session_lifespan;
session_set_save_handler(
array(&$this, '_session_open_method'),
array(&$this, '_session_close_method'),
array(&$this, '_session_read_method'),
array(&$this, '_session_write_method'),
array(&$this, '_session_destroy_method'),
array(&$this, '_session_gc_method')
);
if ($_COOKIE["PHPSESSID"]) {
$this->php_session_id=$_COOKIE["PHPSESSID"];
$stmt="SELECT ID FROM user_session WHERE ASCII_SESSION_ID='".$this->php_session_id."' AND ((NOW()-CREATED)<'".$this->session_lifespan."') AND ((NOW()-LAST_IMPRESSION)<='".$this->session_timeout."' OR LAST_IMPRESSION IS NULL)";
$result=mysqli_query($this->dbhandle, $stmt);
if (mysqli_num_rows($result)==0) {
$result=mysqli_query($this->dbhandle, "DELETE FROM user_session WHERE (ASCII_SESSION_ID='".$this->php_session_id."') OR ((NOW()-CREATED)>$maxlifetime)");
$result=mysqli_query($this->dbhandle, "DELETE FROM session_variable WHERE SESSION_ID NOT IN (SELECT ID FROM user_session)");
unset($_COOKIE["PHPSESSID"]);
}
}
session_set_cookie_params($this->session_lifespan);
session_start();
}
private function _session_open_method($save_path, $session_name) {
return(true);
}
public function _session_close_method() {
return(true);
}
private function _session_read_method($id) {
$this->php_session_id=$id;
$result=mysqli_query($this->dbhandle, "SELECT id, logged_in, user_id FROM user_session WHERE ascii_session_id='$id'");
if (mysqli_num_rows($result)>0) {
$row=mysqli_fetch_array($result);
$this->native_session_id=$row["id"];
if ($row["logged_in"]=="true") {
$this->logged_in=true;
$this->user_id=$row["user_id"];
} else {
$this->logged_in=false;
}
} else {
$this->logged_in=false;
$result=mysqli_query($this->dbhandle, "INSERT INTO user_session(ascii_session_id, logged_in, user_id, created) VALUES('$id', 'false', NULL, NOW())");
$result=mysqli_query($this->dbhandle, "SELECT id FROM user_session WHERE ascii_session_id='$id'");
$row=mysqli_fetch_array($result);
$this->native_session_id=$row["id"];
}
return("");
}
public function _session_write_method($id, $sess_data) {
return(true);
}
private function _session_destroy_method($id) {
$result=mysqli_query($this->dbhandle, "DELETE FROM user_session WHERE ascii_session_id='$id'");
return($result);
}
private function _session_gc_method($maxlifetime) {
return(true);
}
public function StartSession() {
if ($this->native_session_id) {
$result=mysqli_query($this->dbhandle, "UPDATE user_session SET LAST_IMPRESSION=NOW() WHERE ID=".$this->native_session_id);
}
}
public function GetUserObject() {
if ($this->logged_in) {
if (class_exists("user")) {
$objUser=new User($this->user_id);
return($objUser);
} else {
return(false);
}
}
}
public function IsLoggedIn() {
return($this->logged_in);
}
public function GetDBConn() {
return($this->dbhandle);
}
public function Login($strLoginId, $strPlainPassword) {
$strLoginId=trim($strLoginId);
$strPlainPassword=trim($strPlainPassword);
$result=mysqli_query($this->dbhandle, "SELECT invalid_attempts FROM user_session WHERE id=".$this->native_session_id);
if (mysqli_num_rows($result)>0) {
$row=mysqli_fetch_array($result);
$invalidattempt=$row["invalid_attempts"];
if ($invalidattempt>=$this->invalid_attempts) {
$this->logged_in=false;
throw new Exception("Session Restricted From This Computer.", 0);
return(false);
}
}
if (strlen($strLoginId)<=0 || strlen($strPlainPassword)<=0) {
throw new Exception("Login Id and Password required for authentication.", 1);
return(false);
}
$stmt="SELECT user_id FROM users WHERE acc_locked_date is NULL and login_id='$strLoginId'";
$result=mysqli_query($this->dbhandle, $stmt);
if (mysqli_num_rows($result)>0) {
$row=mysqli_fetch_array($result);
$qryPwd="SELECT user_id FROM users WHERE user_id='$row[user_id]' and password=md5('$strPlainPassword')";
$rsPwd=mysqli_query($this->dbhandle, $qryPwd);
if(mysqli_num_rows($rsPwd)>0) {
$this->user_id=$row["user_id"];
$this->logged_in=true;
$result=mysqli_query($this->dbhandle, "UPDATE user_session SET logged_in='true', invalid_attempts=0, user_id='".$this->user_id." WHERE id=".$this->native_session_id);
return(true);
} else {
$result=mysqli_query($this->dbhandle, "UPDATE user_session SET invalid_attempts=invalid_attempts+1 WHERE id=".$this->native_session_id);
$this->logged_in=false;
throw new Exception("Invalid Password",1);
return(false);
}
} else {
$result=mysqli_query($this->dbhandle, "UPDATE user_session SET invalid_attempts=invalid_attempts+1 WHERE id=".$this->native_session_id);
$this->logged_in=false;
throw new Exception("LoginID/UserID Mismatch.", 1);
return(false);
}
}
public function LogOut() {
if ($this->logged_in==true) {
$result=mysqli_query($this->dbhandle, "UPDATE user_session SET logged_in='false', user_id=NULL WHERE id=".$this->native_session_id);
$this->logged_in=false;
$this->user_id = '';
$result=mysqli_query($this->dbhandle, "DELETE from session_variable WHERE session_id=".$this->native_session_id);
return(true);
} else {
return(false);
}
}
public function __get($nm) {
$result=mysqli_query($this->dbhandle, "SELECT variable_value FROM session_variable WHERE session_id=".$this->native_session_id." AND variable_name='".$nm."'");
if (mysqli_num_rows($result)>0) {
$row=mysqli_fetch_array($result);
return(unserialize($row["variable_value"]));
return false;
} else {
return(false);
}
}
public function __set($nm, $val='nothing') {
$strSer=serialize($val);
$result=mysqli_query($this->dbhandle, "DELETE FROM session_variable WHERE session_id=".$this->native_session_id." AND variable_name='".$nm."'");
if ($val!='nothing') {
$stmt="INSERT INTO session_variable(session_id, variable_name, variable_value) VALUES(".$this->native_session_id.", '$nm', '$strSer')";
$result=mysqli_query($this->dbhandle, $stmt);
}
mysqli_commit($this->dbhandle);
}
}
?>
Here are two more functions written in the above class one is __set function and other one is __get function
__set function will set the variable and its value in session database table and __get function will return the stored value for given variable.
To use this class we have to include this php file first then apply below mentioned commands to start session and check whether the user is logged in or not
PHP Code:
require_once('class.session.php');
$objSession=new DBSession();
$objSession->StartSession();
if (!$objSession->IsLoggedIn()) {
$objSession->GoToPage("login.php");
exit();
}
In login.php we have simply created tow fields. One is user name and another one is password and posted both the values again into the above class in login function.
If all goes well then calling program will work fine.
Also there are many pages where logged in not required. so in those cases we will start the session by calling StartSession() function but will not check the login status.
Your valuable suggesion are invited for further improvement in the above class. Also you can ask question about the customization of the class according to your specific requirements.
Thanks.
05-08-2011, 02:28 PM
How to write a MySQL/Database based session handler class in PHP
Linear Mode
