Storing session data in a database on the server

PHP's default mechanism is started using the session_start() function. You can make a short PHP file that uses the phpinfo() function to show where the session data is stored by default. For example:


<?php phpinfo(); ?>

If you view this file in a browser and look for session.save_path, you will see the location where session data is stored. On Ubuntu 14.04, this is /var/lib/php5/ and this is owned by www-data:www-data. This location is read-writable by user only so it cannot be accessed by a regular user. However, PHP uses a cookie to pass data back and forth between this storage area and the browser. Theoretically, this means that someone could change this cookie with some JavaScript and thus alter the session data.

✴ ✴ ✴ ✴ ✴

The function phpinfo() exposes a lot of information about the server. You should make sure that no php files on a production server call this function. If you created such a file to find out something about the PHP configuration, delete that file before the server is used in production.

✴ ✴ ✴ ✴ ✴

To avoid the potential problem of an attacker using JavaScript to modify a cookie that affect session data, you can store the session data in a database that you create. Then, the session data is passed back and forth between the application and that database. This will make it so that only someone with access to the database could change the session data. The following shows how to set up a database for this purpose.

Setting up the database

Start by creating the following SQL script:


-- sess_storage_init.sql
drop table if exists sessions cascade;
create table sessions(
  id serial,
  sess_id text not null,
  _access integer,
  data text,
  primary key (id)
);

Change into the directory where "sess_storage_init.sql" is saved. Then, create the database. This database will be owned by the superuser, but the superuser will grant some privileges to a regular user so that the regular user's application can use the database for storing session data.


$ psql -U cent285man -W cent285db
Password for user cent285man: 
psql (9.3.9)
Type "help" for help.
                                     
cent285db=# create database sess_storage_db;
CREATE DATABASE
cent285db=# \c sess_storage_db
Password for user cent285man: 
You are now connected to database "sess_storage_db" as user "cent285man".
sess_storage_db=# \i sess_storage_init.sql
psql:sess_storage_init.sql:2: NOTICE:  table "sessions" does not exist, skipping
DROP TABLE
CREATE TABLE
sess_storage_db=# grant select,insert,update,delete on sessions to bob;
GRANT
sess_storage_db=# grant usage on sessions_id_seq to bob;
GRANT
sess_storage_db=# \z

The last command \z will display the privileges for the database relations. This is what you should see:


                                       Access privileges
 Schema |      Name       |   Type   |       Access privileges       | Column access privileges 
--------+-----------------+----------+-------------------------------+--------------------------
 public | sessions        | table    | cent285man=arwdDxt/cent285man+| 
        |                 |          | bob=arwd/cent285man           | 
 public | sessions_id_seq | sequence | cent285man=rwU/cent285man    +| 
        |                 |          | bob=U/cent285man              | 
(2 rows)

Note the privileges that the user "bob" has been granted. Make sure that you set this for every regular user that needs to use this database for session data storage.

Set up Session.php

To make use of this database, every PHP script that needs to maintain the session data, must include the following file, "Session.php":


<?php
  class Session {
    private $pdo;
    
    public function connect() {
      $pdoString = "pgsql:host=localhost dbname=sess_storage_db " .
        "user=bob password=somepass";
      $pdo = new PDO($pdoString);
      return $pdo;
    }
    
    public function _open() {
      $this->pdo = Session::connect();
      if ($this->pdo) {
        return true;
      }
      else {
        return false;
      }
    } // end function _open()
    
    public function _close() {
      $this->pdo = null;
      return true;
    } // end function _close()
    
    public function _read($sess_id) {
      if (!$this->pdo) {
        $this->pdo = Session::connect();
      }
      $sql = "select data from sessions where sess_id=:sess_id";
      $statement = $this->pdo->prepare($sql);
      $statement->execute(array(':sess_id' => $sess_id));
      $num = $statement->rowCount();
      if ($num == 1) {
        $row = $statement->fetch(PDO::FETCH_ASSOC);
        $data = $row["data"];
        return $data;
      }
      else {
        return '';  //  return empty string if no data returned
      }
    } // end function _read()
    
    public function _write($sess_id,$data) {
      $access = time();
      $sql = "select * from sessions where sess_id=:sess_id";
      $statement = $this->pdo->prepare($sql);
      $statement->execute(array(':sess_id' => $sess_id));
      $num = $statement->rowCount();
      if ($num === 0) {
        $sql = "insert into sessions (sess_id, _access, data) values ".
          "(:sess_id,:access,:data)";
        $statement2 = $this->pdo->prepare($sql);
        $myArray = array();
        $myArray[':sess_id'] = $sess_id;
        $myArray[':access'] = $access;
        $myArray[':data'] = $data;
        $statement2->execute($myArray);
        if ($statement2 === false) {
          return false;
        }
        else {
          // new data stored
          return true;
        }
      } // end case of inserting new session data
      else {
        $sql = "update sessions set _access = :access, " .
          "data = :data where sess_id = :sess_id"; 
        $statement2 = $this->pdo->prepare($sql);
        $myArray = array();
        $myArray[':sess_id'] = $sess_id;
        $myArray[':access'] = $access;
        $myArray[':data'] = $data;
        $statement2->execute($myArray);
        if ($statement2 === false) {
          return false;
        }
        else {
          // data updated
          return true;
        }
      } // end case of updating existing record
    } // end function _write()
    
    public function _destroy($sess_id) {
      $sql= "delete from sessions where sess_id=:id";
      $statement = $this->pdo->prepare($sql);
      $statement->execute(array(':id' => $sess_id));
      if ($statement === true) {
        return true;
      }
      else {
        return false;
      }
    } // end function _destroy()
    
    public function _gc($max) {
      $old = time() - $max;
      $sql = "delete from sessions where _access < :old";
      $statement = $this->pdo->prepare($sql);
      $statement->execute(array(':old' => $old));
      if ($statement === true) {
        return true;
      }
      else {
        return false;
      }
    } // end function _gc
  }
  $sess = new Session();
  session_set_save_handler(
    array($sess,'_open'),
    array($sess,'_close'),
    array($sess,'_read'),
    array($sess,'_write'),
    array($sess,'_destroy'),
    array($sess,'_gc')
    );
  register_shutdown_function('session_write_close');
  session_start();
