Active Data Objects & ASP

Mark is president of Incurrent Corp., a specialized commerce service provider and developer of Internet cardholder service applications for the payment card industry. He can be contacted at [email protected] or http://www.incurrent.com/.

There are times in web development when you need direct access to data from the web server. Perhaps your application is a simple catalog or report generator. But even a complicated extranet app has its two-tier elements. One clear example is the need to manage application state. Not long ago, just about any web app that did more than display pages had to manage its state on the server side. This is still the case if your app needs to be compatible with absolutely every browser ever made. If you're willing to thin the herd and select a subset of clients, then you can move some processing, and thus some state management, out to the client in the form of scripts, controls, and applets. Still, it is difficult to carry state across pages on the client. If, for example, you have an HTML element that may be in one of two states, then its state is probably going to be set on the server every time you build a page. Otherwise, you would somehow have to persist the state on the client across requests. This is certainly possible, but the available solutions may involve things like writing to the local registry or file system, which makes users nervous.

It's easy, however, to carry state across pages on the web server. Early applications for the Web used CGI programs that sometimes wrote to local files or more-commonly accessed data on a server somewhere. The stored data had to be keyed to a particular user (not a trivial problem in HTTP), but otherwise, there was no limit to what could be persisted to the DBMS. CGI programs are probably still the most common means of activating web applications. However, recent technologies focus on extending the web server through the use of componentry in the form of COM, CORBA, and Java objects, and, very recently, on the use of server-side scripting environments. Server script is particularly suited to cementing the incoming HTTP request to the layer of object interfaces that implements transactional capability. It is also very useful for managing application state and formatting pages. Page generation is handled more readily in script than in CGIs (Perl aficionados will complain, quite rightly, but Javascript and VBScript are certainly more approachable for many developers than Perl). Furthermore, the pages in a web application are subject to frequent change, and scripts provide a very malleable interface between the pages and the less-mutable parts of the system.

Microsoft's Active Server Pages

Microsoft's Active Server Pages (ASP) provides an environment within which a script executes on the web server, resulting in output that is fed back to the client. ASP is compatible with various scripting languages through the use of COM interfaces that expose the scripting engine. Currently, the environment supports VBScript and Javascript. Active Data Objects (ADO) is another Microsoft technology, one built on top of OLEDB, which is in turn built on top of the Open Database Connectivity (ODBC) Standard. ODBC provides a functional interface that separates data access from data providers, and allows applications to execute SQL queries against different server implementations. OLEDB provided the COM interface to ODBC, and ADO simplified OLEDB with an automation wrapper that was useful to Visual Basic programmers. With the growing popularity of ASP as an extension tool for Internet Information Server (IIS), ADO has emerged as a fast, reliable way of connecting your server script to data. Keying application state to a specific user is a good example of using ADO with ASP.

Identifying a user so you can grab the right data is a problem, because HTTP is a connectionless protocol. It is implemented as a set of atomic request/response transactions. The protocol does not provide any concept of a client across requests. In the context of static pages, this is a reasonable choice: It makes the HTTP protocol much simpler, and thus much more robust. However, the lack of connection state has a profound impact on the design of information- or transaction-driven web applications. Consider the problem of authentication across two requests. On request one, the server can receive the user authentication tokens as arguments in a form POST. The tokens can be validated against a user database and, if the user authenticates, the next page in the app can be returned. Request two, originating from the returned page, must somehow convey to the server the authenticated state, as well as the identity, of the user. When the server responds to request two, it will use this information to determine the current state of the application.

There are basically two ways to get user information across the connection:

• Piggyback it onto the HTTP transaction, either as parameters to a GET, or name-value pairs in a POST.
• Use cookies. Cookies are short strings of text that are generated on the server and sent to the client, and are then returned to the server by the client when a request is made.

