Cloud Zone is brought to you in partnership with:

After teaching math (and a little computer science) for 14 years, Brian changed careers in 2006 with the idea that getting out of his comfort zone would be good. So now he works as a writer and programmer at Microsoft who specializes in PHP and Windows Azure. Brian is a DZone MVB and is not an employee of DZone and has posted 82 posts at DZone. You can read more from them at their website. View Full User Profile

Using SQL Azure to Store PHP Session Data

10.20.2011
| 4714 views |
  • submit to reddit

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: http://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.

References
Published at DZone with permission of Brian Swan, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)