?>

Here is an example of such a simple web application that uses "Session.php" to make the session data get stored in the "sess_storage_db" database:

Example application using database storage of session data

Start by going to the desktop and creating the following directories:


$ cd ~/Desktop
$ mkdir -p session_db_demo/docs/cent285/session_db_demo
$ mkdir -p session_db_demo/docs/include/common
$ mkdir -p session_db_demo/docs/include/session_db_demo

Next, create "Session.php" by copying and pasting the file above. Save this file to ~/Desktop/session_db_demo/docs/include/common/

Change into ~/Desktop/session_db_demo/docs/include/session_db_demo and create the following files:

init.sql:


-- init.sql
drop table if exists users cascade;
drop table if exists items cascade;
drop view if exists users_items_view cascade;

create table users(
  id serial,
  username text unique,
  salt text,
  enc_pass text,
  dept text,
  div text,
  primary key (id)
);

create table items(
  id serial,
  user_id integer references users(id),
  description text,
  primary key (id)
);

create view users_items_view as
  select users.id as uid, users.username, users.dept,
  users.div, items.id as item_id, items.user_id,
  items.description from
  users join items on users.id=items.user_id;

auth_funcs.sql:


-- auth_funcs.sql

create or replace function do_hash(_text text)
  returns text as
  $func$
    begin
      return encode(digest(_text,'sha512'),'hex');
    end;
  $func$
  language 'plpgsql';

create or replace function make_salt(_text text)
  returns text as
  $func$
    begin
      return do_hash(now() || _text);
    end;
  $func$
  language 'plpgsql';
  
create or replace function make_enc_pass(_text text)
  returns text as
  $func$
    begin
      return do_hash(_text || make_salt(_text));
    end;
  $func$
  language 'plpgsql';
  
create or replace function add_user(_user text, _pass text,
  _dept text, _div text)
  returns integer as
  $func$
    begin
      insert into users (username, salt, enc_pass, dept,
        div) values (_user, make_salt(_pass), 
        make_enc_pass(_pass), _dept, _div);
      return currval('users_id_seq');
    end;
  $func$
  language 'plpgsql';
  
create or replace function check_user(_user text, _pass text)
  returns text as
  $func$
    declare
      rec record;
    begin
      select into rec * from users where username=_user;
      if found then
        if do_hash(_pass || rec.salt) = rec.enc_pass then
          return rec.id || ',' || rec.div;
        else
          return 'badpass';
        end if;
      else
        return 'baduser';
      end if;
    end;
  $func$
  language 'plpgsql';

add_data.sql:


-- add_items.sql
  
create or replace function add_item(_user text,_pass text,
  _dept text, _div text, _desc text)
  returns integer as
  $func$
    declare
      rec record;
      _uid integer;
    begin
      select into rec * from users where username=_user;
      if found then
        _uid = rec.id;
      else
        _uid = add_user(_user, _pass, _dept, _div);
      end if;
      insert into items (user_id, description) values
        (_uid, _desc);
      return currval('items_id_seq');
    end;
  $func$
  language 'plpgsql';
  
