How to use MySQL-based sessions

From Web Services Wiki

Jump to: navigation, search



Your application makes use of PHP's session mechanism, but it loses session information between requests. Since Stanford has multiple web servers, different requests may be directed to different servers, and session information is often lost as a result.


If you have access to MySQL, you should store sessions in a database. Visit Stanford MySQL Service to register your group, department, or service for database access. At this time Stanford does not support MySQL for individual user accounts.

Use a MySQL-based storage mechanism for your sessions to ensure that session information does not get lost between requests.

Create a MySQL table to store session data

First, create a table to store your session data with the SQL code below.

CREATE TABLE php_sessions (
     sessionid varchar(40) BINARY NOT NULL DEFAULT '',
     expiry int(10) UNSIGNED NOT NULL DEFAULT '0',
     value text NOT NULL,
     PRIMARY KEY  (sessionid)

Add session handling code to your application

Store this code in a file that will be included in all scripts that require the use of sessions. Be sure to include it after any third party scripts which may have their own custom session handlers. We suggest storing this file in a non-web accessible location for security reasons. If for some reason the PHP interpreter were to fail, the database connection information would be able to be accessed via the web unless it were stored outside of the web root.

 * Code to use MySQL-based sessions
$STANFORD_DB      = 'databasename';
$STANFORD_DB_USER = 'databaseuser';
$STANFORD_DB_PASS = 'password';
// Include custom session handler functions stored on the server
include_once '/etc/php5/init/sessions.php';

Sample usage

Below is an example of how to use this recipe.

// Include other scripts
// Include script containing the code shown above, with the credentials and name of your application's database
// Use sessions as you normally would
// Here, the data stored in 'var' is written to the database transparently, using our custom session handler
$_SESSION['var'] = 'value';


Why use MySQL-based sessions?

The web infrastructure at Stanford consists of multiple web servers which do not share session data with each other. For this reason, sessions may be lost between requests. Using MySQL effectively counteracts this problem, as all session data is directed to and from the database server rather than the web cluster. Storing sessions in a database also has the effect of added privacy and security, as accessing the database requires authentication. We suggest that all web developers at Stanford with access to MySQL use this method for session handling.

What if I don't have access to MySQL?

The standard file-based sessions which PHP uses by default are too unreliable on Stanford's web infrastructure for the reasons stated in "Why use MySQL-based sessions?" As a result, we suggest against using PHP sessions when the application does not have access to a database. If your application must maintain state between requests, seek an alternative such as cookies.

Is there anything else that I have to do to use MySQL sessions?

No. The sessions.php file that is stored on the server simply overrides all of the default underlying session functionality. After including this file, use sessions as you would normally. All of the database-related work is done transparently in the background.

Where should I include the custom session handler?

Include the custom session handler just before the session code in your application. This is especially important when using third party open source packages, some of which handle sessions in their own way. Including sessions.php after third party scripts ensures that MySQL-based sessions will be enabled.


Personal tools