View Issue Details

IDProjectCategoryView StatusLast Update
04204User patches[All Projects] Print Viewpublic2010-06-03 16:16
Reporterdaum Assigned Toc_schmitz  
PrioritynormalSeveritytweak 
Status closedResolutionfixed 
Product Version1.87+ 
Target VersionFixed in Version1.90 
Summary04204: add user sessions to be stored in the database
Description

If you want to use two frontend servers with limesurvey you currently cannot as the user sessions are stored locally on the frontend server, thus you cannot have the servers load balanced. This script will store the user sessions in the database, it is heavily adopted from the Symfony Framework module class. For full description see: http://shout.setfive.com/2010/03/12/limesurvey-with-load-balancers-fixing-the-user-sessions/

below is the php script, and sql.
/**

  • Taken from parts the Symfony package "storage" module
  • @author Ashish Datta<ashish@setfive.com>
  • @author Matt Daum<matt@setfive.com>
  • Setfive Consulting, LLC
    */

class setfiveSession {

private $options = array();
private $sessionIdRegenerated;

public function initialize()
{
$this->options = array('db_table' => 'session',
'db_id_col' => 'sess_id',
'db_data_col' => 'sess_data',
'db_time_col' => 'sess_time');

session_set_save_handler(array($this, 'sessionOpen'),
                         array($this, 'sessionClose'),
                         array($this, 'sessionRead'),
                         array($this, 'sessionWrite'),
                         array($this, 'sessionDestroy'),
                         array($this, 'sessionGC'));    

// start our session
// session_start();

}

/**

  • Closes a session.
  • @return boolean true, if the session was closed, otherwise false
    */
    public function sessionClose()
    {
    // do nothing
    return true;
    }

    /**

  • Opens a session.
  • @param string $path (ignored)
  • @param string $name (ignored)
  • @return boolean true, if the session was opened, otherwise an exception is thrown
  • @throws <b>Exception</b> If a connection with the database does not exist or cannot be created
    */
    public function sessionOpen($path = null, $name = null)
    {
    // we're assuming LimeSurvey all ready has db settings and opened a db connection
    return true;
    }

    /**

  • Destroys a session.
  • @param string $id A session ID
  • @return bool true, if the session was destroyed, otherwise an exception is thrown
  • @throws <b>Exception</b> If the session cannot be destroyed.
    */
    public function sessionDestroy($id)
    {
    // get table/column
    $db_table = $this->options['db_table'];
    $db_id_col = $this->options['db_id_col'];

    // cleanup the session id, just in case
    $id = $this->db_escape($id);

    // delete the record associated with this id
    $sql = "DELETE FROM $db_table WHERE $db_id_col = '$id'";

    if ($this->db_query($sql))
    {
    return true;
    }

    // failed to destroy session
    throw new Exception(sprintf('%s cannot destroy session id "%s" (%s).', get_class($this), $id, mysql_error()));
    }

    /**

  • Cleans up old sessions.
  • @param int $lifetime The lifetime of a session
  • @return bool true, if old sessions have been cleaned, otherwise an exception is thrown
  • @throws <b>Exception</b> If any old sessions cannot be cleaned
    */
    public function sessionGC($lifetime)
    {
    // get table/column
    $db_table = $this->options['db_table'];
    $db_time_col = $this->options['db_time_col'];

    // delete the record older than the authorised session life time
    $lifetime = $this->db_escape($lifetime); // We never know...
    $sql = "DELETE FROM $db_table WHERE $db_time_col + $lifetime < UNIX_TIMESTAMP()";

    if (!$this->db_query($sql))
    {
    throw new Exception(sprintf('%s cannot delete old sessions (%s).', get_class($this), mysql_error()));
    }

    return true;
    }

    /**

  • Reads a session.
  • @param string $id A session ID
  • @return string The session data if the session was read or created, otherwise an exception is thrown
  • @throws <b>Exception</b> If the session cannot be read
    */
    public function sessionRead($id)
    {

    // get table/column
    $db_table = $this->options['db_table'];
    $db_data_col = $this->options['db_data_col'];
    $db_id_col = $this->options['db_id_col'];
    $db_time_col = $this->options['db_time_col'];

    // cleanup the session id, just in case
    $id = $this->db_escape($id);

    // delete the record associated with this id
    $sql = "SELECT $db_data_col FROM $db_table WHERE $db_id_col = '$id'";

    $result = $this->db_query($sql);

    if ($result != false && $this->db_num_rows($result) == 1)
    {
    // found the session
    $data = $this->db_fetch_row($result);

    return $data[0];
    }
    else
    {
    // session does not exist, create it
    $sql = "INSERT INTO $db_table ($db_id_col, $db_data_col, $db_time_col) VALUES ('$id', '', UNIX_TIMESTAMP())";
    if ($this->db_query($sql))
    {
    return '';
    }

    // can't create record
    throw new Exception(sprintf('%s cannot create new record for id "%s" (%s).', get_class($this), $id, mysql_error()));
    }

    }

