PHPBuilder - Session Handler (w MSSQL)



RSS Twitter
Snippets Other

Session Handler (w MSSQL)

by: TheSage
|
February 19, 2003

Version: 1.0

Type: Full Script

Category: Other

License: GNU General Public License

Description: A include file to replace the standard PHP session handler using MSSQL. Please Note: This file MUST be included before any calls to the session functions. If there are any problems please attempt to rectify them yourself before bugging me. There is no warranty for this software, if you like it, use it, if not don't. :-)



<?
/* ================================================================ *\
|   Filename:				Sesssion_Handler.inc
|   Author:					Corey Scott (corey_s_scott@yahoo.com)
|   Release Date:			2003/02/19
|   Purpose:
|		This function is designed to replace the PHP session 
|	handling functions and allow the redirection of all session data
|	from files (PHP std) to a database.  This has been designed
|	to save the data into a MSSQL database.
|
|	Instructions: Please include this file before and calls to the
|	PHP session functions.
|
| Code to Include this file:
if ( GLOBALS_INC != "Defined" )
	{
	include_once( 'globals.inc' );
	}
\* ================================================================ */

// =================================================================
// File Definition Handle
// =================================================================
define( "SESSION_HANDLER_INC", "Defined" );

// =================================================================
// Globals Definitions
// =================================================================
// DB Servername (Change this to your server)
define( "strDBServer", "NOWHERE" );

/* ================================================================ *\
|  Define Message Types
|
|	Message Value 	Description
|	   0		Non-Logged Message
|	   1		Logged Message
|	   2		Error Message (Logged)		
\* ================================================================ */
// Define Modes
define("MSG_INFO", 0 );
define("MSG_ERR", 1 );

/* ================================================================ *\
|  Define Operational Modes
|
|	Mode No. 	Description
|	   0		Normal Operation (default)
|	   1		Debug Mode		
\* ================================================================ */
// Define Modes
define("Mode_NORMAL", 0 );
define("Mode_DEBUG", 1 );

// =================================================================
// Set up mode switch
$intMode = Mode_Normal;

// =================================================================
// DB Connection Propoerties
$db_host = strDBServer;		// The session database server
$db_name = "Sessions";			// The database name
$db_user = "phpsession";		// The database user
$db_pass = "phpsession";		// The database password

$db_con = 0;					// The database connection
$db_han = 0;					// The database handle
$sess_life = get_cfg_var("session.gc_maxlifetime");	// Set the session lifetime from default.


// =================================================================
// =================================================================
function sys_Msg_Handler( $strMessageText, $intMsgMode )
	{
	// get access global var (mode)
	global $intMode;

	switch ( $intMode )
		{
		case Mode_DEBUG:
			switch ( $intMsgMode )
				{
				case MSG_INFO:
					echo $strMessageText . "<BR>";
					break;

				case MSG_ERR:
					echo $strMessageText . "<BR>";
					break;

				default:
					echo $strMessageText . "<BR>";
					break;
				}
			break;
			
		case Mode_NORMAL:
			switch ( $intMsgMode )
				{
				case MSG_INFO:
					// do nothing ignore the message
					break;

				case MSG_ERR:
					echo $strMessageText . "<BR>";
					break;

				default:
					// do nothing ignore the message
					break;
				}
			break;

		default:
			switch ( $intMsgMode )
				{
				case MSG_INFO:
					// do nothing ignore the message
					break;

				case MSG_ERR:
					echo $strMessageText . "<BR>";
					break;

				default:
					// do nothing ignore the message
					break;
				}
		}
	}

// =================================================================
// =================================================================

