Chapter 17

In this chapter we looked at databases and how to access them using server-side JavaScript.

Question 1

In Chapter 16 we saw how to create a logon screen that redirects the user to one page if her password is valid or asks her to re-enter the password if invalid. The logon name and password were set in the code. Create a database, which holds user names and corresponding passwords, and use this to validate the user.

This is a modified version of my solution to Chapter 16, Question 1. We can reuse the SecureHome.asp page from Chapter 16 as the secure page to which the user is directed if her name and password is correct. We need to create a database of usernames and passwords, which I've called security.mdb, and a logon page, which I've called Ch17_LogOn.asp. Make sure the pages are saved in a directory under your web server, such as the virtual directory AWalkOnTheServerSide that we created in the chapter. The example only works if the pages are browsed on a server; the example does not work if the pages are loaded as normal files.

<%@ language = JavaScript %>
<html>
<head>
<script language=JavaScript>
function form1_onsubmit()
{
   var form = document.form1;
   var returnValue = false;
    
   if (form.txtUsername.value == "")
   {
      alert("Please enter your username");
      form.txtUsername.focus();
   }
   else if (form.txtPassword.value == "")
   {
      alert("Please enter your password");
      form.txtPassword.focus();
   }
   else
   {
      returnValue = true;
   }
    
   return returnValue;
    
}
</script>
</head>
<body>
<%
if (Request.Form.Count != 0)
{
    
   var userNameInDatabase;
   var userName = new String(Request.Form("txtUsername")).toLowerCase();
   var password = new String(Request.Form("txtPassword")).toLowerCase();
    
   var mySQL = "select UserName FROM User WHERE " +
               "UserName='" + userName +
               "' AND Password='" + password + "'";
    
   var adoRecordset = Server.CreateObject("ADODB.Recordset")
   adoRecordset.Open(mySQL,"DSN=SecureUsersDSN");
    
   if (adoRecordset.EOF == false )
   {
      var userNameInDatabase = adoRecordset("Username").Value;
      userNameInDatabase = userNameInDatabase.toLowerCase();
      if (userNameInDatabase == userName)
      {
         Response.Cookies("IsValid") = "Yes";
         Response.Redirect("SecureHome.asp");
      }
      else
      {
         Session("LogonTrys") = parseInt(Session("LogonTrys")) + 1;
      }
   }
   else
   {
      Session("LogonTrys") = parseInt(Session("LogonTrys")) + 1;
   }
    
   adoRecordset.Close();
   adoRecordset = null;
}
    
var invalidTrys = Session("LogonTrys");
if (isNaN(invalidTrys))
{
   invalidTrys = 1;
   Session("LogonTrys") = invalidTrys;
}
else
{
   invalidTrys = parseInt(invalidTrys);
}
    
if ( invalidTrys != 1 )
{
   Response.Write("<P><font color=red size=+2>"
                + "Sorry the username/password"
                + " you entered were invalid</font></P>")
   if ( invalidTrys <= 3)
   {
   Response.Write("<P><font color=black size=+2>"
                + "Please re-enter your details"
                + "</font></P>")
   }
}
    
if ( invalidTrys <= 3)
{
%>
   <P> To access this website please enter your
       username and password in the boxes below</P>
    
   <form action="CH17_LogOn.asp" method=post
         id=form1 name=form1
         onsubmit="return form1_onsubmit()">
    
   <P>Username : <input id=txtUsername name=txtUsername></P>
   <P>Password : <input id=txtPassword name=txtPassword type=password></P>
    
   <P><input id=reset1 name=reset1 type=reset value=Reset>&nbsp;

      <input id=submit1 name=submit1 type=submit value="Log On">
   </P>
    
   </form>
<%
}
%>
</body>
</html>

Save this page as Ch17_LogOn.asp.

We now need to create the Access database to store usernames and passwords. Call it Security.mdb and create one table called User with two fields, UserName and Password, as shown in the following. You can leave the fields' properties at their default values.

Field Name	Data Type
UserName	Text
Password	Text

You'll need to enter some usernames and passwords into your database. Finally, you need to create an ODBC data source for your database called SecureUsersDSN.

Now let's look at how the code works.

At the very top of Ch17_LogOn.asp, we have the all-important language direction telling the server our server-side code is JavaScript.

<%@ language = JavaScript %>

Next we have a block of client-side script containing the function form1_onsubmit() that will be called by the form's onsubmit event handler. Its purpose is to check that values have been entered by the user in the username and password text boxes. If they are empty, we let the user know and prevent the form submission from going ahead by returning false from the function.

