Home arrow ASP arrow Page 2 - Session Replacement in ASP
ASP

Session Replacement in ASP


An alternative to using ASP's session object is to build your own using a database. In this article Rogier shows us how...

Author Info:
By: Rogier Doekes
Rating: 5 stars5 stars5 stars5 stars5 stars / 65
June 18, 2003
TABLE OF CONTENTS:
  1. · Session Replacement in ASP
  2. · Creating the Database
  3. · Session Check
  4. · The Login Page
  5. · Conclusion

print this article
SEARCH DEVARTICLES

Session Replacement in ASP - Creating the Database
(Page 2 of 5 )

In order to make the login and session check work, you only need one table in the database.
The table below shows its schema. I named the table tbl_users:

Table field

Data type

Description

SessionID (PK)

Uniqueidentifier

This value will be used to communicate with the database and will be valid for the duration of the session

UsID

Varchar(20)

User name

Password

Varchar(20)

User’s password

LastUpdate

Smalldatetime

Will be updated with the current date and time each time you check the validy of the session

We also need two stored procedures. Usp_checkSessionID is used to check the validity of the sessionID. In order to stay close to the session functionality, this procedure checks the last time this stored procedure was called (ie. Page refresh or redirect) and will give back a value of –1 if the idle time is more than the allowed idle time or if the sessionID does not exist in the database. If it all goes well, a value of 0 is given back to the application.

CREATE PROCEDURE usp_CheckSessionID
 @sessionID UNIQUEIDENIFIER
AS
IF EXISTS (
 SELECT 1
 FROM tbl_users
 WHERE sessionID = @sessionID
 AND  DATEDIFF(n, LastUpdate, GETDATE()) <= 20
)
BEGIN
 UPDATE tbl_users SET
LastUpdate = GETDATE()
 WHERE sessionID = @sessionID

 SELECT 0
END
ELSE
 SELECT –1

RETURN
GO

Lets take this step by step.
 
IF EXISTS (
 SELECT 1
 FROM tbl_users
 WHERE sessionID = @sessionID
 AND  DATEDIFF(n, LastUpdate, GETDATE()) <= 20
)

The T-SQL function IF EXISTS is used to check if records exist for the condition specified. Here, we check whether there are records in tbl_users for which the sessionID exists and where the last update happened less than 20 minutes ago.

The DATEDIFF function needs 3 arguments:
 
1. datepart. In this case we use minutes (n)
2. startdate, for which we use the value in the record
3. enddate, for which we use the current date and time (T-SQL function GETDATE())

This test returns true or false. If the test is true then this code is executed:

BEGIN
UPDATE tbl_users SET
LastUpdate = GETDATE()
 WHERE sessionID = @sessionID

 SELECT 0
END

Basically we update the LastUpdate field with the current date and time. We then select the value 0 to give back to the calling application.

If the IF EXISTS returns false, then we define the value –1 to be given back. We conclude with RETURN, which tells the stored produre that we are done.

Moving onto the second stored procedure, usp_CheckLogin, which is called when we submit the login information. We pass the parameters @usID and @password into this stored procedure, and let it execute:

CREATE PROCEDURE usp_CheckLogin
 @usID VARCHAR(20),
 @Password VARCHAR(20)
AS
DECLARE @sessionID UNIQUEIDENTIFIER
IF EXISTS (
 SELECT 1
 FROM tbl_users
 WHERE usID = @usID
 AND password = @password
 )
BEGIN
 SET @sessionID = NEWID()

 UPDATE tbl_users SET
  SessionID = @sessionID,
  LastUpdate = GETDATE()
 WHERE usID = @usID
 AND password = @password

 SELECT @sessionID
END
ELSE
 SELECT –1

RETURN
GO

Let’s take a step-by-step look at this stored procedure:

DECLARE @sessionID UNIQUEIDENTIFIER

We define a variable @sessionID of Type UNIQUEIDENTIFIER. This data type is a special data type which defines a unique value in the database consisting of the following form:
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. In SQL server, it is considered a string datatype, so whenever we pass it to a stored procedure we need to handle it as if it were a string datatype.

IF EXISTS (
 SELECT 1
 FROM tbl_users
 WHERE usID = @usID
 AND password = @password
 )

Again, we do a test, this time to check whether the passed usID and password exist in table tbl_users. If this test returns true:

BEGIN
 SET @sessionID = NEWID()

 UPDATE tbl_users SET
  SessionID = @sessionID,
  LastUpdate = GETDATE()
 WHERE usID = @usID
 AND password = @password

 SELECT @sessionID
END

Firstly, we let SQL server determine a new database wide unique value, by using the function NEWID(). Then we update tbl_users where we give the usID/password record a new SessionID, and set the LastUpdate field to the current date and time. Finally, we tell the calling application the new sessionID is @sessionID (SELECT @SessionID).

If the IF EXIST test returned false, then we only give the calling application a value of –1. Again, we end this procedure with RETURN. Now let’s move on to the ASP side of the equation.


blog comments powered by Disqus
ASP ARTICLES

- Central Scoreboard with Flash and ASP
- Calorie Counter Using WAP and ASP
- Creating PGP-Encrypted E-Mails Using ASP
- Be My Guest in ASP
- Session Replacement in ASP
- Securing ASP Data Access Credentials Using t...
- The Not So Ordinary Address Book
- Adding and Displaying Data Easily via ASP an...
- Sending Email From a Form in ASP
- Adding Member Services in ASP
- Removing Unconfirmed Members
- Trapping HTTP 500.100 - Internal Server Error
- So Many Rows, So Little Time! - Case Study
- XDO: An XML Engine Class for Classic ASP
- Credit Card Fraud Prevention Using ASP and C...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials