Coding Cookbook/SQL Encode

From Wikibooks, open books for an open world
Jump to navigation Jump to search

Encode various datatypes for safe usage in SQL commands.

VBScript solution[edit | edit source]

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