ASP provides an automation object named Session that utilizes cookies to maintain connection state. The Session object is very easy to use. However, it suffers from problems with cookies, namely that users perceive them as a privacy threat and can disable them. Cookies were the only means Microsoft had for enabling user sessions without intruding into the application script. The first method, adding user information to GETs and POSTs, is more intrusive, but more reliable. Since the script-level requirements can be minimized through the use of server includes -- blocks of script included into a page prior to script execution -- or by encapsulating the needed functionality in COM controls, developers often prefer this method. It also makes a more interesting example of ADO use.

In this example, there is a set of pages that cannot be retrieved by users unless they have authenticated. Once authenticated, a user will be assigned a session key, and the key will be used to access application state on the web server. The session key will be embedded into returned pages as a parameter to any URLs, and as a hidden input field in any forms. On the server side, the script has the responsibility of making sure that a controlled page is accessed with a valid session key. If a request is received with no session key, or an invalid session key, then the user must be redirected to the login page. What constitutes an invalid session key? The system may have been forced to invalidate the key due to an error. A more common reason is simply that the user is finished, and no longer needs the key. Unfortunately, there is no easy way to know when this is the case. HTTP provides no hint of state across requests, so how do we know which request was the last one? A logoff button can be provided, but users can easily surf out without clicking it. The only reliable mechanism involves a process that times out inactive sessions. We won't get into that aspect of the system very deeply here.

Our database requirements are simple. Figure 1 shows two tables. The first is named tbl_User. This table has four columns. UserID is an IDENTITY column that serves as the primary key. UserName, UserPwd, and UserFullName are all char columns with self-evident uses. The second table is tbl_Session. It has three columns. SessionID is another IDENTITY column providing the primary key. It isn't necessarily a good idea to use an IDENTITY column to generate session keys. A better mechanism is to generate a pseudorandom 32-bit integer, then hash it against the client's IP address. For this example, we'll keep it simple. The other two columns of tbl_Session are UserID, a foreign key into tbl_User, and LastAccessTime, which is a smalldatetime used to control inactive sessions. These two tables will provide all the storage we need for the purposes of authenticating and providing session state. In practice you'd have other tables keying on the session or user IDs, and containing various parts of the application's state.

Listing One provides examples of the first two components in our app -- the login form and the login script. When the user clicks the login button in userlogin.htm, a POST is executed against the web server, containing the form input field contents, and identifying the login.asp script as the target. On entry to the login.asp script, we first need to extract the login parameters from the HTTP request. ASP provides an automation object called Request. Among the Request object's several collections is one called Form. The values in the Form collection correspond to the named input fields in the login form. Thus, the script <%UserName = Request.Form("UserName")%> retrieves the value that the user typed into the "UserName" edit. The first duty of login.asp is to extract the user name and password from the Form collection, and get them into variables. If this operation fails for any reason, the user must be redirected back to userlogin.htm. Checking parameters before processing them is always a good idea: The "calls" into your script are often URLs that hang out in the browser address bar, tempting users to play around with them. It's a good idea to program defensively.

Once the parameters are verified, we need to create the database connection. ADO's Connection object represents a connection to an ODBC datasource. Opening the database connection requires three steps.

1. First, the connection string is created. It contains the ODBC datasource name, the name of an authorized user, and the user's password.
2. Next, the connection object is created. There are two primary means of creating objects in ASP. Either the object is inserted into the page using the HTML OBJECT tag and the RUNAT="SERVER" parameter, or the Server object is used as a factory. The Server object is one of the built-in ASP objects, and its CreateObject method can create an instance of any scriptable automation object. It's a bit cleaner than the OBJECT tag, so it is the path we'll take. The only parameter we need is a string identifying the class and interface we want -- in this case, the ADODB class and the Connection interface. The result of the call is assigned to the Conn variable.
3. Finally, the Connection object's Open method is called and passed the connection string we built previously. If all is successful, when this method returns, the Conn variable holds an open connection on our datasource. Should an error occur anywhere in this process, the ASP engine will cease evaluating the script and return an error to the client.