select add_item('janedoe@gmail.com','janey','deptA','div1',
  'jane doe item 1');
select add_item('janedoe@gmail.com','janey','deptA','div1',
  'jane doe item 2');
select add_item('johndoe@gmail.com','john','deptB','div1',
  'john doe''s item 1');
select add_item('billg@hotmail.com','windows','deptC','div2',
  'bill''s item 1');
select add_item('esr@thrysus2.com','open_source','deptD','div2',
  'eric raymond''s item 1');

all.sql:


-- all.sql
\i init.sql
\i auth_funcs.sql
\i add_data.sql

session_db_demo.php:


<?php
  // session_db_demo.php
  function connect() {
    $pdoString = "pgsql:host=localhost dbname=session_db_demo_db " .
      "user=bob password=somepass";
    $pdo = new PDO($pdoString);
    return $pdo;
  }
?>

Create database for demo application

Next, create the database for this demo application:


$ psql -U cent285man -W cent285db
Password for user cent285man: 
psql (9.3.9)
Type "help" for help.

cent285db=# create database session_db_demo_db owner bob;
CREATE DATABASE
cent285db=# \c session_db_demo_db
Password for user cent285man: 
You are now connected to database "session_db_demo_db" as user "cent285man".
session_db_demo_db=# create extension pgcrypto;
CREATE EXTENSION
session_db_demo_db=# \q

Make sure you quit out of that database. Then, go to the correct directory and login as "bob" to initialize the database by including "all.sql":


$ cd ~/Desktop/session_db_demo/docs/include/session_db_demo/
$ psql -U bob -W session_db_demo_db
Password for user bob: 
psql (9.3.9)
Type "help" for help.

session_db_demo_db=> \i all.sql

Create PHP scripts that uses Session.php

Next, change into ~/Desktop/session_db_demo/docs/cent285/session_db_demo/ and create the following files:

login.php:


<?php
  // login.php
  require_once('../../include/session_db_demo/session_db_demo.php');
  require_once('../../include/common/Session.php');
  $pdo = connect();
  if (!$pdo) { 
    die("Could not connect"); 
  } 
  $loggedIn = FALSE; 
  if (count($_POST) > 0) { 
    $username = $_POST["user"]; 
    $password = $_POST["pass"]; 
    $sql = "select check_user(:user,:pass)";
    $statement = $pdo->prepare($sql);
    $myarray = array();
    $myarray[':user'] = $username;
    $myarray[':pass'] = $password;
    $statement->execute($myarray);
    $row = $statement->fetch(PDO::FETCH_ASSOC);
    $check = $row["check_user"];
    if ($check !== 'baduser' && $check !== 'badpass') { 
      $loggedIn = TRUE; 
      $displayBlock = "Logged in successfully"; 
      $values = explode(",",$check);
      $_SESSION["id"] = intval($values[0]);
      $_SESSION["div"] = $values[1];
      header("Location: showItems.php");
      exit();
    } 
    else {
      var_dump($check);
    }
  } 
  if (empty($_POST["user"]) || empty($_POST["pass"]) || 
    $loggedIn === FALSE) { 
    $displayBlock = "    <form action=\"login.php\" " . 
      "method=\"post\">\n"; 
    $displayBlock .= "      Username: \n"; 
    $displayBlock .= "      <input type=\"text\" " . 
      "name=\"user\"><br />\n"; 
    $displayBlock .= "      Password: \n"; 
    $displayBlock .= "      <input type=\"password\" " . 
      "name=\"pass\"><br />\n"; 
    $displayBlock .= "      <br />\n"; 
    $displayBlock .= "      <input type=\"submit\" value=\"OK\">\n"; 
    $displayBlock .= "    </form>\n"; 
  } 
?> 
<html> 
  <body> 
<?php echo $displayBlock; ?> 
  </body> 
</html>

showItems.php:


<?php
  // showItems.php
  require_once('../../include/session_db_demo/session_db_demo.php');
  require_once('../../include/common/Session.php');
  if (!isset($_SESSION["id"])) {
    header("Location: logout.php");
  }
  $pdo = connect();
  if (!$pdo) {
    die("Could not connect");
  }
  $sql = "select * from users_items_view where div=:div";
  $statement = $pdo->prepare($sql);
  $myarray = array();
  $myarray[':div'] = $_SESSION["div"];
  $statement->execute($myarray);
  $display = "    <table border=\"1\">\n";
  $display .= "      <tr>\n";
  $display .= "        <th>Username</th> <th>Item desc</th>\n";
  $display .= "      </tr>\n";
  while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
    $display .= "      <tr>\n";
    $display .= "        <td>" . $row["username"] . "</td>\n";
    $display .= "        <td>" . $row["description"] . "</td>\n";
    $display .= "      </tr>\n";
  }
  $display .= "    </table><br />\n";
  $display .= "    <a href=\"addItem.php\">Add Item</a><br />\n";
  $display .= "    <a href=\"logout.php\">Logout</a>\n";
