' ------------------------------------------------------------------------------------- ' Name: MSSQLSafe ' Author: Markus Diersbock ' Created: 04/30/2006 ' ' Description: Creates filtered strings, safe for MS SQL Server tables. ' ' Arguments: StrIn Raw text ' StrType Constant to filter on ' RtnSize Size of returned string (Use NULL for no limit) ' ' Returns: Modified string (or NULL on error) ' ------------------------------------------------------------------------------------- ' ------ MSSQLSafe Constants ---- Const MSSQLSAFE_NAME = 1 Const MSSQLSAFE_ALPHA = 2 Const MSSQLSAFE_NUMBER = 3 Const MSSQLSAFE_MONEY = 4 Const MSSQLSAFE_PHONE_CLEAN = 5 Const MSSQLSAFE_PHONE_DELIM = 6 Const MSSQLSAFE_STREET = 7 Const MSSQLSAFE_ZIP = 8 Const MSSQLSAFE_EMAIL = 9 Const MSSQLSAFE_MISC = 10 Function MSSQLSafe(StrIn, StrType, RtnSize) Dim oRe, ValidChars, Rtn StrIn = Replace(StrIn, "'", "''") StrIn = Replace(StrIn, "--", "") StrIn = Replace(StrIn, "\", "") Select Case StrType Case MSSQLSAFE_NAME ValidChars = "[^'\-A-z0-9\s]" Case MSSQLSAFE_ALPHA ValidChars = "[^A-z]" Case MSSQLSAFE_NUMBER ValidChars = "[\D]" Case MSSQLSAFE_MONEY ValidChars = "[^0-9.]" Case MSSQLSAFE_PHONE_CLEAN ValidChars = "[\D]" Case MSSQLSAFE_PHONE_DELIM ValidChars = "[^\-0-9()+.x\s]" Case MSSQLSAFE_STREET ValidChars = "[^'\-.#A-z0-9\s]" Case MSSQLSAFE_ZIP ValidChars = "[^\-0-9]" Case MSSQLSAFE_EMAIL ValidChars = "[^\-_.@A-z0-9]" Case MSSQLSAFE_MISC ValidChars = "[^!@#$%^&*+:';,?=~`\-_.A-z0-9\s]" Case Else ValidChars = NULL End Select If NOT IsNull(ValidChars) then Set oRe = New RegExp oRe.Pattern = ValidChars oRe.IgnoreCase = True oRe.Global = True Rtn = oRe.Replace(StrIn, "") Set oRe = Nothing Rtn = Trim(Rtn) If NOT IsNull(RtnSize) And IsNumeric(RtnSize) then Rtn = left(Rtn, RtnSize) End If MSSQLSafe = Rtn Else MSSQLSafe = NULL End If End Function