Classic ASP functions for database queries and SQL injection protection

John Avis by | 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.
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

Computers & Internet Web Development Website Hosting

500 Internal Server Error after migrating from IIS 7.5 to IIS 10

by John Avis | November 4, 2019

As support ends for Microsoft Windows Server 2008 I have recently gone through migrating some websites to a new server running Windows Server 2016 and IIS 10 but some of the websites did not work.


Jquery/Javascript Web Development

tagInput: A simple jQuery plugin for tag entry using Bootstrap 4

by John Avis | October 15, 2019

For a website project I needed a way to enter multiple tags. I just wanted something simple that I could easily modify to suit my own needs, so I wrote my own.


ASP.NET Web Forms Web Development

ASP.NET bug with RadioButton GroupName in Repeater

by John Avis | September 1, 2019

I rediscovered a bug in ASP.NET that affects RadioButtons inside repeaters. Here is my solution to the problem.

Comments

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

Leave a Comment

Tags

About me

...random postings about web development and programming, Internet, computers, electronics and automotive topics.

Subscribe

Get the latest posts delivered to your inbox.