?>
<html>
  <body>
<?php echo $display; ?>
  </body>
</html>

addItem.php:


<?php 
  // addItem.php
  require_once('../../include/common/Session.php'); 
  if (!isset($_SESSION["id"])) {
    header("Location: logout.php");
  }
  $id = $_SESSION["id"]; 
  $display = "    Enter item description: <br />\n"; 
  $display .= "    <form method=\"post\" " . 
    "action=\"handleAddItem.php\">\n"; 
  $display .= "     <textarea name=\"desc\" " . 
    "rows=\"6\" cols=\"60\">" . 
    "</textarea><br />\n"; 
  $display .= "      <br />\n"; 
  $display .= "      <input type=\"submit\" " . 
    "value=\"Submit\">\n"; 
  $display .= "    </form>\n"; 
?> 
<html> 
  <body> 
<?php echo $display; ?> 
  </body> 
</html>

handleAddItem.php:


<?php 
  // handleAddItem.php 
  require_once('../../include/session_db_demo/session_db_demo.php');
  require_once('../../include/common/Session.php');
  if (!isset($_SESSION["id"])) {
    header("Location: logout.php");
  }
  $id = $_SESSION["id"]; 
  if (count($_POST) > 0) { 
    $pdo = connect(); 
    if (!$pdo) { 
      die("Could not connect"); 
    } 
    $description = $_POST["desc"]; 
    $sql = "insert into items (user_id,description) " . 
      "values (:id,:desc)";
    $statement = $pdo->prepare($sql);
    $myarray = array();
    $myarray[':id'] = $id;
    $myarray[':desc'] = $description;
    $statement->execute($myarray);
    header("Location: showItems.php"); 
    exit(); 
  }
?>

logout.php:


<?php
  // logout.php
  require_once('../../include/common/Session.php');
  session_destroy();
  header("Location: login.php");
  exit();
?>

Once those files are created, setup so the link so that these files can be viewed by Apache:

Create symbolic link to allow viewing in browser


$ cd /var/www/docs/cent285
$ sudo ln -s ~/Desktop/session_db_demo/docs/cent285/session_db_demo session_db_demo

Start up a browser and navigate to: http://localhost/session_db_demo/login.php

Connect to sess_storage_db to view session data

Before logging in to the application, connect to the sess_storage_db database as "bob":


$ psql -U bob -W sess_storage_db
Password for user bob: 
psql (9.3.9)
Type "help" for help.

sess_storage_db=>

Now, login using janedoe@gmail.com/janey. Query the sess_storage_db table sessions:


sess_storage_db=> select * from sessions;
 id |          sess_id           |  _access   |          data          
----+----------------------------+------------+------------------------
  1 | 73s2cvuj8fhhfu4j2f5vi36333 | 1444470882 | id|i:1;div|s:4:"div1";
(1 row)

If you look back at login.php, look at lines 24-26. Line 24 splits apart the string that is returned by the check_user() function. This string is just the user's id and user's div separated by a comma. Line 24 breaks these data apart on the comma. Line 25 stores the id in $_SESSION["id"]. Line 26 stores the div (division) in $_SESSION["div"]. You can see this data in the sessions table.

Click on the Logout link at the bottom of the items table. Query the sessions table again to see what happens when the user logs out.


sess_storage_db=> select * from sessions;
 id |          sess_id           |  _access   | data 
----+----------------------------+------------+------
  2 | 73s2cvuj8fhhfu4j2f5vi36333 | 1444471145 | 
(1 row)

As can be seen, the data is cleared. If you look back at "logout.php", you can see why. Line 4 in "logout.php" destroys the session, so the data is deleted. If you view the cookies in the browser you will see that the PHPSESSID cookie has the same value as the sess_id field in the sessions table. This field is how the sess_storage_db database "communicates" with the web application.

The only way to change the session data, is by connecting to the sess_storage_db database. An attacker would have to get such a connection and somehow figure out the sess_id to use to manipulate the data. As long as the user logs out, the session is destroyed, so the old sess_id value is no longer valid. The default setting for PHP to let a session last is 24 minutes. So, if the user forgets to logout, the session will be killed after 24 minutes. You can shorten this time, but that would probably have an adverse effect on legitimate users of your application.

You can download a gzipped file containing all the files here.