Next, we come to some server-side script, starting with an if statement in which we check to see if this page has any form elements submitted to it. Why do this? Well, this page both provides the means for the user to enter her username and password, and does the checking of that username and password when the form is submitted. We'll see shortly that the form on this page submits to the same page. So we must check if the user has loaded this page by navigating to this page with her browser, in which case we don't need to check her security details, or if the page has been loaded due to the user submitting the form to have her security details checked.

<%
    
if (Request.Form.Count != 0)
{

If this page has been loaded by a form submission, it's because the user has pressed the Submit button and wants her logon details checked and then taken to the secure home page or given another chance to enter the details. The next bit of code carries out that task.

   var userNameInDatabase;
   var userName = new String(Request.Form("txtUsername")).toLowerCase();
   var password = new String(Request.Form("txtPassword")).toLowerCase();
    
   var mySQL = "select UserName FROM User WHERE " +
               "UserName='" + userName +
               "' AND Password='" + password + "'";
    
   var adoRecordset = Server.CreateObject("ADODB.Recordset")
   adoRecordset.Open(mySQL,"DSN=SecureUsersDSN");
    
   if (adoRecordset.EOF == false )
   {
      var userNameInDatabase = adoRecordset("Username").Value;
      userNameInDatabase = userNameInDatabase.toLowerCase();
      if (userNameInDatabase == userName)
      {
         Response.Cookies("IsValid") = "Yes";
         Response.Redirect("SecureHome.asp");
      }
      else
      {
         Session("LogonTrys") = parseInt(Session("LogonTrys")) + 1;
      }
   }
   else
   {
      Session("LogonTrys") = parseInt(Session("LogonTrys")) + 1;
   }
    
   adoRecordset.Close();
   adoRecordset = null;
}

The preceding code first retrieves the username and password submitted by the user and converts them to lowercase. This is done so that the username and password are case insensitive, although we could leave this out if we wanted to make the username and password case sensitive.

Then the code creates a string of SQL (mySQL) that selects the username from the database where there is a matching username and password. We create a Recordset object and use the SQL string to open this recordset with the results of the select query. We can then check that the recordset has records in it—by checking that it's not at the EOF (end of file) position—then we compare the username value returned with the username submitted from the form. If they match, we can be sure it's a valid user. If it is a valid user, we set a cookie and redirect the user to the securehome.asp page. If it is an invalid user, we add 1 to the session variable LogOnTrys in which we keep count of how many attempts the user has made to log in. If it's more than three, we don't give the user another chance—this helps to prevent malicious hackers from writing code that would submit millions of different password/username combinations to our form in the hope of getting one right.

It's in the next bit of code that we actually use the LogOnTrys session variable. If this is the first time this session the user has been to the page, the variable will contain no value, so we give it the value 1. Then in the second if statement, we check to see if the number of attempts is not 1. If it's not 1, this is clearly not the first time this session the user has been to the page, and therefore she must have been returned here as a result of entering an invalid username or password. This being the case, we write out a message telling the user that she entered an invalid username/password. If she has gotten it wrong three or fewer times, we write out HTML asking her to try again.

var invalidTrys = Session("LogonTrys");
if (isNaN(invalidTrys))
{
   invalidTrys = 1;
   Session("LogonTrys") = invalidTrys;
}
else
{
   invalidTrys = parseInt(invalidTrys);
}
    
if ( invalidTrys != 1 )
{
   Response.Write("<P><font color=red size=+2>"
                + "Sorry the username/password"
                + " you entered were invalid</font></P>")
   if ( invalidTrys <= 3)
   {
   Response.Write("<P><font color=black size=+2>"
                + "Please re-enter your details"
                + "</font></P>")
   }
}

In the final part of the page, we use a server-side if statement to check that the number of logon attempts is three or less. If it is, the if statement allows the HTML to be written to the page displaying a form with text boxes for the user to attempt another logon. If this is beyond her third attempt, then no logon form appears in the browser, and the user doesn't get a fourth chance unless she closes her browser down and navigates back to the page. Note that the form's ACTION attribute is LogOn.asp, meaning that the form submits to itself.

if ( invalidTrys <= 3)
{
%>
   <P> To access this website please enter your
       username and password in the boxes below</P>
    
   <form action="LogOn.asp" method=post
         id=form1 name=form1
         onsubmit="return form1_onsubmit()">
    
   <P>Username : <input id=txtUsername name=txtUsername></P>
   <P>Password : <input id=txtPassword name=txtPassword type=password></P>
    
   <P><input id=reset1 name=reset1 type=reset value=Reset>&nbsp;

      <input id=submit1 name=submit1 type=submit value="Log On">
   </P>
    
   </form>
<%
}
%>
</body>
</html>

