After many long hours of trying to figure out why my session state information was being dropped randomly while using a shared hosting company I finally gave up and wrote my own way to persist session information in a database.
My Problem:
Many shared hosting company recycle their IIS virtual memory at a rate that was unpredictable and therefor lose any In-Process session state information without warning. Because most shared hosting companies do not allow you to use the ASP.NET State Service you are really only left the the option of the built in ASP.NET SQLServer Session State mode.
My Solution:
Use a database to store user information. This is not to say you can't use the built in ASP.NET SQLServer session state mode. However, by using that you may find yourself in need of purchasing a separate database instance on your shared service provider once your site gets large enough because of the overhead it has the possibility of creating.
Here is what you need to do to implement this:
Create a database (use whatever name you like).
Now create two tables: User and LoginKey
User Table Columns:
UserID_PK int Primary Key/Identity
Email varchar(75)
Password varchar(20)
LoginKey Table Columns:
LoginKeyID_PK int Primary Key/Identity
LoginKeyGUID varchar(39)
UserID_FK int
DateLoggedIn datetime
IPAddress varchar(15)
Now that you have your tables in place you need a few stored procedures to Get/Update/Delete the data in them.
Stored procedure to Create A User:
-- =============================================
-- Author: Matt Cramer, CramerCode.com
-- Create date: --
-- Description: Create a user account
-- =============================================
CREATEPROCEDURE [dbo].[CreateNewUser]
@Email varchar(75),
@Password varchar(20),
@IPAddress varchar(15)
AS
DECLARE @UserID int
DECLARE @EmailUsed bit
DECLARE @LoginKey uniqueidentifier
SET @LoginKey = NEWID()
SET @EmailUsed = 0
IF EXISTS( SELECT
UserID_PK
FROM
[User] WITH (NOLOCK)
WHERE
Email = @Email)
BEGIN
SET @EmailUsed = 1
SELECT @EmailUsed AS EmailUsed, '' AS LoginKey
END
IF(@EmailUsed = 0)
BEGIN
INSERT INTO [User]
(
Email,
[Password]
)
VALUES
(
@Email,
@Password
)
SET @UserID = SCOPE_IDENTITY()
INSERT INTO LoginKey
(
LoginKeyGUID,
UserID_FK,
IPAddress
)
VALUES
(
@LoginKey,
@UserID,
@IPAddress
)
SELECT @EmailUsed AS EmailUsed, @LoginKey AS LoginKey
END
The CreateNewUser stored procedure does exactly what it says it does. It creates a new user. You would use this on a signup page most likely. You will also notice that it returns two columns: EmailUsed, and LoginKey. EmailUsed lets you know if the email address they are using to sign up with has already been taken and thus a user was not created in the database. The other column, LoginKey, is used to pass between pages and used to retrieve user information as the user clicks around the site. This column is in the form of a GUID and needs to be stored in either the query string or a hidden field.
Note: hidden field is preferred due to being able to encrypt it if you need to via SSL.
Stored procedure to Login A User:
-- =============================================
-- Author: Matt Cramer, CramerCode.com
-- Create date: -----
-- Description: Log in user
-- =============================================
ALTER PROCEDURE [dbo].[LoginUser]
@Email varchar(75),
@Password varchar(20),
@IPAddress varchar(15)
AS
DECLARE @UserID int
IF EXISTS( SELECT
UserID_PK
FROM
[User] WITH (NOLOCK)
WHERE
Email = @Email
AND
[Password] = @Password)
BEGIN
SET @UserID = ( SELECT
UserID_PK
FROM
[User] WITH (NOLOCK)
WHERE
Email = @Email
AND
[Password] = @Password)
DELETE FROM LoginKey
WHERE
UserID_FK = @UserID
INSERT INTO LoginKey
(
UserID_FK,
IPAddress
)
VALUES
(
@UserID,
@IPAddress
)
SELECT
LoginKeyGUID
FROM
LoginKey WITH (NOLOCK)
WHERE
LoginKeyID_PK = SCOPE_IDENTITY()
END
ELSE
BEGIN
SELECT ''
END
The LoginUser stored procedure is simple enough. It takes Email, Password, and IPAddress (I'll talk about this later) as parameters to verify if a user for that email/password combination exists in the User table. If the user is successfully logged in a row will be added to the LoginKey table and the GUID (or login key) will be returned to you. Again this column is in the form of a GUID and needs to be stored in either the query string or a hidden field and passed around the site as the user clicks around.
Stored procedure to Verify a User is logged in:
-- =============================================
-- Author: Matt Cramer, CramerCode.com
-- Create date: ----
-- Description: Verify login
-- =============================================
ALTER PROCEDURE [dbo].[VerifyLogin]
@Key uniqueidentifier,
@IPAddress varchar(15)
AS
IF EXISTS( SELECT
LoginKeyID_PK
FROM
LoginKey With (NOLOCK)
WHERE
LoginKeyGUID = @Key
AND
IPAddress = @IPAddress)
BEGIN
SELECT 'LoggedIn'
END
ELSE
BEGIN
SELECT 'NotLoggedIn'
END
This should be called at the beginning of any request to verify the user is who they say they are. This stored procedure takes a Key (the login key GUID), and the IP address of the user that is making the request. The response from this stored procedure is simply "LoggedIn" or "NotLoggedIn" based on if the user information was found in the LoginKey table.
Okay, so why the IP address parameter? This is just an extra layer of security to help prevent a session hijack. It is not really needed if you are using SSL and storing the login key in a hidden field. However, if you put the login key in the query string it will go across most proxies as clear text even if you are using SSL.
Last stored procedure will Log a User out:
-- =============================================
-- Author: Matt Cramer, CramerCode.com
-- Create date: ----
-- Description: Log out user
-- =============================================
ALTER PROCEDURE [dbo].[LogoutUser]
@Key uniqueidentifier
AS
DELETE FROM LoginKey
WHERE
LoginKeyGUID = @Key
This stored procedure should be called when a user wants to log out.
Putting it all together the logic looks like this:
user comes to site ->
user creates an account (CreateNewUser ) ->
user gets redirected to an account page and clicks around (VerifyLogin) for each page visited ->
user logs out for the day (LogoutUser) ->
user comes back the next day and logs in (LoginUser)
Final thoughts:
This technique was very popular before people got hooked on using the Session object to clog up the IIS pipeline. This technique enforces the idea of serializable information being stored in either the IO or in the database in small chunks.
-Matt
