Classic ASP functions for database queries and SQL injection protection

If writing database queries in SQL in Classic ASP for SQL Server, rather than using ADO or parameterised queries, you can use these functions to correctly format your data and protect against SQL injection.

Numeric fields

ForceNumeric ensures that only numeric values are passed.

Function ForceNumeric(sngValue)
	If IsNull(sngValue) Then
		ForceNumeric = 0
	ElseIf sngValue = "" Or Not IsNumeric(sngValue)  Then
		ForceNumeric = 0
	Else
		ForceNumeric = CSng(sngValue)
	End If
End Function

String fields

Quotes adds single quotes around the string, and escapes single quotes within the string.

Function Quotes(strValue)
	If IsNull(strValue) Then
		Quotes = "null"
	Else
		Quotes = "'" & Replace(strValue, "'", "''") & "'"
	End If
End Function

Date fields

FormatDateSql adds quotes and formats dates to a SQL friendly format (yyyy-mm-dd). If not a valid date or a null value then it returns "null".

FormatDateTimeSql does the same except it adds the time too, down to seconds.

Function FormatDateSql(datValue)
	If IsNull(datValue) Then 
		FormatDateSql = "Null"
	ElseIf datValue = "" Then
		FormatDateSql = "Null"
	ElseIf IsDate(datValue) Then
		FormatDateSql = Quotes(Year(datValue) & "-" & Right("0" & Month(datValue), 2) & "-" & Right("0" & Day(datValue), 2))
	Else
		FormatDateSql = "Null"
	End If
End Function

Function FormatDateTimeSql(datValue)
	If IsNull(datValue) Then 
		FormatDateTimeSql = "Null"
	ElseIf datValue = "" Then
		FormatDateTimeSql = "Null"
	ElseIf IsDate(datValue) Then
		FormatDateTimeSql = Quotes(Year(datValue) & "-" & Right("0" & Month(datValue), 2) & "-" & Right("0" & Day(datValue), 2) & " " & Right("0" & Hour(datValue), 2) & ":" & Right("0" & Minute(datValue), 2) & ":" & Right("0" & Second(datValue), 2))
	Else
		FormatDateTimeSql = "null"
	End If
End Function

Boolean fields

CBit returns 1 or 0 to represent true or false.

Function CBit(booleanvalue)
	If booleanvalue Then
		CBit = 1
	Else
		CBit = 0
	End If
End Function