Classic ASP functions for database queries and SQL injection protection

John Avis by | July 28, 2014 | Classic ASP

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.
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

Related Posts

Classic ASP

Sending email using Amazon SES with Classic ASP

by John Avis | March 21, 2017

I recently needed to change a client's website to send emails using Amazon SES and encountered a few issues.


Classic ASP

Classic ASP class constructors with parameters

by John Avis | June 8, 2016

Although probably no one cares about Classic ASP, except those who still need to support it, I was refreshing my memory today on how to use classes in Classic ASP and found a couple of options for simulating constructors with parameters.


Classic ASP

Classic ASP cookie with no name hacking attack causing error 80004005

by John Avis | July 31, 2015

As of July 2015, many of my Classic ASP websites have started logging strange errors, either 80004005 or not reporting any error code at all.

Comments

There are no comments yet. Be the first to leave a comment!

Leave a Comment
Tags
ASP.NET Html Forms ASP.NET MVC ASP.NET Web Forms ASP.NET Web Pages Bootstrap C# Classic ASP Cool Websites Databases eBay and PayPal Electrical Repairs General Hardware HTML/CSS Jquery/Javascript Media Center Mobile Phones Responsive Web Design SEO and Social Networking Web Design Web Development Web Security web+db Website Hosting Windows XP

About me

...mostly about web development and programming, with a little bit of anything else related to the Internet, computers and technology.

Subscribe

Get the latest posts delivered to your inbox.