Question 2

Create a message board application. It should display a list of message subjects, with the name of the poster and the date of the post for each message in a database. When a message subject is clicked, it should display the full message body. Add a button to the page that allows new messages to be created and added to the database online.

For my solution we need to create five pages. Make sure the pages are saved in a directory under your web server, such as the virtual directory AWalkOnTheServerSide we created in the chapter. The example only works if the pages are browsed to on a server; it does not work if they are loaded as normal files.

First we have the page that we load into the browser to create the framesets, which load the other pages; call this page Ch17_MessageBoard.htm.

<html>
<frameset rows="*,200">
   <frame name="messages" src="Ch17_messages.asp">
   <frame name="messagebody" src="Ch17_messagebody.asp">
</frameset>
</html>

Then we have the page that displays all the messages available; I've called this Ch17_Messages.asp.

<%@ language=JavaScript %>
<html>
<body>
<center>
   <H2>Message Board</H2>
   <form   name="form1" method=post action="Ch17_NewMessage.asp" TARGET=_top>
    
      <input type="submit" value="Create New Message" name=submit1>
   </form>
   <strong>
   Click the message subject link to
   view the full message
   </strong>
    
<table border=1>
<TR>
   <TH>Message Subject</TH>
   <TH>Posted By</TH>
   <TH>Posted On</TH>
</TR>
<%
var months = new Array("Jan","Feb","Mar","Apr","May","Jun",
                            "Jul","Aug","Sep","Oct","Nov","Dec");
var postedDate;
var lsSQL = "select MessageId, UserName, MessageSubject, " +
            "DatePosted FROM Message " +
            "ORDER BY MessageId DESC"
var adoRS = Server.CreateObject("ADODB.Recordset")
adoRS.Open(lsSQL,"DSN=MessageBoardDSN")
    
while (adoRS.EOF == false)
{
   Response.Write("<TR><TD width=400>" +
                  "<A href='Ch17_MessageBody.asp?MessageId=" +
                  adoRS("MessageId") +
                  "' target=messagebody>" +
                  adoRS("MessageSubject") +
                  "</A></TD>")
    
   Response.Write("<TD width=100>" +adoRS("UserName") + "</TD>")
    
   postedDate = new Date(adoRS("DatePosted"))
   postedDate =postedDate.getDate() + " " + months[postedDate.getMonth()] +
               " " + postedDate.getFullYear();
    
   Response.Write("<TD width=100>" + postedDate + "</TD></TR>" )
    
   adoRS.MoveNext();
}
    
adoRS.Close()
adoRS = null;
    
%>
    
</table>
    
</center>
</body>
</html>

Next we have the page that displays the message's body when the user clicks on a link in the subject line; this page needs to be saved as Ch17_MessageBody.asp.

<%@ language=JavaScript%>
<html>
<body>
<center>
<form>
<%
var lsMessageBody = "";
if (isNaN(Request.QueryString("MessageId")) == false)
{
   var lsSQL = "select MessageBody FROM Message " +
               "WHERE MessageId = " + Request.QueryString("MessageId")
   var adoRS = Server.CreateObject("ADODB.Recordset")
   adoRS.Open(lsSQL,"DSN=MessageBoardDSN")
   lsMessageBody = adoRS("MessageBody").Value;
    
   adoRS.Close();
   adoRS = null;
}
%>
   <textarea rows=8 cols=60 name=textarea1><%=lsMessageBody%></textarea>
</form>
</center>
</body>
</html>

Now we have the page in which users can create a new message; save this as Ch17_NewMessage.asp.

<html>
<head>
<title>Create New Message</title>
<script language=JavaScript>
    
function checkForm()
{
   var form = document.form1
   var controlCounter;
   var returnValue = true;
   var formControl;
    
   for (controlCounter = 0; controlCounter < form.length; controlCounter++)
   {
      formControl = form.elements[controlCounter]
      if ((formControl.type == "text" || formControl.type == "textarea")
           && formControl.value == "")
      {
         alert("Please complete all of the form");
         formControl.focus();
         returnValue = false;
         break;
      }
   }
   return returnValue;
}
    
</script>
</head>
<body>
<H2>Add New Message</H2>
<form action="Ch17_StoreMessage.asp"
      method=post name=form1
      onsubmit="return checkForm()">
