About   -   Contact   -   Purchase   -   Search   -   What's New

SQL String Validation
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)

   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.


Top of Page

Legal Notice

Ken Howe 2011