    /**

  • Writes session data.
  • @param string $id A session ID
  • @param string $data A serialized chunk of session data
  • @return bool true, if the session was written, otherwise an exception is thrown
  • @throws <b>sfDatabaseException</b> If the session data cannot be written
    */
    public function sessionWrite($id, $data)
    {
    // get table/column
    $db_table = $this->options['db_table'];
    $db_data_col = $this->options['db_data_col'];
    $db_id_col = $this->options['db_id_col'];
    $db_time_col = $this->options['db_time_col'];

    // cleanup the session id and data, just in case
    $id = $this->db_escape($id);
    $data = $this->db_escape($data);

    // update the record associated with this id
    $sql = "UPDATE $db_table SET $db_data_col='$data', $db_time_col=UNIX_TIMESTAMP() WHERE $db_id_col='$id'";

    if ($this->db_query($sql))
    {
    return true;
    }

    // failed to write session data
    throw new Exception(sprintf('%s cannot write session data for id "%s" (%s).', get_class($this), $id, mysql_error()));
    }

/**

  • Regenerates id that represents this storage.
  • @param boolean $destroy Destroy session when regenerating?
  • @return boolean True if session regenerated, false if error
  • */
    public function regenerate($destroy = false)
    {
    if ($this->sessionIdRegenerated)
    {
    return;
    }

    $this->sessionIdRegenerated = true;
    $currentId = session_id();
    $newId = session_id();
    $this->sessionRead($newId);

    return $this->sessionWrite($newId, $this->sessionRead($currentId));
    }

    /**

  • Counts the rows in a query result
  • @param resource $result Result of a query
  • @return int Number of rows
    */
    protected function db_num_rows($result)
    {
    return mysql_num_rows($result);
    }

    /**

  • Extracts a row from a query result set
  • @param resource $result Result of a query
  • @return array Extracted row as an indexed array
    */
    protected function db_fetch_row($result)
    {
    return mysql_fetch_row($result);
    }

    /**

  • Executes an SQL Query
  • @param string $query The query to execute
  • @return mixed The result of the query
    */
    protected function db_query($query)
    {
    return @mysql_query($query);
    }

    /**

  • Escapes a string before using it in a query statement
  • @param string $string The string to escape
  • @return string The escaped string
    */
    protected function db_escape($string)
    {
    return mysql_real_escape_string($string);
    }

}

$sfSessionHandler = new setfiveSession();
$sfSessionHandler->initialize();

Here is the SQL.
CREATE TABLE IF NOT EXISTS session (
id int(11) NOT NULL auto_increment,
sess_id varchar(255) NOT NULL,
sess_data text NOT NULL,
sess_time datetime NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

TagsNo tags attached.
Complete LimeSurvey version number (& build)1.87

Activities

c_schmitz

c_schmitz

2010-03-15 21:29

administrator   ~11370

Last edited: 2010-03-15 21:30

View 2 revisions

AdoDB (the db lib that LimeSurvey is using) offers DB-based sessions out of the box which we will more likely implement. Until then we can move this to user patches since it is not a bug.

BTW: You can attach files to issues.

c_schmitz

c_schmitz

2010-04-26 16:58

administrator   ~11659

Implemented based on adodb session handling

Issue History

Date Modified Username Field Change
2010-03-15 20:20 daum New Issue
2010-03-15 21:27 c_schmitz Status new => assigned
2010-03-15 21:27 c_schmitz Assigned To => c_schmitz
2010-03-15 21:29 c_schmitz Note Added: 11370
2010-03-15 21:30 c_schmitz Note Edited: 11370 View Revisions
2010-03-15 21:30 c_schmitz Project Bug reports => User patches
2010-04-26 16:58 c_schmitz Note Added: 11659
2010-04-26 16:58 c_schmitz Status assigned => resolved
2010-04-26 16:58 c_schmitz Fixed in Version => 1.90
2010-04-26 16:58 c_schmitz Resolution open => fixed
2010-06-03 16:16 c_schmitz Status resolved => closed