When you think ASP, think...
Recent Articles xml
All Articles
ASP.NET Articles
Related Web Technologies
User Tips!
Coding Tips
spgif spgif

Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
spgif spgif
ASP ASP.NET ASP FAQs Feedback topnav-right
Print this page.
Published: Wednesday, July 07, 1999

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:




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

    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.


    '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 = ""
                    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
                    ' 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
                            Attempted = True
                            Rejected = True
                    End If
            End If
    End If

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

    <!--#INCLUDE FILE="authentication.htm"-->

            'Stop processing before it gets back to your page
    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 %>
    <!--#INCLUDE FILE="include/security.inc"-->

    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 <!--#INCLUDE FILE="security.inc"--> 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.

    ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article