Microsoft KB Archive/246069: Difference between revisions
m (Text replacement - "&" to "&") |
m (Text replacement - """ to """) |
||
Line 60: | Line 60: | ||
== MORE INFORMATION == | == MORE INFORMATION == | ||
The error message above occurs when you try to submit a name with an apostrophe, (for example | The error message above occurs when you try to submit a name with an apostrophe, (for example "O'Malley"), and then insert that name into a SQL Server clause without first "escaping" it. This can result in a SQL Server clause that resembles the following: | ||
<pre class="codesample">INSERT INTO Table (txtName VALUES ('O'Malley') | <pre class="codesample">INSERT INTO Table (txtName VALUES ('O'Malley') | ||
</pre> | </pre> | ||
Because SQL Server uses the apostrophe as a text delimiter, the apostrophe in the name inadvertently signifies an end to the data, which causes the error message to occur. This text can be | Because SQL Server uses the apostrophe as a text delimiter, the apostrophe in the name inadvertently signifies an end to the data, which causes the error message to occur. This text can be "escaped" by using two apostrophes, for example: | ||
<pre class="codesample">INSERT INTO Table (txtName VALUES ('O''Malley') | <pre class="codesample">INSERT INTO Table (txtName VALUES ('O''Malley') | ||
</pre> | </pre> | ||
Line 92: | Line 92: | ||
tmpText2 = tmpText1 | tmpText2 = tmpText1 | ||
' replace each CR with a line break tag and CR-LF | ' replace each CR with a line break tag and CR-LF | ||
tmpText2 = Replace(tmpText2,Chr(13), | tmpText2 = Replace(tmpText2,Chr(13),"<br>" & vbCrLf) | ||
' replace each TAB character with four non-breaking space tags | ' replace each TAB character with four non-breaking space tags | ||
tmpText2 = Replace(tmpText2,Chr(9), | tmpText2 = Replace(tmpText2,Chr(9),"    ") | ||
' return the fixed string | ' return the fixed string | ||
FixForHTML = tmpText2 | FixForHTML = tmpText2 | ||
Line 104: | Line 104: | ||
The following steps demonstrate how to use the above functions to create a simple guestbook application using ASP and a Microsoft Access database: | The following steps demonstrate how to use the above functions to create a simple guestbook application using ASP and a Microsoft Access database: | ||
<ol> | <ol> | ||
<li><p>Copy the following ASP code and save it to a folder in your Web site with at least IIS script access enabled and Copy the following ASP code and save it to a folder in your Web site with at least IIS | <li><p>Copy the following ASP code and save it to a folder in your Web site with at least IIS script access enabled and Copy the following ASP code and save it to a folder in your Web site with at least IIS "script" access enabled and '''Everyone - Change''' NTFS permissions:</p> | ||
<pre class="codesample"><% @Language= | <pre class="codesample"><% @Language="VBScript" %> | ||
<% | <% | ||
Option Explicit | Option Explicit | ||
Line 119: | Line 119: | ||
' get some environment variables | ' get some environment variables | ||
With Request | With Request | ||
strMethod = UCase(.ServerVariables( | strMethod = UCase(.ServerVariables("REQUEST_METHOD")) | ||
strTitle = UCase(.ServerVariables( | strTitle = UCase(.ServerVariables("SERVER_NAME")) & " Guestbook" | ||
strURL = LCase(.ServerVariables( | strURL = LCase(.ServerVariables("URL")) | ||
End With | End With | ||
' open our database connection | ' open our database connection | ||
Set objCN = Server.CreateObject( | Set objCN = Server.CreateObject("ADODB.Connection") | ||
objCN.Open | objCN.Open "driver={Microsoft Access Driver (*.mdb)};uid=admin;pwd=;" & _ | ||
"dbq=" & Server.MapPath(".\guestbook.mdb") | |||
' if the request was a POST then a guest submitted | ' if the request was a POST then a guest submitted | ||
If strMethod = | If strMethod = "POST" Then | ||
Dim strName, strComments | Dim strName, strComments | ||
' get the form data and format it | ' get the form data and format it | ||
strName = FixForSQL(Request.Form( | strName = FixForSQL(Request.Form("txtName")) | ||
strComments = FixForSQL(Request.Form( | strComments = FixForSQL(Request.Form("txtComments")) | ||
' build the SQL string | ' build the SQL string | ||
strSQL = | strSQL = "INSERT INTO tblGuestbook (txtName, txtComments)" | ||
strSQL = strSQL & | strSQL = strSQL & " VALUES ('" & strName & "','" & strComments & "')" | ||
' execute the SQL | ' execute the SQL | ||
objCN.Execute(strSQL) | objCN.Execute(strSQL) | ||
Line 143: | Line 143: | ||
' create a SQL string to read all guest entries | ' create a SQL string to read all guest entries | ||
strSQL = | strSQL = "SELECT * FROM tblGuestbook" | ||
Set objRS = objCN.Execute(strSQL) | Set objRS = objCN.Execute(strSQL) | ||
Line 158: | Line 158: | ||
%> | %> | ||
<table border= | <table border="1"> | ||
<tr> | <tr> | ||
<th>Name</th> | <th>Name</th> | ||
Line 167: | Line 167: | ||
While Not objRS.EOF | While Not objRS.EOF | ||
With Response | With Response | ||
.Write | .Write "<tr>" & vbCrLf | ||
.Write | .Write "<td>" & FixForHTML(objRS("txtName")) & "</td>" & vbCrLf | ||
.Write | .Write "<td>" & FixForHTML(objRS("txtComments")) & "</td>" & vbCrLf | ||
.Write | .Write "</tr>" & vbCrLf | ||
End With | End With | ||
objRS.MoveNext | objRS.MoveNext | ||
Line 179: | Line 179: | ||
<% End If %> | <% End If %> | ||
<form action= | <form action="<%=strURL%>" method="POST"> | ||
<table> | <table> | ||
<tr> | <tr> | ||
<td>Name</td> | <td>Name</td> | ||
<td><input type= | <td><input type="text" name="txtName"><br> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td>Comments</td> | <td>Comments</td> | ||
<td><textarea name= | <td><textarea name="txtComments"></textarea><br> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
<td colspan= | <td colspan="2"><input type="submit" value="Submit Comments"> | ||
</tr> | </tr> | ||
</form> | </form> | ||
Line 208: | Line 208: | ||
Dim tmpText2 | Dim tmpText2 | ||
tmpText2 = tmpText1 | tmpText2 = tmpText1 | ||
tmpText2 = Replace(tmpText2,Chr(13), | tmpText2 = Replace(tmpText2,Chr(13),"<br>" & vbCrLf) | ||
tmpText2 = Replace(tmpText2,Chr(9), | tmpText2 = Replace(tmpText2,Chr(9),"    ") | ||
FixForHTML = tmpText2 | FixForHTML = tmpText2 | ||
End Function | End Function |
Latest revision as of 13:50, 21 July 2020
Article ID: 246069
Article Last Modified on 8/8/2007
APPLIES TO
- Microsoft Internet Information Services 5.0
This article was previously published under Q246069
SUMMARY
When you are working with records in a database using Microsoft Active Server Pages (ASP) for Internet Information Services (IIS), errors may occur when the text has not been properly formatted for creating a SQL Server clause.
For example, when you create an ASP page to store information posted from an HTML form to a database, you may see an error message similar to the following:
MORE INFORMATION
The error message above occurs when you try to submit a name with an apostrophe, (for example "O'Malley"), and then insert that name into a SQL Server clause without first "escaping" it. This can result in a SQL Server clause that resembles the following:
INSERT INTO Table (txtName VALUES ('O'Malley')
Because SQL Server uses the apostrophe as a text delimiter, the apostrophe in the name inadvertently signifies an end to the data, which causes the error message to occur. This text can be "escaped" by using two apostrophes, for example:
INSERT INTO Table (txtName VALUES ('O''Malley')
Obviously, it is not ideal to mandate that all users on a Web site enter two apostrophes for each desired apostrophe. In addition, when text is later extracted from a database, certain formatting characters are ignored during HTML parsing on a client (for example CR-LF, TAB, and so on.)
An ASP solution
The following ASP code defines two functions that are designed to handle some simple parsing of information for input/output of SQL Server data, and can easily be expanded to incorporate more functionality:
FixForSQL() parses a string for input to SQL Server
Function FixForSQL(tmpText1) ' define a working variable Dim tmpText2 ' populate our working variable tmpText2 = tmpText1 ' compact a CR-LF sequence as CR to save space tmpText2 = Replace(tmpText1,vbCrLf,Chr(13)) ' replace each apostrophe with two apostrophes tmpText2 = Replace(tmpText2,Chr(39),String(2,39)) ' return the fixed string FixForSQL = tmpText2 End Function
FixForHTML() parses a string for output to HTML
Function FixForHTML(tmpText1) ' define a working variable Dim tmpText2 ' populate our working variable tmpText2 = tmpText1 ' replace each CR with a line break tag and CR-LF tmpText2 = Replace(tmpText2,Chr(13),"<br>" & vbCrLf) ' replace each TAB character with four non-breaking space tags tmpText2 = Replace(tmpText2,Chr(9)," ") ' return the fixed string FixForHTML = tmpText2 End Function
Practical example - Guestbook application
The following steps demonstrate how to use the above functions to create a simple guestbook application using ASP and a Microsoft Access database:
Copy the following ASP code and save it to a folder in your Web site with at least IIS script access enabled and Copy the following ASP code and save it to a folder in your Web site with at least IIS "script" access enabled and Everyone - Change NTFS permissions:
<% @Language="VBScript" %> <% Option Explicit ' define our variables Dim strMethod Dim strURL Dim strTitle Dim strSQL Dim objCN Dim objRS ' get some environment variables With Request strMethod = UCase(.ServerVariables("REQUEST_METHOD")) strTitle = UCase(.ServerVariables("SERVER_NAME")) & " Guestbook" strURL = LCase(.ServerVariables("URL")) End With ' open our database connection Set objCN = Server.CreateObject("ADODB.Connection") objCN.Open "driver={Microsoft Access Driver (*.mdb)};uid=admin;pwd=;" & _ "dbq=" & Server.MapPath(".\guestbook.mdb") ' if the request was a POST then a guest submitted If strMethod = "POST" Then Dim strName, strComments ' get the form data and format it strName = FixForSQL(Request.Form("txtName")) strComments = FixForSQL(Request.Form("txtComments")) ' build the SQL string strSQL = "INSERT INTO tblGuestbook (txtName, txtComments)" strSQL = strSQL & " VALUES ('" & strName & "','" & strComments & "')" ' execute the SQL objCN.Execute(strSQL) End If ' create a SQL string to read all guest entries strSQL = "SELECT * FROM tblGuestbook" Set objRS = objCN.Execute(strSQL) %> <html> <head><title><%=strTitle%></title></head> <body> <h1><%=strTitle%></h1> <% ' only show the guestbook when there are entries If Not objRS.EOF Then %> <table border="1"> <tr> <th>Name</th> <th>Comments</th> </tr> <% ' loop through the database While Not objRS.EOF With Response .Write "<tr>" & vbCrLf .Write "<td>" & FixForHTML(objRS("txtName")) & "</td>" & vbCrLf .Write "<td>" & FixForHTML(objRS("txtComments")) & "</td>" & vbCrLf .Write "</tr>" & vbCrLf End With objRS.MoveNext Wend %> </table> <% End If %> <form action="<%=strURL%>" method="POST"> <table> <tr> <td>Name</td> <td><input type="text" name="txtName"><br> </tr> <tr> <td>Comments</td> <td><textarea name="txtComments"></textarea><br> </tr> <tr> <td colspan="2"><input type="submit" value="Submit Comments"> </tr> </form> </body> </html> <% Function FixForSQL(tmpText1) Dim tmpText2 tmpText2 = tmpText1 tmpText2 = Replace(tmpText1,vbCrLf,Chr(13)) tmpText2 = Replace(tmpText2,Chr(39),String(2,39)) FixForSQL = tmpText2 End Function Function FixForHTML(tmpText1) Dim tmpText2 tmpText2 = tmpText1 tmpText2 = Replace(tmpText2,Chr(13),"<br>" & vbCrLf) tmpText2 = Replace(tmpText2,Chr(9)," ") FixForHTML = tmpText2 End Function %>
- Open Microsoft Access, create a new database, and then save it as Guestbook.mdb in the same folder as the above ASP page. Create a new table in the database named tblGuestbook with the following fields:
Field Name Data Type Field Size txtName Text 50 txtComments Memo N/A
Testing the Guestbook Application
- When you browse the page for the first time, no data is displayed. When information has been submitted, any guestbook entries will appear. Entering apostrophes in names (for example O'Brien, O'Sullivan, and so on) or contractions (such as don't, can't, and so on) are properly formatted and stored in the database.
- When information has been submitted, any guestbook entries will appear. Entering apostrophes in names (for example O'Brien, O'Sullivan, and so on) or contractions (such as don't, can't, and so on) are properly formatted and stored in the database.
- Entering apostrophes in names (for example O'Brien, O'Sullivan, and so on) or contractions (such as don't, can't, and so on) are properly formatted and stored in the database.
REFERENCES
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. For more information about Microsoft scripting technologies, visit the Microsoft Developer Network web site:
Additional query words: iis iis5 iis 5.0
Keywords: kbinfo KB246069