function sess_open( $save_path, $session_name )
{
/* ================================================================ *\
|  This function sets up the database connection and makes it 
|  ready for use by the other session functions
\* ================================================================ */

// =================================================================
// Inputs Defined and Explained
// =================================================================

/* ================================================================ *\
|  The inputs are not used here, but provide to confirm to the 
|  standard (backwards compatability)
\* ================================================================ */

// =================================================================
// Main Code
// =================================================================
// define the global variables to be used.
global $db_host, $db_name, $db_user, $db_pass, $db_con, $db_han;

// setup the database connection
$db_con = mssql_connect( $db_host, $db_user, $db_pass );

if ( $db_con == FALSE )
	{
	// db connection failed, write to log and exit
	$strMessageText = "Session DB Connection failed. Cannot connect to " . $db_host . " as " . $db_user;
	sys_Msg_Handler( $strMessageText, MSG_ERR );
	}
else
	{
	// db connection successful notification
	$strMessageText = "Session DB Connection made. Connect to " . $db_host . " as " . $db_user;
	sys_Msg_Handler( $strMessageText, MSG_INFO );

	// try to select the database on connection
	$db_han = mssql_select_db( $db_name, $db_con );
	
	// make sure the database was selected properly.
	if ( $db_han == FALSE )
		{
		// the database could not be selected
		$strMessageText = "Session DB " . $db_name . " could not be selected. ";
		sys_Msg_Handler( $strMessageText, MSG_ERR );
		}
	else
		{
		// the database was selected properly
		$strMessageText = "Session DB " . $db_name . " was selected successfully. ";
		sys_Msg_Handler( $strMessageText, MSG_INFO );
		}
	}

// Return success (any error will have stopped before here)
return TRUE;
}

// =================================================================
// =================================================================

function sess_close()
{
/* ================================================================ *\
|  This function cleans up the session.
\* ================================================================ */

// =================================================================
// Main Code
// =================================================================
// define the global variables to be used.
global $db_con, $db_name;

$q_rslt = mssql_close( $db_con );

// make sure it was closed properly
if ( $q_rslt == FALSE )
	{
	// the database connection was not closed properly
	$strMessageText = "Session DB connection to " . $db_name . " could not be closed. ";
	sys_Msg_Handler( $strMessageText, MSG_INFO );

	return FALSE;
	}
else
	{
	// the database could not be selected
	$strMessageText = "Session DB connection to " . $db_name . " was closed properly. ";
	sys_Msg_Handler( $strMessageText, MSG_INFO );
	}

return TRUE;
}

// =================================================================
// =================================================================

function sess_read( $strKey )
{
/* ================================================================ *\
|  This function retrieves the session data.
\* ================================================================ */

// =================================================================
// Inputs Defined and Explained
// =================================================================

/* ================================================================ *\
|  The input here is the session key (a random alphanumeric string).
\* ================================================================ */

// =================================================================
// Output Defined and Explained
// =================================================================

/* ================================================================ *\
|  The output here is all of the session data sesialized into a string
\* ================================================================ */

// =================================================================
// Main Code
// =================================================================
// define the global variables to be used.
global $db_con, $sess_life;

// make sure the session key is valid
if ( $strKey == "" )
	{
	// the query failed
	$strMessageText = "Session key supplied was blank.";
	sys_Msg_Handler( $strMessageText, MSG_ERR );

	return FALSE;
	}

// build the SQL query
$strQ = "spSessRead @strKey='" . $strKey . "'";

// output query string to the message handler for debug purposes
$strMessageText = "Session DB: Query:" . $strQ;
sys_Msg_Handler( $strMessageText, MSG_INFO );

// execute the query
$q_rslt = mssql_query( $strQ, $db_con );

// make sure the query was successful
if ( $q_rslt == FALSE )
	{
	// the query failed
	$strMessageText = "Session DB query: " . $strQ . " has failed. ";
	sys_Msg_Handler( $strMessageText, MSG_INFO );

	return FALSE;
	}
else
	{
	// the query was a success
	$fetched_row = mssql_fetch_array( $q_rslt );

	// make sure the fetch was ok.
	if ( $fetched_row == FALSE )
		{
		// there was no rows in the result (there should be one)
		$strMessageText = "Session DB: Query " . $strQ . " returned no rows (should return one). ";
		sys_Msg_Handler( $strMessageText, MSG_INFO );

		return FALSE;
		}
	else
		{
		// obtain session data
		$sess_data = $fetched_row["strSessionValue"];
		//echo "Session Data: " . $sess_data . "<BR>";

		// output session data debug purposes
		$strMessageText = "Session Data: " . $sess_data;
		sys_Msg_Handler( $strMessageText, MSG_INFO );
		
		return $sess_data;
		}
	}
}

