Classic ASP functions for database queries and SQL injection protection
by johna | July 28, 2014 | Classic ASP Web Development
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.
String fields
Quotes adds single quotes around the string, and escapes single quotes within the string.
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.
Boolean fields
CBit returns 1 or 0 to represent true or false.
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
Related Posts
Another pointless project - the programmable digital watch
by johna | January 20, 2025
I've come up with yet another pointless project. Would you like a watch that you could program yourself - but not a "smart watch"?
Converting dBase IV programs to run in the browser
by johna | September 13, 2024
Some pointless entertainment trying to get some old dBase programs running in the browser.
How to set up a debugging using the Turnkey Linux LAMP stack and VS Code
by johna | December 19, 2023
The second part in my guide to setting up a website and database using the Turnkey Linux LAMP stack.
Comments
There are no comments yet. Be the first to leave a comment!