Allowing free text fields to be written directly to databases is a
potential minefield due to the opportunity for these fields to contain
illegal characters such as single quotes, double quotes, percentages, asterisks,
etc. This is because these characters can effect the context of the query.
To guard against this problem, the easiest solution is to strip out all occurrences
of such characters. The function below searches for all these characters
and by replacing them with nothing, removes them.
Function strReplaceSQL(strTxt)
Dim strIllegalChar
Dim strChar
Dim intPos
Dim intCount
If strTxt = "" then Exit Function
'double quote, percentage, single quotes, asterisks
strChars = chr(34) & chr(37) & chr(39) & chr(42)
'loop thru illegal chars
For intCount = 1 to Len(strChars)
strIllegalChar = Mid(strChar, intCount, 1)
intPos = Instr(strText, strIllegalChar)
Do While intPos > 0
'remove illegal chars
strTxt = Replace(strTxt, strIllegalChar, "")
intPos = Instr(strText, strIllegalChar)
Loop
Next
strReplace = strTxt
End Function
This code can be used in ASP or VB. If VB, declare the variables as the
correct string or integer datatype. In addition, illegal characters can be
omitted or added depending upon its application.
|