// =================================================================
// =================================================================

function sess_write( $strKey, $strVal )
{
/* ================================================================ *\
|  This function sets a session variable into the session data.
\* ================================================================ */

// =================================================================
// Inputs Defined and Explained
// =================================================================

/* ================================================================ *\
|  The input here is the session key (a random alphanumeric string)
|  and the new session data string to be recorded
\* ================================================================ */

// =================================================================
// Main Code
// =================================================================
// define the global variables to be used.
global $db_con, $sess_life;

// define the new expiry time 
// Please note: PHP session max lifetime is stored in seconds and the
//	MSSQL has been configured to take minutes, hence the 'x/60'
$expiry = $sess_life / 60;

// validate the session key
if ( $strKey == "" )
	{
	// the query failed
	$strMessageText = "Session key supplied was blank.";
	sys_Msg_Handler( $strMessageText, MSG_ERR );

	return FALSE;
	}

// modify string to allow the use of "'" in the string
$strVal = str_replace( "'", "''", $strVal );

// build the SQL query
$strQ = "spSessWrite @strKey='" . $strKey . "', @intExpiry=" . $expiry . ", @strSessValue='" . $strVal . "'";
//echo $strQ . "<BR>";
// output query string to the message handler for debug purposes
$strMessageText = "Session DB: Query:" . $strQ;
sys_Msg_Handler( $strMessageText, MSG_INFO );

// execute the query
$q_rslt = mssql_query( $strQ, $db_con );

// make sure the query was successful
if ( $q_rslt == FALSE )
	{
	// the query failed
	$strMessageText = "Session DB query has failed. ";
	sys_Msg_Handler( $strMessageText, MSG_INFO );

	return FALSE;
	}
else
	{
	return $q_rslt;
	}
}

// =================================================================
// =================================================================

function sess_destroy( $strKey )
{
/* ================================================================ *\
|  This function destroys a session.
\* ================================================================ */

// =================================================================
// Inputs Defined and Explained
// =================================================================

/* ================================================================ *\
|  The input here is the session key (a random alphanumeric string).
\* ================================================================ */

// =================================================================
// Main Code
// =================================================================
// define the global variables to be used.
global $db_con;

// validate the session key
if ( $strKey == "" )
	{
	// the query failed
	$strMessageText = "Session key supplied was blank.";
	sys_Msg_Handler( $strMessageText, MSG_ERR );

	return FALSE;
	}

// build the SQL query
$strQ = "spSessDelete @strKey='" . $strKey . "'";

// output query string to the message handler for debug purposes
$strMessageText = "Session DB: Query:" . $strQ;
sys_Msg_Handler( $strMessageText, MSG_INFO );

// execute the query
$q_rslt = mssql_query( $strQ, $db_con );

// make sure the query was successful
if ( $q_rslt == FALSE )
	{
	// the query failed
	$strMessageText = "Session DB query has failed. ";
	sys_Msg_Handler( $strMessageText, MSG_INFO );

	return FALSE;
	}
else
	{
	return $q_rslt;
	}
}


// =================================================================
// =================================================================