<P>
Your Name:<br>
<input type="text" name=txtUserName maxlength=50>
</P>
<P>
Message Subject:<br>
<input type="text" maxlength=50 name=txtMessageSubject>
</P>
<P>
Message Body:<br>
<textarea rows=8 cols=60 name=txtMessageBody></textarea>
</P>
<P>
<input type="submit" value="Submit" name=submit1>
</P>
</form>
</body>
</html>

Finally, we have the page that stores new messages created by the user into the database; save this as Ch17_StoreMessage.asp.

<%@ language=JavaScript %>
<html>
<head>
<title>Message Added</title>
</head>
<body>
    
<%
    
var months = new Array("Jan","Feb","Mar","Apr","May","Jun",
                          "Jul","Aug","Sep","Oct","Nov","Dec");
    
var messageSubject = Request.Form("txtMessageSubject");
var messageBody = Request.Form("txtMessageBody");
var userName = Request.Form("txtUserName");
var postedDate = new Date();
postedDate = postedDate.getDate() + " "
             + months[postedDate.getMonth()] + " "
             + postedDate.getFullYear();
if (messageSubject != "" && messageBody != "" && userName != "")

{
   var mySQL = "insert INTO Message " +
               "(UserName, MessageSubject, " +
               " MessageBody,DatePosted) " +
               "valueS ('" + userName + "'," +
               "'" + messageSubject + "'," +
               "'" + messageBody + "'," +
               "#" + postedDate + "#)";
    
   var adoConnection = Server.CreateObject("ADODB.Connection");
   adoConnection.Open("DSN=MessageBoardDSN")
   adoConnection.Execute(mySQL);
   adoConnection.Close();
   adoConnection = null;
   Response.Write("<H2>Message Posted Successfully</H2>");
}
    
%>
<form name=form1>
   <input type="button" value="Return to Message Board"
          onclick="window.location.href='CH17_MessageBoard.htm'"
          name=button1>
</form>
</body>
</html>

Now we need to create the database that holds the messages and set up an ODBC data source.

Create a new database in Access and call it Message.mdb. Now create one new table called Message with the fields and field types shown as follows. You can leave the sub-properties at their default values. Make MessageId the primary key field.

Field Name	Data Type
MessageId	AutoNumber
UserName	Text
MessageSubject	Text
MessageBody	Memo
DatePosted	Date/Time

You might want to add a few dummy values to start with, though you can also use the new message form page to enter your data.

Now close the database and create a new DSN named MessageBoardDSN, and select the database just created.

Let's look at how it works, starting with the Ch17_Messages.asp page. Near the top we have a form with just a button in it; when clicked, the button submits the form to the New Message page where a new message can be created. We don't actually pass any values and simply use the form post as a way of navigating to a new page using a button but without using code. An alternative would be adding code to the onclick event handler of the button.

   <form name="form1" method=post action="NewMessage.asp" target=_top>
      <input type="submit" value="Create New Message" name=submit1>
   </form>

The action is all happening in the following block of server-side code. It's this that actually accesses the database, retrieves the messages, and writes them out to the page.

<%
var months = new Array("Jan","Feb","Mar","Apr","May","Jun",
                            "Jul","Aug","Sep","Oct","Nov","Dec");
var postedDate;
var lsSQL = "select MessageId, UserName, MessageSubject, " +
            "DatePosted FROM Message " +
            "ORDER BY MessageId DESC"
var adoRS = Server.CreateObject("ADODB.Recordset")
adoRS.Open(lsSQL,"DSN=MessageBoardDSN")
    
while (adoRS.EOF == false)
{
   Response.Write("<TR><TD width=400>" +
                  "<A href='Ch17_MessageBody.asp?MessageId=" +
                  adoRS("MessageId") +
                  "' target=messagebody>" +
                  adoRS("MessageSubject") +
                  "</A></TD>")
    
   Response.Write("<TD width=100>" +adoRS("UserName") + "</TD>")
    
   postedDate = new Date(adoRS("DatePosted"))
   postedDate =postedDate.getDate() + " " + months[postedDate.getMonth()] +
               " " + postedDate.getFullYear();
    
   Response.Write("<TD width=100>" + postedDate + "</TD></TR>" )
    
   adoRS.MoveNext();
}
    
adoRS.Close()
adoRS = null;

At the top of the code an array containing the names of all the months of the year is created, which we use later to format the date that the message was posted before writing it to the page. Then we create our SQL query that selects the information we need from the database so that we can create the list of messages posted to our message board. Using this SQL, we open a recordset and populate it with the data.