With the connection open, the first order of business is to authenticate the user by looking up the user name and password in the database. A simple SQL SELECT statement suffices in this example. If no record containing both the name and password is found, then the authentication fails and the user would normally bounce out to an error page. In this case, we'll just return them to the login screen. To execute the SQL statement, we first build it into a string. This process is familiar to anyone who has programmed SQL Server using Microsoft's APIs. Once the query string is constructed, it is passed to the Execute method of the Connection object. The Execute method is capable of running any SQL statement that is supported by the data provider. Since ours is SQL Server, we've got a wide range of functionality available. Later, we'll see an example of compound statements, multiple result sets, inserts, and so forth. If you're keeping an eye on portability, you don't want to get too fancy, and in any case, it is evident from reading the listings why restricting the use of ADO and ASP to managing application state and other local web server tasks is a good idea. ADO enshrines the data schema in the application source code.

The result of a successful call to Execute is an interface on a RecordSet. The RecordSet object is ADO's primary interface to data on a server, with a number of properties and methods that provide full cursor access to result sets. Note that not all operations are available from every data provider. Often, if an operation is not supported, the result is a NULL, or other ambiguous value. In the case of the AbsolutePosition property, for example, a return value equal to the constant adPosUnknown may mean the RecordSet is empty, the current position really is unknown, or the provider does not support the absolute position property. The moral is: Know your datasource. If you're keeping things relatively simple, you won't have any problems. At the moment, our needs are very simple. If Conn.Execute completes successfully, then the RecordSet is assigned to the variable RS, and the script in login.asp checks to see if the RS.EOF property is True. EOF is set to True when the current position is one after the last record returned, and so is True for an empty result set. True, in this case, means the user record wasn't found, so we send the user out to userlogin.htm again. If RS.EOF is False, then we retrieve the selected UserID into a variable by querying the RecordSet Field collection. Fields are available for the current row and may be accessed by name or ordinal position. In this case, we ask for UserID by name.

With the user authenticated and the database ID in hand, the script has one more check to make before creating the session. Since this is a web app, the user may be trying to log on again while a session is still active for him. To prevent this, the script queries the session table to see if the UserID is present. If it is, then the selected session key can be reused in the redirect to the welcome page. Assuming the user does not have an active session, the script must create one by inserting a new record into the session table. We noted earlier that SQL Server was going to generate the session keys for us through the use of an IDENTITY column in tbl_Session. An IDENTITY column is automatically stuffed with a unique value when an INSERT takes place on the table. Since we need the session key, we also need to get our hands on the value of that column right after the INSERT. Fortunately, SQL Server supplies the @@IDENTITY global that contains the last generated value for an IDENTITY column. Unfortunately, @@IDENTITY is only valid until the next INSERT against a column without an IDENTITY attribute occurs. We don't know when that will happen. Therefore, we'll use a compound statement to insert the session record and grab the IDENTITY column value all at once.

The use of a compound statement gives us a chance to look at how the RecordSet object handles multiple result sets. The statement itself is simply a batch of commands separated by semicolons. The first command in our batch is an INSERT. What we're inserting is the UserID and the initial value for LastAccessTime. SQL Server generates the SessionID, updating @@IDENTITY to the new value, which is selected out by the second command of the batch. Assuming Conn.Execute completes successfully, RS will contain an empty RecordSet resulting from the successful INSERT. Since we executed a batch, we know that there is another RecordSet. To get to it, we call NextRecordSet on our existing RecordSet. What we get back, hopefully, is a new RecordSet that is positioned at the first row in the results from the next command in the batch. At this point, all that remains is to extract the SessionID by querying for RS(0). We use an ordinal here because our SELECT did not assign a column name to the result. RS(0) accesses the first field of the current row, which is our selected identity value. The last step in the process simply redirects the browser to the welcome page with a URL that includes the session key.

