Building an Internet Database Drilldown Application, Part 1

Building an Internet Database Drilldown Application, Part 1

Read Building an Internet Database Drilldown Application, Part 2

Roger Drye Directed Minds

Homepage: www.directedminds.com Email: info@directedminds.com

A recent project I developed was for a group of local school districts that wanted to be able to view school bus ridership statistics and data via the web.

The application was constructed so that the end user could drill down through the bus ridership data in the following order:

School District

Local School Bus Office (each district has a number of local school bus offices)

Bus Route (each bus office has a number of assigned routes)

Bus Run (each route had a series of runs in the morning and afternoon)

Bus Stops (each run has a series of stops)

Students (each stop has a number of assigned students at the stop)

Guardian (each student has a responsible guardian)

At each level the user can see summary statistics for that level and can drill down into the next level. An early decision made was that all page creation had to be built dynamically from a database, so that, for example, if new bus routes were added or deleted, no programatic changes were needed in the internet application.

The solution was an internet-based Access 97 database and ASP pages. Microsoft Access was selected primarily because the size of the database would remain relatively stable, and a defined number of users meant no significant concurrency issues. A nice feature of building ASP pages for Microsoft Access is that the code can be quickly ported to another database, such as Microsoft SQL Server, simply by modifying the connection statements. This series of articles reviews some of the processes that were developed to implement the application.

Securing the Site

Sounds easy, there are many ways to secure an ASP site, using NT security, 3rd party controls, databases, or script code. For this application, the user had to be allowed to access the application and login with any browser type and from behind any firewall or proxy server that they had in place. Additionally, many of the users were were on NT networks that used DHCP for assigning IP addresses.

What does this mean?

If you have ever tried to implement NT or IIS security on a system that has users coming in from different networks, domains, firewalls, proxies and browser types, you have certainly seen errors such as this before:

From Internet Explorer:

HTTP Error 401
401.1 Unauthorized: Logon Failed
This error indicates that the credentials passed to the server do not match the credentials required to log on to the server. Please contact the Web server's administrator to verify that you have permission to access the requested resource.

or from Netscape:

HTTP Error 401
401.3 Unauthorized: Unauthorized due to ACL on resource
This error indicates that the credentials passed by the client do not have access to the particular resource on the server. This resource could be either the page or file listed in the address line of the client, or it could be another file on the server that is needed to process the file listed on the address line of the client.

Why? Well, here's just one example of what might have happened. Believe me, this is just one example of the type of mess you get into developing security solutions.

When a user makes a request to the Internet Information Server (IIS), it begins as an Anonymous request. Only when the user requests a restricted directory will the user be prompted for logon credentials.

When the user is located behind a firewall, the possibility arises for the initial Anonymous logon credentials to be blocked from the user's request before reaching the IIS. The IIS should then respond with a 401.3 HTTP error because the client didn't have the minimum (Anonymous) credentials.

In short, the logon credentials are being blocked by the firewall. To get IIS security to work in this situation the system administrator of the firewall would have to configure a peek-through to allow access to the internet application. Not very likely.

Another option was to go with 3rd party server-side authentification. There are several good products out there that do this. However, in this case the client said no to the money. Too bad, it would have been money will spent.

So next, I was off to write my own ASP solution. Some standard methods of doing this did not pan out. For example, I could not restrict access by IP address because some of my users had dynamically generated IPs, either from there ISP or their Windows NT DHCP system. Restricting by domain did not seem adequate.

Evaluating the security needs again to see what was really needed, I settled on a simple solution. I only needed one group account, a simple verification that the user was allowed to use the site. I ended up with this simple all ASP solution. Create a login form such as this:

Username:
Password:

<%
'AUTHENTICATION.HTM
%>
<table>
<tr>
    <td><form action="<%=URL%>" method="POST">Username: </td>
    <td><input type="text" name="UserID" size="20"></td>
</tr>
<tr>
    <td>Password: </td>
    <td><input type="password" name="UserPWD" size="20"></td>
</tr>
</table>
<input type="submit">
</form>

Save the file as "authentication.htm" and place it in a separate directory from my ASP or HTML files, such as an "/include/authentication.htm".

Next create the "security.inc" file. This file is included at the top of every page that requires validation.

<%
'SECURITY.INC

'Does the session know the user? A session ID will remain active as long as the user has the browser open.
'So the user will have to login every time they start a new browser session.
UserID = Session("UserID")
Rejected = False

If IsEmpty(UserID) Or IsNull(UserID) Or UserID = "" Then
        Attempted = False

        'Figure out who we are, for later
        URL = Request.ServerVariables("QUERY_STRING")

        If IsEmpty(URL) Or URL="" Then
                ' Just in case
                URL = ""
        Else
                URL = "?" & URL
        End If

        URL = Request.ServerVariables("SCRIPT_NAME") & URL

        'Check for POSTed authentication information
        UserID = Request.Form("UserID")
        UserPWD = Request.Form("UserPWD")

        If IsEmpty(UserID) Or IsNull(UserID) Or UserID = "" Then
                Rejected = True
        Else
                ' Insert your own checking here
                ' You may want to store username/passwords in a database
                ' See this article for more information!
                If UserID = "test" AND UserPWD = "1234" Then
                        'They are good, set the session variable
                        Session("UserID") = UserID
                        Rejected = False
                Else
                        Attempted = True
                        Rejected = True
                End If
        End If
End If

If Rejected Then
        If Attempted Then
                Title = "Authentication Failure"
        Else
                Title = "Please Authenticate"
        End If
%>

<%
'Stop processing before it gets back to your page
Response.End
End If

' ... Otherwise, on with your normal page.
%>

Next, include the following in every ASP or HTML file of your site, by typing

<% response.buffer = true %>

at the top of every page. The line: <% response.buffer = true %> statement does not allow the page to be displayed before processing is complete.

There you have it. Before each page is loaded, the "security.inc" file will test for a valid session, which means a valid login. If the session does not exist then the user is sent back to the login page. The nice feature of this is that the user cannot enter the site by bypassing the default home page. You can enhance this by adding more valid logins, or with a little work, even different levels of logins. Also, you can set up your site so that certain pages require a valid login, while others do not. Only pages with the  at the top require validation.

Next ... Making the connection to the database

Read Building an Internet Database Drilldown Application, Part 2

Directed Minds is a web development company specializing in hosting and developing ASP, database and ecommerce solutions for large and small organizations.

Roger Drye is the owner of Directed Minds. He has been developing Internet solutions for small and large companies and government entities for many years.