A Recipe for Replacing Session Variables

What can you do with one cookie, a couple of stored procedures, and a random number? How about cooking up a medium-duty replacement for session variables? My recipe for tracking user Web sessions relies on the native speed of SQL stored procedures and the fault tolerance of a SQL Server database to deliver fast, reliable performance, and is device and platform independent in distributed processing environments.

An Alternative to Session Variables

Tracking users' Web sessions is necessary in all but the most basic Web-based scenarios.Web browser sessions are, by definition, temporary in nature. A Web session starts when the user opens a browser; when the browser is closed or the session times out from inactivity, the value assigned to the session variable is permanently lost. A user stranded in the middle of a Web transaction due to an interrupted or expired session is at best inconvenienced. At worst, having lost the carefully contemplated contents of his or her shopping cart, the customer simply clicks off the site, never to return.

Other factors can destroy session variables. Web sites in a distributed environment are prone to session variable loss when pages are distributed among servers for load balancing. The temporal nature of session variables isn't a new problem, and numerous alternatives have been developed to resolve it. However, many of the alternatives are language, platform, or device dependent, impose significant overhead, or have inherent security risks.

I offer an alternative to session variables that's independent of the language, platform or device being used and has low overhead. The only requirements for implementing this solution are a universally unique identifier (UUID), a user cookie, and a back-end SQL database. I use Active Server Pages (ASP) for demonstration purposes; however, you can adapt this code to any Web scripting language capable of processing user cookies and calling SQL stored procedures.

It's commonplace for Web developers to store session data in user cookies. But did you know that cookies from some of the largest and most popular Internet sites contain data that can be used to identify users and their personal information? In a Web application, security is job one.Any security analyst worth his or her salt will tell you that the best security is having nothing to hide. Because all Web sessions need to store a small amount of data that is, or could be, exposed to unauthorized users, the best option is to store the data securely. SQL Server does a good job of securing data within the database, but unless you establish some link from the user side to the user's secure data, you can't process user-level database transactions. The trick is to keep the user-side link as inscrutable as possible to prevent, or at least impede, those who would use it for less than noble purposes. If a cookie or other session data that uniquely identifies a user can be read by an unauthorized user, the whole purpose of secure data storage becomes moot.

A smarter approach is to use cookies to store information that only SQL can translate. Some developers choose a user ID to uniquely identify a user in a Web session. They might use a globally unique identifier (GUID) to generate user IDs and let themselves be comforted by the GUID's nonsequential nature. However, a hacker might still be able to use the cookie to impersonate the user on Web pages. Because the user ID is part of a permanent record, it doesn't expire, and using it in a cookie puts time on the hacker's side.

A 6-Step Recipe for Replacing Session Variables

Using a UUID cookie has two advantages over the user ID approach. First, the UUID isn't permanently tied to the database in any way. Second, the Web session expires on a predetermined schedule, and the UUID expires along with it, so you minimize the length of time during which the cookie can be put to unauthorized use and make it difficult to use cookies to access sensitive information.

My UUID cookie recipe has just a few simple ingredients:

Cooking up a solution takes just six steps.

Step 1. Create the Sessvars table to store session variables. Table 1 shows the column names, data types, and data lengths for the SQL table.Add a column for each additional session variable you want to track. For example, you might add a shopping cart ID (for a retail site) or a project ID. Such ID values are typically primary keys that link to other tables. For efficiency and performance, store the smallest amount of data necessary. Don't store any information that identifies the user in the Sessvars table.

Step 2. Generate a UUID that will be used to identify the session and look up session data in the Sessvars table.A UUID is a 128-bit number that identifies a record, object, or entity that's unique and is expected to remain unique until approximately the year 3400. Servers can generate UUIDs, but you should use more than just a random-number algorithm to generate the UUID. To ensure a unique UUID, your generator should create it by using a combination of values. Common values to use in generating UUIDs are a time-stamp combined with the clock ID and node ID, preferably from an IEEE 802 MAC address. MAC addresses should be unique, although some manufacturers are known to reuse them. The generated UUID will be a 36-character alphanumeric string, such as d3179f30-5492-11da-8cd6-0800200c9a66. You can write your own custom UUID generator or use a third-party solution. If you use a third-party component to generate the UUID, you can use the ASP code similar to that in Listing 1.

