| Answer: |
Just as VBScript uses a pair of " marks inside a literal string to represent a single embedded ", so does SQL use a pair of ' marks within a literal to mean a single embedded '.
That is, with VBScript you can do:
<%
demoText = "He said, ""This should work!"" "
Response.Write demoText
%>
|
and the line
He said, "This should work"
will appear in the browser.
So with SQL, you do something similar:
<%
SQL = "INSERT INTO table (userName) VALUES('O''Brien')"
someConnection.Execute SQL
%>
|
That will insert the name O'Brien into the database.
What do you do when you get a value from a FORM input and need to check for an embedded apostrophe? Use the VBScript REPLACE function.
Thus:
<%
valueForSQL = Replace( Request.Form("userName"), "'", "''" )
SQL = "INSERT INTO table (userName) VALUES(" & valueForSQL & ")"
someConnection.Execute SQL
%>
|
If the strings used in that REPLACE are hard to read, you could do this, instead:
<%
SQLQT = Chr(39) ' this is the apostrophe character!
...
valueForSQL = Replace( Request.Form("userName"), SQLQT, SQLQT & SQLQT )
...
%>
|
And that makes it pretty clear that you are replacing a single apostrophe with a pair of them.
Zach Mattson offers a nifty function to automatically replace all instances of single apostrophes with double apostrophes in the Request.Form collection!
|
I use a function that takes the entire form collection and stuffs it into another collection (a Dictionary object) but, as it moves the contents from the Request.Form collection over to the Dictionary object, it replaces all single apostrophes with double apostrophes.
<%
'*********************************************************
Function RemoveCharacters()
dim frm,item
Set frm = Server.CreateObject("Scripting.Dictionary")
frm.CompareMode=1
For each Item in Request.Form
frm.Add Cstr(Item), Replace(Request.Form(Item),"'","''")
Next
Set RemoveCharacters = frm
End Function
'*************************************************
%>
|
Calling and using this function is a breeze:
<%
dim myform
Set myform = RemoveCharacters()
myform("formfieldname") ' refer to a specific form field
%>
|
Big Thanks To Bill Wilkinson for his help on that one! |
