Using SQL Azure to Store PHP Session Data

Article
10/19/2011

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

SqlAzureSessionHandler.zip

Comments

Anonymous
October 23, 2011
Is it really necessary to base64 encode the session data? Maybe using binary(n), varbinary(n) or image (well, I doubt one has to store 2GB in sessions :) should do it as well and spare us the encoding/decoding part. But I did not check if these types are available on sqlazure, they should :)
Anonymous
November 08, 2011
Pierre- Sorry for the delyed response. No, I don't think it is necessary to base64 encode the session data. And yes, binary, varbinary, and image are all supported in SQL Azure: msdn.microsoft.com/.../ee336233.aspx. -Brian
Anonymous
January 10, 2012
Thx for this article, I also red your article about Handling PHP Sessions in Windows Azure. Your Code above works for me very well. The only problem I see in my case is the limited column size of SQL Azure. In your example you use nvarchar(4000). The maximum is varchar(8000). But my sessions come very close to this size. So I'll go back to table storage were the max row size is up to 64k, I just need more speed performance.
Anonymous
January 10, 2012
To store more than 8000 Bytes per session you can use ZIP compression: <?php mySerialize( $obj ) { return base64_encode(gzcompress(serialize($obj), 9)); } myUnserialize( $txt ) { return unserialize(gzuncompress(base64_decode($txt))); } ?>

Share via

Using SQL Azure to Store PHP Session Data

Comments

Additional resources