The Insert Script, Part 2: Basic Error Handling
Although the insert script we presented above is straightforward and easy to follow, if you look at other developers' insert scripts you'll see that our code isn't quite so nice. Error handling is to blame; it's a necessary evil. Every insert script should handle four typical errors, listed here in order of ease of implementation:
Spaces before and after user information
The single-quote bug
Blank information
Letters for numbers
Let's quickly look at how to handle each one.
Spaces Before and After User Information
It's common for users to accidentally—or maliciously—insert spaces before and after otherwise valid information. For example, suppose our guest book has a text box to get the user's name. A user named Pat Smith may enter his or her name as " Pat Smith" or "Pat Smith " or " Pat Smith ." These spaces before and after a name are undesirable and may hinder certain search operations. Fortunately for us, VBScript has a trim() function that removes all spaces before and after a string. The syntax for trim is shown in Listing 15.
Listing 15 General Command to Trim Spaces in a String
trimmed_string = trim(original_string)
For example, in our insert script, instead of this:
v_name=request.form("name")
you would first pass request.form("name") as a parameter to the trim() function:
v_name=trim(request.form("name"))
As an exercise, trim all control values prior to storing them in variables in your insert script. Remember to save your file. The result should look like Listing 16 (changes shown in bold).
Listing 16 insert.asp: Control Values Trimmed
<%
'
' Step 1: Open the database
'
set conn=server.createobject("adodb.connection")
conn.open("DBQ=" & server.mappath("visualtutorial.mdb") & _
";Driver={Microsoft Access Driver (*.mdb)};")
'
' Step 2: Read the user's information
'
v_name = trim(request.form("name"))
v_email = trim(request.form("email"))
v_age = trim(request.form("age"))
v_gender = trim(request.form("gender"))
v_comment = trim(request.form("comment"))
v_hideEmail = trim(request.form("hideEmail"))
'
' Step 3: Send a SQL INSERT query
'
conn.execute("insert into GuestBook " &_
"(name,email,hideEmail, age,gender,comment) " & _
"values (" & _
"'" & v_name & "'," & _
"'" & v_email & "'," & _
" " & v_hideEmail & " ," & _
" " & v_age & " ," & _
"'" & v_gender & "'," & _
"'" & v_comment & "')")
'
' Step 4: Close and exit the database
'
conn.close
set conn=nothing
%>
That's a pretty easy change. Next we'll look at how to handle the single-quote bug.
The Single-Quote Bug
In a SQL INSERT query, single quotes (') surround values that are stored in text and memo fields. If a user types a word that includes a single quote, such as don't, the single quotes won't match up, and your script will crash. The solution is to replace any single quote the user types with two consecutive single quotes (''). When the database sees two consecutive single quotes, it "does the right thing," and we'll leave it at that. Fortunately, VBScript has a replace() function that easily allows you to replace any single quote with two consecutive single quotes. The general syntax for replace is shown in Listing 17.
Listing 17 The replace Command
new_string = replace(original_string, find_pattern,replacement_pattern)
For example, suppose we're worried that a user named Conan O'Brien will fill out our form. Instead of simply doing this:
v_name=trim(request.form("name"))
we call replace() after the v_name assignment:
v_name=trim(request.form("name"))
v_name = replace(v_name, "'", "''")
As an exercise, replace all single quotes in single-line and multi-line text boxes in your script with two single quotes; then save your file. Listing 18 shows the result, with the new stuff in bold.
Listing 18 insert.asp: Code to Handle the Single-Quote Bug
<%
'
' Step 1: Open the database
'
set conn=server.createobject("adodb.connection")
conn.open("DBQ=" & server.mappath("visualtutorial.mdb") & _
";Driver={Microsoft Access Driver (*.mdb)};")
'
' Step 2: Read the user's information
'
v_name = trim(request.form("name"))
v_name = replace(v_name, "'", "''")
v_email = trim(request.form("email"))
v_email = replace(v_email, "'", "''")
v_age = trim(request.form("age"))
v_gender = trim(request.form("gender"))
v_comment = trim(request.form("comment"))
v_comment = replace(v_comment, "'", "''")
v_hideEmail = trim(request.form("hideEmail"))
'
' Step 3: Send a SQL INSERT query
'
conn.execute("insert into GuestBook " &_
"(name,email,hideEmail, age,gender,comment) " & _
"values (" & _
"'" & v_name & "'," & _
"'" & v_email & "'," & _
" " & v_hideEmail & " ," & _
" " & v_age & " ," & _
"'" & v_gender & "'," & _
"'" & v_comment & "')")
'
' Step 4: Close and exit the database
'
conn.close
set conn=nothing
%>
Remember, you only add this error-handling code for the variables associated with text boxes in your HTML form. Finally, let's look at how to handle blank fields in our HTML forms.
Blank fields
Users often leave fields blank. When this happens, you can either fill in a default value automatically, or display an error and have the user refill the form. We'll show you how to do the former. To detect a blank field, you compare the variable holding the value for that field with a blank string. To get a blank string, you use two double quotes in a row (""). If the value is blank, set the variable to a default value (see Listing 19).
Listing 19 General Code for Handling Blank Entries
IF variable="" THEN variable=default_value
Suppose we want to check whether the user has left the Name text box blank in our HTML form. If so, we'll replace the blank with "anonymous coward." Start with this:
v_name = trim(request.form("name"))
and add an IF-THEN statement following the v_name assignment:
v_name=trim(request.form("name"))
IF v_name="" THEN v_name = "anonymous coward"
As an exercise, substitute a default value for all controls in your script, with the exception of age and gender. After saving, your script should resemble Listing 20.
Listing 20 insert.asp: Code to Substitute Default Values for Blank Entries
<%
'
' Step 1: Open the database
'
set conn=server.createobject("adodb.connection")
conn.open("DBQ=" & server.mappath("visualtutorial.mdb") & _
";Driver={Microsoft Access Driver (*.mdb)};")
'
' Step 2: Read the user's information
'
v_name = trim(request.form("name"))
IF v_name="" THEN v_name="anonymous coward"
v_name = replace(v_name, "'", "''")
v_email = trim(request.form("email"))
IF v_email="" THEN v_email="Not specified"
v_email = replace(v_email, "'", "''")
v_age = trim(request.form("age"))
v_gender = trim(request.form("gender"))
v_comment = trim(request.form("comment"))
IF v_comment="" THEN v_comment="No comment given"
v_comment = replace(v_comment, "'", "''")
v_hideEmail = trim(request.form("hideEmail"))
IF v_hideEmail="" THEN v_hideEmail="No"
'
' Step 3: Send a SQL INSERT query
'
conn.execute("insert into GuestBook " &_
"(name,email,hideEmail, age,gender,comment) " & _
"values (" & _
"'" & v_name & "'," & _
"'" & v_email & "'," & _
" " & v_hideEmail & " ," & _
" " & v_age & " ," & _
"'" & v_gender & "'," & _
"'" & v_comment & "')")
'
' Step 4: Close and exit the database
'
conn.close
set conn=nothing
%>
The only variables where we didn't set a default value were v_age and v_gender. We don't need to check whether v_gender is blank because we set up our guest book form so that it always returns a value (either male or female)—it can't be blank. Although we could have checked for age being blank and substituted a default age, there's an even better solution, described in the next section.
Letters and Punctuation in Number Boxes
Malicious users often put non-numeric values such as letters and punctuation characters in number boxes. Whenever we have a text box where we want the user to type a number, we don't just want to check for blanks, we want to check whether the user typed a valid number! VBScript's IsNumeric() function takes a variable as a parameter and returns a response indicating whether the value is a number. The general syntax of IsNumeric is as follows:
IsNumeric(variable)
IsNumeric() returns TRUE if the variable contains a number and FALSE otherwise. To set a default value if the user doesn't fill in a number, we use IsNumeric() as the condition for an IF-THEN statement, as shown in Listing 21.
Listing 21 General Code to Check for a Non-Number and Substitute a Default Value
IF IsNumeric(variable)=FALSE THEN variable=default_value
If we want to check whether the user has typed a valid age and, if not, set 2 as the default age, we start with this:
v_age = trim(request.form("age"))
and add an IF-THEN statement with an IsNumeric() test in the condition, following the v_age assignment:
v_age=trim(request.form("age"))
IF IsNumeric(v_age)=FALSE THEN v_age = 2
As an exercise, for all numeric controls, check that the user has entered a valid number and substitute a default value if necessary (see Listing 22).
Listing 22 insert.asp: Code to Handle Non-Numeric Values in Number Fields
<%
'
' Step 1: Open the database
'
set conn=server.createobject("adodb.connection")
conn.open("DBQ=" & server.mappath("visualtutorial.mdb") & _
";Driver={Microsoft Access Driver (*.mdb)};")
'
' Step 2: Read the user's information
'
v_name = trim(request.form("name"))
IF v_name="" THEN v_name="anonymous coward"
v_name = replace(v_name, "'", "''")
v_email = trim(request.form("email"))
IF v_email="" THEN v_email="Not specified"
v_email = replace(v_email, "'", "''")
v_age = trim(request.form("age"))
IF IsNumeric(v_age)=FALSE THEN v_age=2
v_gender = trim(request.form("gender"))
v_comment = trim(request.form("comment"))
IF v_comment="" THEN v_comment="No comment given"
v_comment = replace(v_comment, "'", "''")
v_hideEmail = trim(request.form("hideEmail"))
IF v_hideEmail="" THEN v_hideEmail="No"
'
' Step 3: Send a SQL INSERT query
'
conn.execute("insert into GuestBook " &_
"(name,email,hideEmail, age,gender,comment) " & _
"values (" & _
"'" & v_name & "'," & _
"'" & v_email & "'," & _
" " & v_hideEmail & " ," & _
" " & v_age & " ," & _
"'" & v_gender & "'," & _
"'" & v_comment & "')")
'
' Step 4: Close and exit the database
'
conn.close
set conn=nothing
%>
And that's it for error handling. We are almost totally finished with our insert script. Ever wonder what happens after the insert script finishes? We cover this topic next.