Cookies Crumbling?

Managing user sessions across Web farms and platforms continues to be one of the paramount challenges in developing high-availability Web sites.As I explored in "A Recipe for Replacing SessionVariables" (March 2006, InstantDoc ID 49114), using cookies in combination with SQL Server stored procedures and a globally unique identifier (GUID) generator is one way to propagate sessions seamlessly across platforms without the need for resource-intensive session variables. But some users have become distrustful of cookies, and Web developers can make no assumptions about the availability of user cookies to manage Web sessions.

You can still achieve a persistent state without cookies by using SQL Server stored procedures, a GUID generator, and URL parameters. Some developers avoid URL parameters because they're visibly passed from page to page. Instead, they prefer to pass values using hidden fields. Either way, the results aren't secure. Passing any information is a potential security risk. Therefore, the values passed from page to page should be as obscure as possible. I like to use GUIDs because they don't really reveal anything useful to potential hackers but still let the session persist as long as necessary. I'll walk you through the techniques I use to set up a SQL Server-based cookie-less session manager. I'll also discuss how the session manager works and ways to use it.

How to Create a Cookie-less Web Session

Creating a SQL Server-based cookie-less session manager is easy. Here's the four-step process I use:

Step 1: Set up the GUID generator in SQL Server. For this example, I'm using the built-in GUID generator that ships with SQL Server, which is a simple function call named newid(). Keep in mind that uniqueness is guaranteed only on the host machine. However, because I'm setting it up so one database server is managing sessions, this is perfectly suitable for my application. I start by writing a simple stored procedure that generates a new GUID, as you can see in Listing 1.

It's logical to use GUIDs. These are unique, nonsequential values that have no meaning to users but can be tracked in the database just like a user ID. For simplicity, in this example I use the rightmost 12 characters of the 36-character output of the newid() function. If you have a very busy site, you'll probably want to use the entire value, but keep in mind that long values take longer to index. A shorter value improves indexing performance but might not provide sufficient uniqueness for your application.

Step 2: Test the GUID generator. To test the stored procedure, I use an Active Server Pages (ASP) page. Listing 2 shows this page's test code inVBScript. (Web Listing 1 shows a Visual Basic version of this code at http://www.sqlmag.com, InstantDoc ID 93529.) This code opens the connection to the SQL database, then executes the stored procedure that generates the GUID value used to track the user's activity.

In a Web browser, the output from this page will look something like this:

7ED986BFB37C 

Every time the page is loaded, a new value is produced.The newid() function is native to SQL Server and calling it from a precompiled stored procedure adds very little overhead compared to ASP session variables, which consume considerable amounts of server memory.

Step 3: Set up Sessvars table and stored procedures. Next, I create a SQL Server table called Sessvars to store the GUID and my first two session variables. Listing 3 contains the code that creates this table.

After creating the Sessvars table, I need a few stored procedures to update it at various points in my application. The first stored procedure, which Listing 4 shows, appends the GUID that's generated on the first visit to the Web page to the new Sessvars table. This GUID becomes the seed value for subsequent pages. The second stored procedure, which Listing 5 shows, swaps the seed value with another GUID. The purpose of the swap is to keep refreshing the URL string with a new value, thus making it very difficult for a Web user to extrapolate the scheme. The third stored procedure, which Listing 6 shows, selects the latest GUID from the Sessvars table based on the last value used.

Step 4: Create the session manager ASP page. Finally, I combine all these elements to create a functional ASP session manager, as Listing 7 shows. In this code, I use only one connection to the SQL database per page load. Making the first connection to the database is usually the slowest operation in the application. Therefore, I reuse this connection instead of opening a new connection for every call to the database. Closing the connection at the conclusion of the page doesn't immediately close the physical connection, but because I don't know when the user's session will expire, it's important to clean up so that the connection can be properly released when the application goes out of scope.

How the Session Manager Works

The session manager starts by generating a new GUID when a new user visits a page on yourWeb site.The GUID becomes the user's temporary ID.This value is then transmitted to every subsequent Web page as a URL parameter. You can store it in the Sessvars table, then use it when needed to look up additional values, such as the last page visited, the product number just added to a shopping cart, or any number of other user-related data items you want to persist across the session.

For additional security, the session manager generates a new GUID every time a new page is loaded. By frequently replacing the GUID values in the Sessvars table, you'll keep hackers guessing. A hacker would need to actively monitor your site in real time and somehow guess both the database schema and how the GUID could be used against it. By the time this would happen, the GUID would be already replaced with the next value or expired with the session, rendering it useless to any would-be intruders.

Besides using the session manager for managing Web sessions, you can use it to assign pseudo values to items such as product codes. This way, even though the values are passed in plain sight, they mean little to users.This usage involves the use and frequent replacement of nonsequential GUIDs, which I'll discuss shortly.

How to Use the Web Session

To use the new session manager, you need to pass the GUID on every page.To do this, append the GUID to the URL string when the next page is called in code:

  Click her  e to go to next page   

If you don't pass the GUID as a URL parameter on every page, the values you've stored in the Sessvars table will be stranded from the user's session.

The URL method adds a little bit of overhead to your coding because you need to be disciplined about reading and passing the values on every page. However, you can use an include file for this purpose and even add it to your Web template.To do this, you package the code in a separate file, then use the #include directive at the top of the page's code to include the file. Using the HTML example just given, your page's code would look like the following:

   Click here  to go to next page   

In this example, the #include directive tells the compiler to execute the code contained in the sess_mgr.asp file before rendering the page.You should note that the SQL Server 2000 newid() function doesn't generate sequentially ordered GUIDs, so you can't use the GUID itself to determine when a value was added or changed. This benefits our security scheme. However, in SQL Server 2005, you'll find a new GUID function named newsequentialid() that does, as its name implies, generate sequentially ordered GUIDs. The downside to sequentially ordered GUIDs is that it's possible to guess the next GUID if you know the sequence. For this example, you should continue to use newid() in SQL Server 2005, which is still supported.

You'll find it becomes second nature to use the session manager on every page, especially when you derive the concomitant benefit of tracking user-specific inputs that will let you customize the user's interaction with your Web site during the session. Keep in mind that the Sessvars table could quickly grow to an unwieldy size unless managed, so you'll need to set up a job to routinely delete old entries. For example, to delete entries more than 12 hours old, use this stored procedure:

CREATE PROCEDURE del_sessvarsASDELETE FROM sessvars WHERE DateDiff(hh,SVar_Date,Get  Date()) >= 12 

If you have a high-volume site, run this stored procedure frequently, perhaps even once per hour.You might also want to use a value less than 12 hours.This will keep the Sessvars table small and efficient, so it produces no drag on your page views. Also be sure to index the GUID column, as this column gets used every time the Sessvars table is queried or updated.

Don't Assume Your System Is Secure

The session manager involves the use and frequent replacement of nonsequential GUIDs. However, even with this method, your security might still be at risk. For example, if you use dynamic SQL instead of stored procedures, you're providing a road map for potential hackers, whether human or disguised as Internet bots. Admittedly, it takes an extra step to write the stored procedure that deletes old entries, but the extra security makes it worth doing. Don't blithely assume yourWeb site code and database calls are secure. Also, be sure you've taken steps to secure your SQL Server database from escalation and SQL-injection attacks.

Cookies Crumble, Web Sessions Persist

The overall advantage of passing URL strings is to persist a Web session indefinitely. If the user opens your page on Monday, leaves the browser open, then leaves town and comes back on Friday,the"session"will still be active. This is because it isn't really a session at all,but a SQL Server database alternative for keeping track of users as they navigate from page to page, without cookies and without encountering a session timeout.