The welcome page makes its way into our example because it demonstrates the basic tasks that all session-controlled pages must accomplish when a user requests them: checking for a valid session, updating the session's LastAccessTime column, and using the validated session to retrieve user information. All of these things happen in welcome.asp, which is presented in Listing Two. In essence, this file is a template from which any other session-controlled page can easily be built. Normally, we would take the session validation script and move it out to an include file that can be parsed into any page that needs the ability to verify a session. Once again, though, we'll try to keep it simple for the purpose of illustrating how ASP and ADO work together to handle these tasks. The welcome.asp page also demonstrates the combination of HTML and ASP script that is the strength, and the weakness, of server-side scripting. The strengths lie in the ease with which output can be generated and fed back to the client, using all of the power of Visual Basic's string handling. The weakness is that it can be difficult to combine these elements in a way that leaves a structured and readable source file. Fortunately, in this case, nearly all the script precedes the HTML in the file.

In this script, as with the login script, the first thing to do is certify that the required parameters are present in the query string. In a more complicated application, we would probably have to do some validation on the types and ranges of the parameters as well. Fortunately, all we are concerned with here is that the sesskey argument is present. It doesn't really matter what the type of its value is. One of the nice things about working in a script environment where all variables are of type VARIANT is that you can almost always compare what you have against what you want. If we want a numeric session key and the user edits the URL to present a string instead, the result is the same as for any other invalid session key. (Of course, working with nothing but VARIANTs causes lots of problems, too, but that's a topic for another time.) Once the script has determined that the session key is present, it has to validate the session. A session key is valid if it is the primary key to a record in the session table, and the UserID column of that record is a foreign key to a valid record in the user table.

We could easily wrap these two requirements into a single query, by joining the tbl_User and tbl_Session tables on the UserID column. We won't do this because it requires that the data provider support joins. The business logic of a real application may need to set and enforce this requirement, but we prefer to keep the simpler application-state management as portable as possible. Before executing the statements to validate the session, the script creates a database connection exactly as in the login script. This is the kind of logic that can be extracted into an include file, since the same connection string is often used throughout an app. It might not be a good idea if, for example, the user also has database privileges, and you want to build the actual user ID and password into the string. In that case, you'd probably build the string when the session was created and store it. Our example envisions a case where the application and database security models are separate. Regardless of where the user's ID and password come from, there are other parameters that you shouldn't have to retype into every page. In practice, we've found it handy to store information such as the datasource name, login ID, and login password in the system registry on the web server or another host on the back-end. A simple COM control can extract this information into script variables, so that the pages can be built generically.

With the connection in hand, the script executes a SELECT to get the UserID from the record in tbl_Session that is identified by the session key passed in the URL. If this record is not found, then the session key is invalid. If the record is found, the returned UserID is extracted from the RecordSet object in RS. The next test is for a valid user record. The script performs this test by selecting out the UserFullName column, which will be used later in the welcome message. A failure here means an error in the integrity of the database: There should be no records in the session table with a UserID column that is not a valid foreign key into the user table. In fact, if we're using a full-featured database such as SQL Server to store the application state, the UserID column of tbl_Session will be constrained so that only valid foreign keys can be inserted into it. Such a constraint would allow us to select the UserFullName column without any fear that the operation could fail due to an invalid user ID. Since we are not assuming such a constraint here, we check to see if the returned RecordSet is empty. If it is, then the script makes use of the Response object to write an error message to the client. If all goes well, the script completes processing and the variable UserFullName is used in the body of the HTML to extract the user's name into the welcome message. This illustrates one of the more powerful ways in which ASP script integrates with the HTML output stream.

Conclusion

Active Server Pages are useful for generating output and managing application state on behalf of a client. When combined with Active Data Objects, your script can manipulate ODBC data sources to do nearly anything that is possible in native SQL. In our applications, we have used ASP and ADO to execute stored procedures, create temporary tables, and wrap up transactions. Still, it is important to recognize this model's limitations, which flow more from the limitations of scripting languages than from limits on what ADO can do. Scripting languages, whether running on the server or client, suffer from the problems normally associated with interpreted, weakly typed languages. Misspell a variable name in VBScript and you've created a new variable. If you're lucky, it will generate an error. ASP suffers particularly from the difficulty of mixing script and HTML in ways that retain structure and maintainability. It is also obvious that too liberal use of ADO in script will lead to a very tight coupling between the application logic and the data schema. This is acceptable for small applications, but not for critical systems. For this reason, we have argued here in favor of using ASP with ADO to manage application state and glue components together. In this role, they serve as one more layer in an architecture that may include dynamic web pages on the front end, and mission-critical object interfaces on the back end.