Step 3. Write the UUID to a user cookie. Although the UUID is stored in a relatively insecure location outside the database, it doesn't contain any information that would identify the user.The following code writes the UUID to a user cookie and sets an expiration date for the cookie only if the user doesn't have a valid, unexpired cookie from a prior session:

Response.Cookies("SessCookie")=      strGuid Response.Cookies("SessCookie")      .Expires = Date + 1 

For additional cookie security, specify both the domain and path when storing the cookie:

Response.Cookies("SessCookie")     .domain = your-domain.com Response.Cookies("SessCookie")     .path = "/virtual/secure" 

where your-domain.com is the name of the domain that will store the cookie and / virtual/secure is the path to the storage location.

Step 4. Add the session cookie record to the Sessvars table. Listing 2 shows ASP code that calls a stored procedure to add the UUID to the Sessvars table. You'll need to use the connection method appropriate for your environment. You can download the code for the stored procedure at the top of the page. Always use a stored procedure to add even a single record to a table. Stored procedures are precompiled and more secure than dynamic SQL.

Step 5. Read the session cookie on every server page and add, update, or retrieve values from the Sessvars table as necessary. Listing 3 shows the code for reading and sending the cookie data to the stored procedure. At first blush, reading every server page might seem burdensome, but the overhead is low compared with storing session data in memory variables on the Web server. Besides, session variables have a tendency to disappear and are fairly heavy users of server resources. Making the SQL database do the work in the form of precompiled stored procedures frees up Web server resources and is generally more efficient.To improve performance when adding or updating data in your Sessvars table, use a data retrieval method such as GetRows, as the code in Listing 3 does. Using GetRows places record-set data in an array, letting you close the SQL connection immediately.

Step 6. Finally, run an automated SQL job that deletes session data every 24 hours or on a specified schedule.

When you follow this recipe, the Web session will expire only when you want it to, even in clustered or nonheterogeneous environments. If users close the browser and reopen it later,their session data remains intact until the UUID cookie expires or you delete the session data in the SQL database. By keeping the session alive in this manner, you can ensure that otherwise volatile data, such as shopping carts or data searches, persists.

Caveats for Using Cookies

Storing session data in a SQL database and retrieving it with a user cookie has advantages and disadvantages.The main advantage is that you can easily make session data persist without using volatile, resource-intensive memory variables. One disadvantage is that you know only what the user is doing but not who he or she is.And if a different user subsequently visits your site from the same machine, the new user will have the same access as the earlier user on that machine until the session expires.

From a security standpoint, the UUID cookie method shouldn't be considered secure on public Web sites that don't require a login. On a secure intranet or extranet, where users are required to log in, you can still use the cookie-to-SQL session manager, and you'll know who the user is from the context of a secure login.

If your Web site requires users to log in and be validated, you can increase Web page security by storing a second UUID in another user cookie. Link the second UUID to the real user ID in the SQL database behind the scenes using a simple translation table, and expire the second UUID on a regular basis. If someone happens to read the cookies from the user's computer or capture packets from your site, the intruder will see only meaningless, nonsequential numbers. By the time a malicious user discovers the cookie and tries to use it to retrieve data, the cookie likely will have expired and be useless. If your UUIDs conform to published specifications and each one is truly unique and universal, they also foil the game of "guess the next ID" because it's impossible to use one UUID to guess or mathematically derive another UUID. See Related Reading for a list of resources about specifications.

Other Basic Assumptions

The UUID cookie method assumes the user allows cookies. Users who turn them off quickly discover they can't conduct much useful business on the public information highway.You can always check for disabled cookies and redirect the user to another page as appropriate.

The UUID cookie approach also assumes that just one instance of your application is running, because switching between multiple browser sessions using the same cookie doesn't work. The other assumptions are that you've hardened your SQL box, secured it properly behind your demilitarized zone (DMZ), and are using stored procedures instead of dynamic SQL.

This approach for tracking user sessions lets you manage Web sessions on a variety of platforms using any SQL database that supports stored procedures. It also overcomes some of the problems inherent in using session variables. All the ingredients you need to implement this method should be readily available regardless of the environment you're working in.