Brent E. Curtis
Microsoft Corporation
Created: August 4, 1997
In database applications that use Transact-SQL, a run-time error is generated if a user submits data to the database that contains an apostrophe such as in the name "O'Brian." So, it is necessary to prevent this error by altering the string prior to submitting it to the database.
The problem with the single quotation mark arises from the fact that ANSI SQL uses the single quotation mark as a string delimiter. Therefore, when a user submits a string that contains a single quotation mark as part of the string, the parser interprets this single quotation mark as the end of the string. To see this more clearly, look at how an INSERT statement is constructed:
INSERT INTO TableName (Field1, Field2) VALUES('Value1', 'Value2')
If a user submitted the name O'Brian for Value1, the above INSERT statement would become:
INSERT INTO TableName (Field1, Field2) VALUES('O'Brian', 'Value2')
It is easy to see that sending O'Brian for Value1 created an improperly formed INSERT statement that would result in an error being returned to the user. In order to avoid this error, a second single quotation mark can be added before submitting the string to the database. This will solve the problem because the single quotation mark is also used to instruct the parser to interpret the next character as a literal character. Therefore, if O''Brian is submitted for Value1 as shown below, the parser will correctly interpret the apostrophe as part of the string and not as a delimiter.
INSERT INTO TableName (Field1, Field2) VALUES('O''Brian', 'Value2')
The remainder of this article will show how to create a reusable class module that performs this function for use in Microsoft® Visual Basic® applications as well as how to include that class module in an in-process Component Object Model (COM) Server.
I have chosen a class module to demonstrate this technique because once the code exists in the class, it can easily be included in future projects or compiled as an ActiveX™ dynamic-link library (DLL) that can be used by other applications.
In Visual Basic, begin a new ActiveX DLL project. The new project will be named Project 1 and will contain a class module named Class 1.
In the Properties window for Class 1, type CValidateData in the Name property. In order to use the class as a source code module that can be included in any project, set the Instancing property to 1 – Private. To create an ActiveX DLL, leave the Instancing property on its default value of 5 – MultiUse.
After you have created the class, add the DoubleApostrophe method by inserting the following code into the class module:
Option Explicit
Public Function DoubleApostrophe(ByVal sBr As String) As String
Dim lPos As Long
Dim sBl As String
If Len(sBr) = 0 Then Exit Function
lPos = InStr(sBr, Chr$(39))
While lPos <> 0
sBl = sBl & Left$(sBr, lPos) & Chr$(39)
sBr = Right$(sBr, Len(sBr) - lPos)
lPos = InStr(sBr, Chr$(39))
Wend
DoubleApostrophe = sBl & sBr
End Function
The following example demonstrates how to use the CValidateData class in a Visual Basic application.
Dim oValDat As New CValidateData
txtAlteredMessage = oValDat.DoubleApostrophe (txtOriginalMessage)The result will be that the original message will appear in the txtAlteredMessage text box with two apostrophes back-to-back. Of course, within an actual application, you would use the DoubleApostrophe method to prepare text strings for submission to a database.
The following example demonstrates how to compile the CValidateData class as an ActiveX DLL and register it for use on the server.
Once the DLL has been created, install and register it on the Web server to make it available as an Active Server Component. Use the Visual Basic Setup wizard to build a setup program that installs and registers dbutility.dll and the required dependency files.
The following example demonstrates how to use the object to validate data from a Web page before it is inserted into a Microsoft SQL Server™ table via ActiveX Data Objects (ADO) on the server.
Complete the following to create the sample table for use in the Web example:
The following example uses two Web pages to demonstrate how to implement the ValidateData object. The initial page is named Post.asp and is responsible for inserting the data into the table. The second page is named Get.asp and is used to retrieve the submitted data from the table.
Paste the following code into a new file named Post.asp.
<%
' Test to see if user has clicked the "Enter Message" button.
Action = Left(UCase(Request("Action")),5)
If Action = "ENTER" Then
' Create an instance of the CValidateData object.
Set Data = Server.CreateObject("DbUtility.CValidateData")
' Process the input data and assign to local variables for easier reference.
Message = Data.DoubleApostrophe(Request("Message"))
FirstName = Data.DoubleApostrophe(Request("FirstName"))
MiddleName = Data.DoubleApostrophe(Request("MiddleName"))
LastName = Data.DoubleApostrophe(Request("LastName"))
' Build the INSERT statement.
sql = "INSERT INTO tblMessages (fName, mName, lName, Message) "
sql = sql & " VALUES ( "
sql = sql & "'" & FirstName & "', "
sql = sql & "'" & MiddleName & "', "
sql = sql & "'" & LastName & "', "
sql = sql & "'" & Message & "')"
' Open a connection to the database.
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "dsn=Messages;uid=sa;pwd="
' Execute the INSERT statement.
Set rs = cn.Execute(sql)
End if
%>
<HTML>
<BODY>
<CENTER>
<-- Display the altered message that was inserted into the database. -->
<% If Action="ENTER" Then %>
Message that was submitted to the database:
<TABLE>
<TR>
<TD ALIGN="center">
<B><% =Message %></B>
</TD>
</TR>
</TABLE>
<% end if %>
<P>
<A HREF="Get.asp">Get Messages</A>
<P><BR>
<-- Use selfreferencing to submit the data and allow the user to enter more records without forcing the user to a separate confirmation page. -->
<FORM ACTION="post.asp" METHOD="POST">
<TABLE>
<TR>
<TD>First Name:</TD>
<TD><INPUT TYPE="Text" NAME="FirstName"></TD>
</TR>
<TR>
<TD>Middle Name:</TD>
<TD><INPUT TYPE="Text" NAME="MiddleName"></TD>
</TR>
<TD>Last Name:</TD>
<TD><INPUT TYPE="Text" NAME="LastName"></TD>
</TR>
<TR>
<TD VALIGN="top">Message:</TD>
<TD><TEXTAREA ROWS="10" COLS="60" NAME="Message"></TD>
</TR>
</TABLE>
<P>
<INPUT TYPE=SUBMIT NAME="Action" VALUE="Enter Message">
</FORM>
<TABLE>
</CENTER>
</BODY>
</HTML>
Next, paste the following code into a file named Get.asp.
<%
' Build the SELECT statement.
SQLQuery = "SELECT * FROM tblMessages"
' Open a connection to the database.
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "dsn=Messages;uid=sa;pwd="
' Execute the SELECT statement.
Set rs = cn.Execute(SQLQuery)
%>
<HTML>
<BODY>
<CENTER>
<P><BR><BR>
<P>
<a href="Post.asp">Enter More Messages</a><P>
<TABLE>
<tr>
<td colspan="3">Name</td>
<td> </td>
<td>Message</TD>
</tr>
<-- Loop through the rows in the database table and fill the table. -->
<% While Not rs.EOF %>
<TR>
<TD>
<% =rs("fName") %>
</TD>
<TD>
<% =rs("mName") %>
</TD>
<TD>
<% =rs("lName") %>
</TD>
<td> </td>
<TD>
<% =rs("Message") %>
</TD>
</TR>
<-- Retrieve the next row from the database table. -->
<%
rs.MoveNext
Wend
%>
</TABLE>
</CENTER>
</BODY>
</HTML>
Place both files in the same directory on the Web server. Note that, in order for the code to be executed, the directory must be configured in Microsoft Internet Information Server (IIS) to be a virtual directory with execute permissions.
The DoubleApostrophe method is a good example of the type of procedure that benefits the most from being packaged in a Visual Basic class because it is useful to a broad range of database applications. Utilizing classes in your applications allows you to shorten the development cycle of future Visual Basic applications by reusing existing code. In addition, by packaging the class as a COM server (.dll), it becomes even more reusable because programs written in any language that can use a COM server (including VBScript) can use that code. As you become more familiar with classes and begin thinking about the types of procedures that should be grouped together, you can begin to develop an application architecture that maximizes reuse by building application-specific functionality upon layers of reusable code that become progressively more general. Once the ValidateData class exists, you can include additional methods at later dates to handle other issues that may arise.