Then, in the while loop, we loop through the records in the recordset and write out a table row containing the message subject, message author, and date it was posted. Notice that the subject is wrapped inside a link that loads Ch17_MessageBody.asp into the messagebody frame at the bottom of the page. We add data to the URL with the name MessageId and give it the value of MessageId retrieved for that record. That way the message body page knows which message body it needs to get from the database and display.

Let's turn next to the Ch17_Messagebody.asp page. It's fairly simple; first we get the message ID we added to the URL from the Request.QueryString property. We check that it contains a valid number. If it doesn't, the message body page has been loaded by the frameset and not because the user clicked a link to see it, so we need to access the database. We create our SQL to select the message body from the database before using it to open a recordset and store the body in the lsMessageBody variable.

<%
var lsMessageBody = "";
if (isNaN(Request.QueryString("MessageId")) == false)
{
   var lsSQL = "select MessageBody FROM Message " +
               "WHERE MessageId = " + Request.QueryString("MessageId")
   var adoRS = Server.CreateObject("ADODB.Recordset")
   adoRS.Open(lsSQL,"DSN=MessageBoardDSN")
   lsMessageBody = adoRS("MessageBody").Value;
    
   adoRS.Close();
   adoRS = null;
}
%>

Finally we display the body by putting lsMessageBody inside the <textarea> tags.

   <textarea rows=8 cols=60 name=textarea1><%=lsMessageBody%></textarea>

The Ch17_NewMessage.asp page is also straightforward. It simply consists of a form for the user to enter her message details. At the top of the page we have some client-side script, the checkForm() function, that's called by the form's onsubmit event handler. It goes through each text box and textarea in turn and checks that they have been filled in by the user; if not, false is returned to the calling event handler and the onsubmit is canceled.

function checkForm()
{
   var form = document.form1
   var controlCounter;
   var returnValue = true;
   var formControl;
    
   for (controlCounter = 0; controlCounter < form.length; controlCounter++)
   {
      formControl = form.elements[controlCounter]
      if ((formControl.type == "text" || formControl.type == "textarea")
           && formControl.value == "")
      {
         alert("Please complete all of the form");
         formControl.focus();
         returnValue = false;
         break;
      }
   }
    
   return returnValue;
}

Finally, if the form is complete, it is submitted to our final page Ch17_StoreMessage.asp. As the name suggests, this inserts the new message as a new record in the database.

In the page's server-side script block, we need to connect to the database and insert a new record based on what the user submitted as her message.

First we declare an array of months that we'll use in creating the "date posted" details. Then we retrieve the message subject, body, and user's name from the form submitted and store these in variables. Finally, we generate a postedDate variable containing today's date. This gives us all the information we need to then move on and generate the SQL that will insert the record.

var months = new Array("Jan","Feb","Mar","Apr","May","Jun",
                          "Jul","Aug","Sep","Oct","Nov","Dec");
    
var messageSubject = Request.Form("txtMessageSubject");
var messageBody = Request.Form("txtMessageBody");
var userName = Request.Form("txtUserName");
var postedDate = new Date();
postedDate = postedDate.getDate() + " "
             + months[postedDate.getMonth()] + " "
             + postedDate.getFullYear();

In the remainder of the server-side script we first check that there actually has been a message subject, body, and username submitted. If any of these variables is empty, we don't put the information in the database. This helps make it slightly more difficult for hackers to post directly to your database without going through the proper form or indeed the situation where a user's browser doesn't support JavaScript and so our client-side check of the form onsubmit has not occurred.

Assuming the variables do contain values, we generate our SQL and store it in the variable mySQL. We use an insert statement to do this, making sure that any text or memo fields are surrounded by single quotes and the date field by a #.

Then the code creates an ADO Connection object and uses it to open a connection to the database using our DSN, and finally executes our SQL insert statement and adds the message to the database.

if (messageSubject != "" && messageBody != "" && userName != "")

{
   var mySQL = "insert INTO Message " +
               "(UserName, MessageSubject, " +
               " MessageBody,DatePosted) " +
               "valueS ('" + userName + "'," +
               "'" + messageSubject + "'," +
               "'" + messageBody + "'," +
               "#" + postedDate + "#)";
    
   var adoConnection = Server.CreateObject("ADODB.Connection");
   adoConnection.Open("DSN=MessageBoardDSN")
   adoConnection.Execute(mySQL);
   adoConnection.Close();
   adoConnection = null;
   Response.Write("<H2>Message Posted Successfully</H2>");
}