Using SQL Azure to Store PHP Session Data

Article
10/19/2011

This entry is a re-post of Brian Swan's entry on The Silver Lining blog.

In my last post, I looked at the session handling functionality that is built into the Windows Azure SDK for PHP, which uses Azure Tables or Azure Blobs for storing session data. As I wrote that post, I wondered how easy it would be to use SQL Azure to store session data, especially since using a database to store session data is a common and familiar practice when building distributed PHP applications. As I found out, using SQL Azure to store session data was relatively easy (as I’ll show in this post), but I did run into a couple of small hurdles that might be worth taking note of.

Note: Because I’ll use the SQL Server Drivers for PHP to connect to SQL Azure, you can consider this post to also cover “Using SQL Server to Store PHP Session Data”. The SQL Server Drivers for PHP connect to SQL Azure or SQL Server by simply changing the connection string.

The short story here is that I simply used the session_set_save_handler function to map session functionality to custom functions. The biggest hurdle I ran into was that I had to heed this warning in the session_set_save_handler documentation: “As of PHP 5.0.5 the write and close handlers are called after object destruction and therefore cannot use objects or throw exceptions. The object destructors can however use sessions. It is possible to call session_write_close() from the destructor to solve this chicken and egg problem.” I got around this by putting my session functions in a class and including a __destruct method that called session_write_close(). A smaller hurdle was that I needed to write a stored procedure that inserted a new row if the row didn’t already exist, but updated it if it did exist.

The complete story follows. I’ll assume that you already have a Windows Azure subscription (if you don’t, you can get a free trial subscription here: https://www.microsoft.com/windowsazure/free-trial/). Keep in mind that this code is “proof of concept” code – it needs some refining to be ready for production.

1. Create the database, table, and stored procedure (the stored procedure described above). To keep my PHP code simple, it assumes that you have created a database called SessionsDB with a table called sessions and a stored procedure called UpdateOrInsertSession. (A TODO item is to add the creation of the table and stored procedure to the PHP code, but the creation of the database will have to be done separately.) To create these objects, execute the code below using the SQL Azure Portal or SQL Server Management Studio (details in this article – Overview of Tools to Use with SQL Azure):

Create table:

 CREATE TABLE sessions
 (
     id NVARCHAR(32) NOT NULL,
     start_time INT NOT NULL,
     data NVARCHAR(4000) NOT NULL,
     CONSTRAINT [PK_sessions] PRIMARY KEY CLUSTERED 
     (
         [id]
     )
 )

Create stored procedure:

 CREATE PROCEDURE UpdateOrInsertSession
 ( 
     @id AS NVARCHAR(32), 
     @start_time AS INT, 
     @data AS NVARCHAR(4000)
 )
 AS
 BEGIN    
     IF EXISTS (SELECT id FROM sessions WHERE id = @id)    
         BEGIN       
             UPDATE  sessions        
             SET  data = @data        
             WHERE id = @id      
         END    
     ELSE    
         BEGIN             
             INSERT INTO sessions (id, start_time, data)       
             VALUES ( @id, @start_time, @data )    
         END
 END

One thing to note about the table: the data column will contain all the session data in a serialized form. This allows for more flexibility in the data you store.

2. Add the SqlAzureSessionHandler class to your project. The complete class is attached to this post, but I’ll call out a few things here…

The constructor takes your server ID, username, and password. Formatting the connection options is taken care of, but will need to be changed if you are using SQL Server. (i.e. The username will not require the “@serverId” suffix and your server name will not require the “tcp” prefix and “.database.windows.net” suffix.)

Also note that session_set_save_handler is called in the constructor.

 public function __construct($serverId, $username, $password)
 {    
     $connOptions = array("UID"=>$username."@".$serverId, "PWD"=>$password, "Database"=>"SessionsDB");
     $this->_conn = sqlsrv_connect("tcp:".$serverId.".database.windows.net", $connOptions);
     if(!$this->_conn)
     {
         die(print_r(sqlsrv_errors()));
     }
  
     session_set_save_handler(
                              array($this, 'open'),
                              array($this, 'close'),
                              array($this, 'read'),
                              array($this, 'write'),
                              array($this, 'destroy'),
                              array($this, 'gc')
                              );
 }

The write method serializes and base64 encodes all the session data before writing it to SQL Azure. Note that the InsertOrUpdateSession stored procedure is used here so that new session data is inserted, but existing session data is updated:

 public function write($id, $data)
 {
     $serializedData = base64_encode(serialize($data));
     $start_time = time();
     $params = array($id, $start_time, $serializedData);
     $sql = "{call UpdateOrInsertSession(?,?,?)}";
     
     $stmt = sqlsrv_query($this->_conn, $sql, $params);
     if($stmt === false)
     {
         die(print_r(sqlsrv_errors()));
     }
     return $stmt;
 }

Of course, when session data is read, it must be base64 decoded and unserialized:

 public function read($id)
 {
     // Read data
     $sql = "SELECT data
             FROM sessions
             WHERE id = ?";
  
     $stmt = sqlsrv_query($this->_conn, $sql, array($id));
     if($stmt === false)
     {
         die(print_r(sqlsrv_errors()));
     }
     
     if (sqlsrv_has_rows($stmt))
     {
         $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
         return unserialize(base64_decode($row['data']));
     }
     
     return '';
 }

There are no surprises in the close, destroy, and gc methods. However, note that this __destruct method must be included:

 function __destruct()
 {
     session_write_close(); // IMPORTANT!
 }

3. Instantiate SqlAzureSessionHandler before calling session functions as you normally would. After creating a new SqlAzureSessionHandler object, you can handle sessions as you normally would (but the data will be stored in SQL Azure):

 require_once "SqlAzureSessionHandler.php";
 $sessionHandler = new SqlAzureSessionHandler("serverId", "username", "password");
  
 session_start();
  
 if(isset($_POST['username']))
 {
     $username = $_POST['username'];
     $password = $_POST['password'];
  
     $_SESSION['username'] = $username;
     $_SESSION['time'] = time();
     $_SESSION['otherdata'] = "some other session data";
     header("Location: welcome.php");
 }

That’s it. Hope this is informative if not useful.

Thanks.

-Brian

Share this on Twitter

https://blogs.msdn.com/cfs-file.ashx/__key/communityserver-components-postattachments/00-10-22-76-75/SqlAzureSessionHandler.zip

Share via

Using SQL Azure to Store PHP Session Data

Additional resources