Last modified on 22 March 2006, at 15:27

Coding Cookbook/SQL Encode

Encode various datatypes for safe usage in SQL commands.

VBScript solutionEdit

function SQLEncode (vInput)
        dim CurrentLocale
        select case VarType(vInput)
                case 0,1  ' empty, null
                        SQLEncode = "NULL"
                case 2,3  ' integer, longint
                        SQLEncode = vInput
                case 4,5  ' single, double
                        CurrentLocale = GetLocale
                        SetLocale ("en-us")
                        SQLEncode = CStr(vInput)
                        SetLocale (CurrentLocale)
                case 7    ' date
                        SQLEncode = "#" & _
                                DatePart("yyyy", vInput) & "-" & _
                                DatePart("m",    vInput) & "-" & _
                                DatePart("d",    vInput) & " " & _
                                DatePart("h",    vInput) & ":" & _
                                DatePart("n",    vInput) & ":" & _
                                DatePart("s",    vInput) & "#"
                case 8    ' string
                        SQLEncode = vInput
                        SQLEncode = Replace (SQLEncode, chr(0), "")
                        SQLEncode = Replace (SQLEncode, "'", "''")
                        SQLEncode = "'" & SQLEncode & "'"
        end select

end function