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.