Beginning ASP and Microsoft Access

We now know how to perform some basic SQL commands in ASP. ASP is so efficient for dealing with data as it is simple to code and you can perform all of the necessary actions you would need to do to keep your web site dynamic.

Consider CGI pages, where one must use a language like C++ or PERL to manipulate the URL to gather and send information. This is a timely process and requires you to program with more than one file.

There are two forms of debugging (i.e. the debugging at the compilation stage and debugging in the browser). All of this is time consuming, and in business, costs money. With ASP you are only using 1 file -- the ASP page itself. Debugging is done in the browser. Another use for ASP is that you can generate a URL and pass the information collected from the user’s interaction to the next page.

Being a keen web programmer, like myself, you would have heard about name/value pairs. The ideology behind name/value pairs, similar to form submission, is to assign information to a variable and pass the information amongst the web pages.

Let's say that the ASP page appears with a URL such as  www.mysite.com/myaspinsertpage.asp?id=1&name=Fred , where the URL contains a question mark at the end of the asp page to indicate that there are variables to be passed in to the page.

After the question mark there is a set of name/value pairs, so we pass the variables into the page via the URL, but how do we receive them? This is the strong point of ASP files. All you need to do is request the name of the variable from the URL -- ASP takes care of the rest!

So, if we submit a form or simply build up a URL on the client side, we can get the value of the variable in a way similar to a hash table. The code for this is as follows:

v_id = Request.QueryString("id")

The result would be v_id = 1. We could now use this information to insert the new user Fred using the insert statement listed above.

If you are a web developer who believes in secure web development, you may consider using a password to protect your database. So how can you do this? Well, it's quite simple. You now know how to connect to a database from a web server and you know how to pass variables into an ASP page from the URL. Let's put the two together to solve the security problem.

Solving Our Problem
One method to do this would be to create an Access page, such as  www.mysite.com/myaccesspage.html , where the user seeks permission to retrieve data from the database. The user is prompted to enter his/her username and password. The information details that were entered in the page are then checked and the user is allowed to access the database if permission is granted.

The user name and password are passed into the next page, such as  www.mysite.com/mynextpage.asp . The information could be sent using a form which allows the user to have the password hidden when typed (i.e. an asterix (*) will appear in place of any character in the password field). The next page would be equivalent to a URL containing the values from the form, such as
www.mysite.com/mynextpage.asp?name=yoda
&password=maytheforcebewithyou, if and only if the "method" attribute of the HTML <form> tag was set the "GET".

This is insecure however, as this page would show up in your browser history if someone else was using your PC. To avoid passing the values in the URL, set the "method" attribute of your HTML <form> tag to "POST" . The authentication may be done by typing the following code into the body section of the mynextpage.asp file:

<%
Set v_name = Request.Form("name")
Set v_password = Request.Form("password")

Set v_connection_object = Server.CreateObject("ADODB.Connection")
v_mapped_path = Server.MapPath("yadayada.mdb")
v_connection_string = "Driver={Microsoft Access Driver (*.mdb)};DBQ="&v_mapped_path&";"
v_connection_object.Open v_connection_string

v_query = "SELECT * FROM authorised_user_table WHERE name='"&v_name&"' and password='"&v_password&"'"

Set v_users = v_conn.Execute(v_query)

IF (NOT v_users.BOF) AND (NOT v_users.EOF) THEN
' Login successful set cookie
response.cookies("authorised_user") = v_name
ELSE
' Login not successful user is sent back to access page
Response.Redirect(www.mysite.com/myaccesspage.html)
END IF

v_users.Close
v_connection_object.Close
v_users = Nothing
v_connection_object = Nothing
%>

Note that the Form object was used instead of the QueryString object to get the username and password that the user was trying to login with.  Cookies are used to keep track of user interactions.