Solved

PHP sessions and MSSQL

Posted on 2004-10-14
4
1,309 Views
Last Modified: 2007-12-19
Im having a problem with PHP and using MSSQL to store sessions....my table in SQL is such...
CREATE TABLE [dbo].[ST_Sessions] (
      [SID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [expiration] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [value] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Heres the code for he sessions:
sessions.inc:
<?PHP

// Session Table

$sess_table = "ST_Sessions";

// Retrieve the session maximum lifetime (found in php.ini)

$lifetime = get_cfg_var("session.gc_maxlifetime");

//=============
// function: mssql_session_open()
// purpose: Opens a persistent server connection and selects the
//    database.
//=============

function mssql_session_open($session_path, $session_name) {

  mssql_pconnect("SQL01", "sa", "mypass")
         or die("Can't connect to mssql server! ");

  mssql_select_db("Dev")
         or die("Can't select mssql sessions database");

} // end mssql_session_open()

//=============
// function: mssql_session_close()
// purpose: Doesn't actually do anything since the server connection is
//    persistent. Keep in mind that although this function
//    doesn't do anything in my particular implementation, I
//    still must define it.
//=============

function mssql_session_close() {

  return 1;

} // end mssql_session_close()

//=============
// function: mssql_session_select()
// purpose: Reads the session data from the database
//=============

function mssql_session_select($SID) {

  GLOBAL $sess_db;
  GLOBAL $sess_table;

  $query = "SELECT value FROM $sess_table
      WHERE SID = '$SID' AND
      expiration > ". time();

  $result = mssql_query($query);

} // end mssql_session_select()

//=============
// function: mssql_session_write()
// purpose: This function writes the session data to the database. If that SID // already exists, then the existing data will be updated.
//=============

function mssql_session_write($SID, $value) {

  GLOBAL $sess_db;
  GLOBAL $sess_table;
  GLOBAL $lifetime;

  $expiration = time() + $lifetime;

  $query = "INSERT INTO $sess_table
      VALUES('$SID', '$expiration', '$value')";

  $result = mssql_query($query, $sess_db);

  if (! $result) :

   $query = "UPDATE $sess_table SET
       expiration = '$expiration',
       value = '$value' WHERE
       SID = '$SID' AND expiration >". time();

   $result = mssql_query($query, $sess_db);

  endif;

} // end mssql_session_write()

//=============
// function: mssql_session_destroy()
// purpose: deletes all session information having input SID (only one row)
//=============

function mssql_session_destroy($sessionID) {

  GLOBAL $sess_table;

  $query = "DELETE FROM $sess_table
      WHERE SID = '$sessionID'";
  $result = mssql_query($query);

} // end mssql_session_destroy()

//=============
// function: mssql_session_garbage_collect()
// purpose: deletes all sessions that have expired.
//=============

function mssql_session_garbage_collect($lifetime) {

  GLOBAL $sess_table;

  $query = "DELETE FROM $sess_table
      WHERE sess_expiration < ".time() - $lifetime;
  $result = mssql_query($query);

  return mssql_affected_rows($result);

} // end mssql_session_garbage_collect()

?>

globals.inc:
<?php
include 'session.inc';

session_set_save_handler("mssql_session_open", "mssql_session_close",
  "mssql_session_select", "mssql_session_write",
  "mssql_session_destroy",
  "mssql_session_garbage_collect");

session_start();

and finally, the page that is causing the error: disclamier.php
<?php

$document_name = "scada_page.php";
REQUIRE 'globals.inc';

$login_pwd = $_REQUEST["password"];
$host_ip = $_SERVER['SERVER_ADDR'];
IF (isset ($_SERVER['HTTP_X_FORWARDED_FOR'])) {
      $client_ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
}
ELSE {
      $client_ip = $_SERVER['REMOTE_ADDR'];
};
$password_query = "SELECT Password, NumberofLogins from ST_Customer_Config_Table WHERE Username = '$login_id';";
......snipped

Anyway, the problem is when I use the login page (I havent added that here as is just a HTML form that passes to the disclamier.php), I get what looks like a DNS error page in IE instead of the disclaimer page.  If I remove all the session stuff for sql and turn session.save_handler back to files, everything works fine....Im at a loss here!!
My setup is Webservers - Redhat9 (PHP5, Apache2), MS-SQL2000
Please help!!
0
Comment
Question by:maunded
  • 2
4 Comments
 
LVL 5

Expert Comment

by:php-webdesign
ID: 12316673
then i think the sql query (or an IF / ELSE) times out, and doesn't get a response back from the server, so it times out, and looks like a DNS error.

Never really worked with this, but will go and look for it.
0
 
LVL 1

Accepted Solution

by:
maunded earned 0 total points
ID: 12316708
It is instant tho, its not like a timeout.
Ive got it working tho, Ill post my code here....
globals.inc:
<?php
require_once('session.inc');
.....
session.inc:
<?php
$sql_table = "Sessions";

$lifetime = get_cfg_var("session.gc_maxlifetime");

function open($session_path, $session_name) {
      
      mssql_pconnect("SQL01", "sa", "mypass")
      or die("Can't connect to mssql server! ");
      
      mssql_select_db("[Dev]")
      or die("Can't select mssql sessions database");
      return (true);
}

function close() {
      
      return true;
      
}

/* Read session data from database */
function read($ses_id) {
      global $sql_table;
      $session_sql = "SELECT ses_value FROM " . $sql_table
      . " WHERE ses_id = '$ses_id'";
      $session_res = mssql_query($session_sql);
      $array = mssql_fetch_array($session_res);
      $ses_data = $array['0'];
      if ($session_res = "") {
            return '';
      }
      else return $ses_data;
}

/* Write new data to database */
function write($ses_id, $ses_data) {
      global $sql_table;
      $find_id_query = "SELECT ses_id from " . $sql_table . " WHERE ses_id = '$ses_id'";
      $result = mssql_query($find_id_query);
      $rows = mssql_num_rows($result);
      if ($rows >= 1) {
            $session_sql = "UPDATE " . $sql_table
            . " SET ses_time='" . time()
            . "', ses_value='$ses_data' WHERE ses_id='$ses_id'";
            $session_res = mssql_query ($session_sql);
            if (!$session_res) {
                  return FALSE;
            }
            elseif ($session_res == "1") {
                  return TRUE;
            }
      }
      else {
            $session_sql = "INSERT INTO " . $sql_table
            . " (ses_id, ses_time, ses_start, ses_value)"
            . " VALUES ('$ses_id', '" . time()
            . "', '" . time() . "', '$ses_data')";
            $session_res = mssql_query ($session_sql);
            if ($session_res == "1") {
            return (true);
            }
            else {
            return (false);
            }
      };
}            
            

/* Destroy session record in database */
function destroy($ses_id) {
      global $sql_table;
      $session_sql = "DELETE FROM " . $sql_table
      . " WHERE ses_id = '$ses_id'";
      $session_res = mssql_query ($session_sql);
      if (!$session_res) {
            return FALSE;
      }         else {
            return TRUE;
      }
}

/* Garbage collection, deletes old sessions */
function garbage($life) {
      global $sql_table;
      global $lifetime;
      $timenow = time();
      $ses_life = $timenow - $lifetime;
    $session_sql = "DELETE FROM " . $sql_table
                     . " WHERE ses_time < $ses_life";
      $session_res = mssql_query ($session_sql);
      if (!$session_res) {
            return FALSE;
      }         else {
            return TRUE;
      }
}

session_set_save_handler("open","close","read","write","destroy","garbage");
session_start();

?>

Thats it!!
0
 
LVL 5

Expert Comment

by:php-webdesign
ID: 12316725
oke mate :-) nice work.... close the question :)

http://www.experts-exchange.com/help.jsp#hs5
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now