function sess_gc( $intMaxLifetime )
{
/* ================================================================ *\
|  This function sets a session variable into the session data.
\* ================================================================ */

// =================================================================
// Inputs Defined and Explained
// =================================================================

/* ================================================================ *\
|  The input is not used here, it is provided for back compatability.
\* ================================================================ */

// =================================================================
// Main Code
// =================================================================
// define the global variables to be used.
global $db_con;

// build the SQL query
$strQ = "spSessGC";

// output query string to the message handler for debug purposes
$strMessageText = "Session DB: Query:" . $strQ;
sys_Msg_Handler( $strMessageText, MSG_INFO );

// execute the query
$q_rslt = mssql_query( $strQ, $db_con );

// make sure the query was successful
if ( $q_rslt == FALSE )
	{
	// the query failed
	$strMessageText = "Session DB query has failed. ";
	sys_Msg_Handler( $strMessageText, MSG_INFO );

	return FALSE;
	}
else
	{
	return $q_rslt;
	}
}

// =================================================================
// =================================================================

/* ================================================================ *\
|  This function sets the above functions as the session handling 
|  functions.
\* ================================================================ */

session_set_save_handler( 
			"sess_open",
			"sess_close",
			"sess_read",
			"sess_write",
			"sess_destroy",
			"sess_gc" );

/* ================================================================ *\
|  Below is the SQL code to create the table and stored procedures
\* ================================================================ */

/* ================================================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spSessDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spSessDelete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spSessGC]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spSessGC]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spSessRead]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spSessRead]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spSessWrite]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spSessWrite]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[entSessions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[entSessions]
GO

CREATE TABLE [dbo].[entSessions] (
	[intEntryID] [int] IDENTITY (1, 1) NOT NULL ,
	[dtCreation] [smalldatetime] NOT NULL ,
	[strSessionKey] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[dtExpiry] [smalldatetime] NOT NULL ,
	[strSessionValue] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[status] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[entSessions] WITH NOCHECK ADD 
	CONSTRAINT [PK_entSessions] PRIMARY KEY  CLUSTERED 
	(
		[intEntryID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[entSessions] WITH NOCHECK ADD 
	CONSTRAINT [DF_entSessions_dtCreation] DEFAULT (getdate()) FOR [dtCreation]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE spSessDelete ( @strKey varchar(50) ) AS

DELETE FROM entSessions
WHERE strSessionKey = @strKey
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE spSessGC  AS

-- Calculate the new expiry time
declare @dtNow smalldatetime

-- Setup current time
SET @dtNow = getdate()

DELETE FROM entSessions WHERE dtExpiry < @dtNow
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE spSessRead ( @strKey varchar(50) ) AS

declare @dtNow smalldatetime

SET @dtNow = getdate()

SELECT strSessionValue FROM entSessions WHERE strSessionKey = @strKey  AND dtExpiry >= @dtNow
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE spSessWrite ( @strKey varchar(50), @intExpiry int, @strSessValue text ) AS

declare @intRows as int

-- Calculate the new expiry time
declare @dtNow smalldatetime
declare @dtExpiry smalldatetime

-- Setup current time
SET @dtNow = getdate()

-- This rounds the expiry time down to minutes.  The number below is equiv. to 1 day / 24 hours / 60 minutes. Normally adding one to a Date/Time adds one day.
SET @dtExpiry = @dtNow + ( 0.000694 * @intExpiry )


SELECT @intRows = count(*) FROM entSessions
WHERE strSessionKey = @strKey
AND dtExpiry > @dtNow


if ( @intRows <> 0 )
	begin
		UPDATE entSessions SET dtExpiry = @dtExpiry, strSessionValue = @strSessValue 
		WHERE strSessionKey = @strKey AND dtExpiry >= @dtNow
	end
else
	begin
		INSERT INTO entSessions ( strSessionKey, dtExpiry, strSessionValue ) 
		VALUES ( @strKey,  @dtExpiry, @strSessValue )
	end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
================================================================ */

?>

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:
TheSage

Comment:



Comment:

(Maximum characters: 1200). You have characters left.