DDJ

Listing One

<!-- USERLOGIN.HTM --><HTML>
<HEAD>
<TITLE>Application Login</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF">


</p>
Please login with your user name and password.


</p>
<FORM NAME="LoginForm" METHOD="POST" ACTION="login.asp">
  <INPUT NAME="UserName" TYPE="TEXT" SIZE=25>
  <INPUT NAME="UserPwd" TYPE="TEXT" SIZE=25>
  <INPUT TYPE="RESET" VALUE="Clear">
  <INPUT TYPE="SUBMIT" VALUE="Log In">
</FORM>


</p>
</BODY>
</HTML>


</p>
<!-- LOGIN.ASP -->
<%@ LANGUAGE="VBSCRIPT"%>
<%
Response.Buffer = TRUE
'*********************************************************************
' This script handles the user login
'*********************************************************************


</p>
UserName = Request.Form("UserName")
UserPwd = Request.Form("UserPwd")
if UserName = "" or UserPwd = "" then
  Response.Redirect("userlogin.htm")
end if


</p>
cADOConnectString = "dsn=example_db;uid=user;pwd=userpwd"
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open(cADOConnectString)


</p>
Query = "SELECT UserID from tbl_User where ((UserName = " & UserName _
  & ") and (UserPwd = " & UserPwd & "))"
set RS = Conn.Execute(Query)
if not RS.EOF then
  UserId = RS("UserID")
else
  Response.Redirect("userlogin.htm")
end if


</p>
RedirectURL = "welcome.asp?sesskey="
Query = "SELECT SessionID from tbl_Session where UserID = " _
  & CStr(UserId)
if not RS.EOF then
  RedirectURL = RedirectURL & RS("SessionID")
else
  DateTime = Date & " " & Time
  Query = "INSERT tbl_Session values(" & CStr(UserId) & ", '" _
    & DateTime & "'); SELECT @@IDENTITY"
  Conn.Execute(Query)
  set RS = RS.NextRecordSet
  RedirectURL = RedirectURL & RS(0)
end if


</p>
Response.Redirect(RedirectURL)
%>

Back to Article

Listing Two

<!-- WELCOME.ASP --><%@ LANGUAGE="VBSCRIPT"%>
<%
Response.Buffer = TRUE
'*********************************************************************
' This page welcomes the authenticated user
'*********************************************************************
if Request.QueryString("sesskey") = "" then
  Response.Redirect("userlogin.htm")
end if


</p>
cADOConnectString = "dsn=example_db;uid=user;pwd=userpwd"
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open(cADOConnectString)


</p>
Query = "SELECT UserID from tbl_Session where SessionID = " _
  & Request.QueryString("sesskey")
set RS = Conn.Execute(Query)
if not RS.EOF then
  UserId = RS("UserID")
  Query = "SELECT UserFullName from tbl_User where UserID = " _
    & CStr(UserId)
  set RS = Conn.Execute(Query)
  if RS.EOF then
    Response.Write("Bad user ID in welcome.asp")
    Response.End
  else
    UserFullName = RS("UserFullName") 
    DateTime = Date & " " & Time
    Query = "UPDATE tbl_Session set LastAccessTime = " & DateTime _
      & " where SessionID = " & Request.QueryString("sesskey")
    Conn.Execute(Query)
  end if 
else
  Response.Redirect("userlogin.htm")
end if
%>
<HTML>
<HEAD>
<TITLE>Welcome Page</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF">


</p>
Welcome <%=UserFullName%>.


</p>
</BODY>
</HTML>

Back to Article