<

Dynamically Parameterized Queries for Classic ASP

Published on
11,779 Points
5,779 Views
Last Modified:
Approved
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :)

To start, I want to make sure everyone understands the importance of utilizing parameterized queries.

They are useful in preventing SQL Injection, and reducing the size of as well as reducing the size of the system table, syscacheobjects.

In my mind there is no more efficient method to programming and development than organizing your code into re-usable chunks.

Any time you can re-use something you have already developed, should be placed in an include file and called when the chunk of code is needed.

Thus said, I had a need to make some serious database calls and as everyone knows, simply cleaning the input is not always the most efficient and effective method.  It is always better to force the data types of the parameters you are passing to the database.  It is pretty easy to use parameterized queries in code to do this, but why bother writing and re-writing basically the same code every time you need to use it.

So, I came up with a method that will do everything you will need it to do for dynamically parameterized queries when developing a classic ASP web application.

I also added in some recordset paging.

Please note this can also be ported to Visual Basic and complied into a COM object for even more efficiency.

Please see the code below for the usage and class files for this tip.

Thanks for reading!
~KevP

USAGE:

< %
'*****************************************************************************
'*****************************************************************************
'
' This code can be used anywhere you like, all I ask is that you keep this
' notice here, so people know who actually made it! =D  Thanks!
'
' This code was developed by Kevin Pirnie, c/o o7th Web Design
' support@07th.com :: http://www.07th.com
'
'*****************************************************************************
'*****************************************************************************
'------------------------------------------------------------------------------
'------------------------------------------------------------------------------
'Dimension our variables
Dim objData, intPage
Dim intCols, intRows, intTotPages, intTotRecs, strDisplayPaging, strConnString
'------------------------------------------------------------------------------
'------------------------------------------------------------------------------
'Get the page we are on, if any
intPage = Request.QueryString("p")
'If we don't have a page, set it to 1
If Not(IsNumeric(intPage)) Then intPage = 1
'------------------------------------------------------------------------------
'------------------------------------------------------------------------------
'Set our data object to the class
Set objData = New DBv1
	With objData 'just because i don't feel like typing out objData everytime I need it
		'Set our command type: 1 = Inline SQL Statement, 4 = Stored Procedure
		.intCommandType = 		1
		'Pass our Connection String ... if we have one, uncomment the line below, and comment the ... section
		'.strConnString = 		strConnString
		'Or we can simply build it here, just uncomment the lines in between ...
		' ...
		.intDBType = 			3
		'Supported database types:
		'	1 = SQL 2000
		'	2 = SQL 2005
		'	3 = SQL 2005 Express
		'	4 = MS Access
		'	6 = MS Access 2007
		'	7 = MySQL
		'	8 = Borland Interbase '<- requires the proper driver installed on the server
		.strDBServer = 			"The Address to your Database Server"
		.strDBUser = 			"The Database Username"
		.strDBPassword = 		"The Database Password"
		.strDBDatabase = 		"The default database"
		' ...
		'Pass the class our SQL statement, use ? in the WHERE clause
		'You can also use this to fire off a stored procedure
		.strQry = 				"Select Field1, Field2 From Table Where Field3 = ? Or Field4 = ?"
		'Pass an array of values to look for in our WHERE clause
		.arrParamValues = 		Array(Val1, Val2) 'If none, use ""
		'Pass an array of Data Types for our values (These are ADO DataTypes, and the list of them can be found here -> http://www.w3schools.com/ADO/ado_datatypes.asp)
		'Only use the numeric value for this, we also only support the following:
		'2, 3, 4, 5, 6, 7, 11, 14, 72, 128, 129, 200, 203, 204
		.arrParamDataTypes = 	Array(200, 200) 'If none, use ""
		'If we fire a stored procedure, and it has an output variable
		'.intRetDataType = 		3 'Set the return value data type
		'.intRetSize = 			4 'Set the return value size
		'Do we want to page the results?
		.boolUsePaging = 		True 'or False
		'How many records per page do we want to display?
		.intRecPerPage = 		10 'any numeric value
		'What page number are we on?
		.intPageNumber = 		intPage
		'What page are we displaying these results?
		.strPagingPage = 		"usage.asp?a=a" 'We use a=a because the paging method expects a querystring to already be available
		'What should we use to display for the left arrows?
		.strPagingLeft = 		"«"
		'What should we use to display for the right arrows?
		.strPagingRight = 		"»"
		'Execute our query, and store the resulting 2d array in a variable for use later
		'If we fire a stored procedure and it has an output variable, this variable will equal that value
		tmpArray = 				.ExecuteQry()
		'Get the total number of pages returned from the recordset
		intTotPages = 			.intTotalPages
		'Get the total number of records returned from the recordset
		intTotRecs = 			.intTotalRecords
		'Get our paging links for the recordset
		strDisplayPaging = 		.RecordPaging()
	End With 'End our With block
'Clean up the object
Set objData = Nothing
'------------------------------------------------------------------------------
'------------------------------------------------------------------------------
'Now that we have done all this, let's see what the results are.
'First we check to see if we have an array
If IsArray(tmpArray) Then
	'We have an array, so let's work with it
	'I'd like to put in the info about the paging, total records & total pages
	Response.Write("<strong>" & intTotRecs & "</strong> total records in <strong>" & intTotPages & "</strong> pages.")
	'Now I want to display my paging links
	Response.Write(strDisplayPaging)
	'Now Let's display the results of the recordset...
	Response.Write("
" & VbCrLf)
	'Since I know how many columns, I will simply put the table header here
	Response.Write("
" & VbCrLf)
	Response.Write("
" & VbCrLf)
	Response.Write("
" & VbCrLf)
	Response.Write("
" & VbCrLf)
	Response.Write("
" & VbCrLf)
	Response.Write("
" & VbCrLf)
	Response.Write("		" & VbCrLf)
	Response.Write("	" & VbCrLf)
	'Now let's start the body of our table
	Response.Write("
" & VbCrLf)
	'Let's start a FOR loop to get and display all available rows
	For intRows = 0 To UBound(tmpArray, 2) 'The 2 is representative of the rows in the recordset
		'If we didn't know what columns we have, we would do another FOR loop inside this using:
		'UBound(tmpArray, 1)
		Response.Write("
" & VbCrLf)
		Response.Write("
" & VbCrLf)
		Response.Write("
" & VbCrLf)
		Response.Write("
" & VbCrLf)
		Response.Write("
" & VbCrLf)
		Response.Write("		" & VbCrLf)
	Next
	intRows = Null 'Clear intRows out
	Response.Write("	" & VbCrLf)
	Response.Write("<table 100%="" 2="" 0="" 1="" border="1" cellpadding="" cellspacing="" width=""><thead><tr><th>ISO CC</th>

<th>ISO CC3</th>

<th>Country Number Code</th>

<th>Country Name</th>

</tr>

</thead>

<tbody><tr><td>" & tmpArray(1, intRows) & "</td>

<td>" & tmpArray(3, intRows) & "</td>

<td>" & tmpArray(4, intRows) & "</td>

<td>" & tmpArray(2, intRows) & "</td>

</tr>

</tbody>

</table>

" & VbCrLf)
	'Id like to display the record paging again, in case the list is long
	Response.Write(strDisplayPaging)
	'I'd also like to display that paging info again
	Response.Write("<strong>" & intTotRecs & "</strong> total records in <strong>" & intTotPages & "</strong> pages.")
	Erase tmpArray 'Releases the array from memory
Else
	'We have no array, so let's display a message stating this!
	Response.Write("There are no records for that query.")
End If
%>

Open in new window


CLASS (The Work Horse):

 
< %
'*****************************************************************************
'*****************************************************************************
'
' This code can be used anywhere you like, all I ask is that you keep this
' notice here, so people know who actually made it! =D  Thanks!
'
' This code was developed by Kevin Pirnie, c/o o7th Web Design
' support@07th.com :: http://www.07th.com
'
'*****************************************************************************
'*****************************************************************************
Class DBv1
    'Private Declarations
    Private i, p, pp, strDataLength, objCmd, objRS, objConn, objError
    Private intCurrPage, ini, fim
    'Public Declarations
    Public intDBType, strDBUser, strDBPassword, strDBServer, strDBDatabase
	Public strConnString, intCommandType
    Public strQry, arrParamValues, arrParamDataTypes, intRetDataType, intRetSize
    Public boolUsePaging, intTotalPages, intTotalRecords
    Public intRecPerPage, intPageNumber, strPagingPage, strPagingLeft, strPagingRight
    'Initialize
    Private Sub Class_Initialize()
        intDBType = 0
        intCommandType = 0
        strDBServer = Null
        strDBUser = Null
        strDBPassword = Null
        strDBDatabase = Null
        strQry = Null
        arrParamValues = Null
        arrParamDataTypes = Null
        boolUsePaging = False
        intTotalPages = 0
        intTotalRecords = 0
        intRecPerPage = 0
        intPageNumber = 0
        strPagingPage = Null
        strPagingLeft = " < "
        strPagingRight = " > "
    End Sub
    'Terminate
    Private Sub Class_Terminate()
        intDBType = 0
        intCommandType = 0
        strDBServer = Null
        strDBUser = Null
        strDBPassword = Null
        strDBDatabase = Null
        strQry = Null
        arrParamValues = Null
        arrParamDataTypes = Null
        boolUsePaging = False
        intTotalPages = 0
        intTotalRecords = 0
        intRecPerPage = 0
        intPageNumber = 0
        strPagingPage = Null
        strPagingLeft = " &lgt; "
        strPagingRight = " &rgt; "
    End Sub
    'Execute the Query
    Public Function ExecuteQry()
        Set objConn = CreateObject("ADODB.Connection")
            objConn.Open strConnectionString
                Set objCmd = CreateObject("ADODB.Command")
                    objCmd.CommandText = strQry
                    objCmd.CommandType = intCommandType
                    If IsArray(arrParamValues) And IsArray(arrParamDataTypes) Then
                        If UBound(arrParamValues) = UBound(arrParamDataTypes) Then
                            For i = 0 To UBound(arrParamValues)
                                Select Case arrParamDataTypes(i)
                                    Case 2 'Small Integer
                                        strDataLength = 2
                                    Case 3 'Integer
                                        strDataLength = 4
                                    Case 4 'Single
                                        strDataLength = 4
                                    Case 5 'Float
                                        strDataLength = 8
                                    Case 6 'Currency
                                        strDataLength = 8
                                    Case 7 'Date
                                        strDataLength = 8
                                    Case 11 'Bit
                                        strDataLength = 1
                                    Case 14 'Decimal
                                        strDataLength = 9
                                    Case 72 'GUID
                                        strDataLength = 16
                                    Case 128 'Binary
                                        strDataLength = 50
                                    Case 129 'Char
                                        If Not ReqValue(arrParamValues(i)) Then
                                            strDataLength = 1
                                        Else
                                            strDataLength = Len(arrParamValues(i))
                                        End If
                                    Case 200 'VarChar
                                        If Not ReqValue(arrParamValues(i)) Then
                                            strDataLength = 1
                                        Else
                                            strDataLength = Len(arrParamValues(i))
                                        End If
                                    Case 203 'NText
                                        If Not ReqValue(arrParamValues(i)) Then
                                            strDataLength = 1
                                        Else
                                            strDataLength = Len(arrParamValues(i))
                                        End If
                                    Case 204 'VarBinary
                                        strDataLength = 50
                                    Case Else 'Hmm...guess
                                        If Not ReqValue(arrParamValues(i)) Then
                                            strDataLength = 1
                                        Else
                                            strDataLength = Len(arrParamValues(i))
                                        End If
                                End Select
                                If arrParamDataTypes(i) = 14 Then
                                    Set p = objCmd.CreateParameter(, CInt(arrParamDataTypes(i)), , CInt(strDataLength), InputCleaner(arrParamValues(i)))
                                    p.NumericScale = 2
                                    p.Precision = 10
                                    objCmd.Parameters.Append p
                                Else
                                    objCmd.Parameters.Append (objCmd.CreateParameter(, CInt(arrParamDataTypes(i)), , CInt(strDataLength), InputCleaner(arrParamValues(i))))
                                End If
                            Next
                            i = Null
                            Erase arrParamValues
                            Erase arrParamDataTypes
                        Else
                            ExecuteQry = "Your values and data type arrays need to be the same length."
                        End If
                    End If
					'Debug the parameters if necessary
					'For each Item In objCmd.Parameters
					'	Write("Name:" & Item.Name & "-Type:" & Item.Type & "-Value:" & Item.Value & "")
					'Next
                    Set objCmd.ActiveConnection = objConn
                        Select Case intCommandType
                            Case 1
                            If InStr(1, UCase(strQry), "SELECT") > 0 Then
                                Set objRS = CreateObject("Adodb.RecordSet")
                                    If boolUsePaging Then
                                        objRS.PageSize = intRecPerPage
                                        objRS.CacheSize = intRecPerPage
                                        objRS.CursorType = 3
                                    End If
                                    objRS.Open objCmd
                                    If Not (objRS.EOF) Then
                                        If boolUsePaging Then
                                            If Not (validNumber(intPageNumber)) Then
                                                objRS.AbsolutePage = 1
                                            Else
                                                objRS.AbsolutePage = intPageNumber
                                            End If
                                            ExecuteQry = objRS.GetRows(intRecPerPage)
                                            intTotalPages = objRS.PageCount
                                            intTotalRecords = objRS.RecordCount
                                        Else
                                            ExecuteQry = objRS.GetRows()
                                        End If
                                    Else
                                        ExecuteQry = "There are no records."
                                        Exit Function
                                    End If
                                Set objRS = Nothing
                                Exit Function
                            ElseIf InStr(1, UCase(strQry), "INSERT") > 0 Then
                                If InStr(1, UCase(strQry), "@@IDENTITY") > 0 Or InStr(1, UCase(strQry), "NEWID()") > 0 Then
                                    Set objRS = objCmd.Execute()
                                        If Not (objRS.EOF) Then
                                            ExecuteQry = objRS(0)
                                        End If
                                    Set objRS = Nothing
                                Else
                                    objCmd.Execute
                                    ExecuteQry = "Your command has been executed."
                                End If
                            ElseIf (InStr(1, UCase(strQry), "DELETE") > 0 Or InStr(1, UCase(strQry), "UPDATE") > 0 Or Left(UCase(strQry), 2) = "SP") Then
                                If Left(UCase(strQry), 2) = "SP" And intRetDataType > "" Then
                                    objCmd.Parameters.Append (objCmd.CreateParameter("@ret", intRetDataType, 2, , intRetSize))
                                    objCmd.Execute
                                    ExecuteQry = objCmd.Parameters("@ret")
                                Else
                                    ExecuteQry = objCmd.Execute
                                End If
                            End If
                        Case 4
                            If intRetDataType > "" Then
                                objCmd.Parameters.Append (objCmd.CreateParameter("@ret", intRetDataType, 2, , intRetSize))
                                objCmd.Execute
                                ExecuteQry = objCmd.Parameters("@ret")
                            Else
                                If boolUsePaging Then
                                    Set objRS = CreateObject("Adodb.RecordSet")
                                        objRS.PageSize = intRecPerPage
                                        objRS.CacheSize = intRecPerPage
                                        objRS.CursorType = 3
                                        objRS.CursorLocation = 3
                                        objRS.Open objCmd
                                        If Not (objRS.EOF) Then
                                            If boolUsePaging Then
                                                If Not (validNumber(intPageNumber)) Then
                                                    objRS.AbsolutePage = 1
                                                Else
                                                    objRS.AbsolutePage = intPageNumber
                                                End If
                                                ExecuteQry = objRS.GetRows(intRecPerPage)
                                                intTotalPages = objRS.PageCount
                                                intTotalRecords = objRS.RecordCount
                                            Else
                                                ExecuteQry = objRS.GetRows()
                                            End If
                                        Else
                                            objCmd.Execute
                                        End If
                                    Set objRS = Nothing
                                Else
                                    objCmd.Execute
                                End If
                            End If
                        End Select
                    Set objCmd.ActiveConnection = Nothing
                Set objCmd = Nothing
            objConn.Close
        Set objConn = Nothing
    End Function
    'Paging Links
    Public Function RecordPaging()
        tmpString = ""
        tmpString = tmpString & "
<div class="" paging_links="">" & vbCrLf
        If Not (validNumber(intPageNumber)) Then
            CurrentPage = 1 'We're On the first page
            NumPerPageOf = 1
        Else
            CurrentPage = CInt(intPageNumber)
            NumPerPageOf = ((CurrentPage * NumPerPage) - NumPerPage) + 1
        End If
        If CurrentPage > 1 Then
            If CurrentPage > 5 And intTotalPages > 10 Then
                tmpString = tmpString & "   <span><a href="" strpagingpage="" &#038;="" &p="1&quot;&quot;">1</a></span> "
                tmpString = tmpString & "<span class="" prevchunk=""> « </span>"
            End If
            If intTotalPages > 10 Then
                If CurrentPage > 5 Then
                    If intTotalPages > (CurrentPage + 5) Then
                        ini = (CurrentPage - 4)
                        fim = (CurrentPage + 5)
                    Else
                        ini = (intTotalPages - 9)
                        fim = intTotalPages
                    End If
                Else
                    ini = 1
                    fim = 10
                End If
            Else
                ini = 1
                fim = intTotalPages
            End If
            For a = ini To fim
                If a = CInt(intPageNumber) Then
                    tmpString = tmpString & " <span class="" curpage="">" & a & "</span> "
                Else
                    tmpString = tmpString & "   <span><a href="" strpagingpage="" &#038;="" &p=" & a & ">" & a & "</a></span> "
                End If
            Next: a = Null
        Else
            If intTotalPages = 1 Then
                tmpString = tmpString & ""
            Else
                tmpString = tmpString & "<span class="" curpage="">1</span> "
            End If
            If intTotalPages > 10 Then     'id=161&MWC=Layouts
                fim = 10
            Else
                fim = intTotalPages
            End If
            For a = 2 To fim
                If a = CInt(intPageNumber) Then
                    tmpString = tmpString & "<span class="" curpage="">" & a & "</span> "
                Else
                    tmpString = tmpString & "   <span><a href="" strpagingpage="" &#038;="" &p=" & a & ">" & a & "</a></span> "
                End If
            Next: a = Null
        End If
        If CurrentPage < intTotalPages - 5 And intTotalPages > 10 Then
            tmpString = tmpString & "<span class="" lastchunk=""> » </span>"
			tmpString = tmpString & "   <span><a href="" strpagingpage="" &#038;="" &p=" & intTotalPages & ">" & intTotalPages & "</a></span> "
        End If
        tmpString = tmpString & "</div>

" & vbCrLf
        RecordPaging = tmpString
        tmpString = ""
    End Function
    'Get our connection string
    Private Function strConnectionString()
        If ReqValue(strConnString) Then
            strConnectionString = strConnString
        Else
            Select Case intDBType
                Case 1 'SQL 2000
                    strConnectionString = "Provider=SQLOLEDB.1;Password=" & strDBPassword & ";User ID=" & strDBUser & ";Initial Catalog=" & strDBDatabase & ";Data Source=" & strDBServer & ""
                Case 2 'SQL 2005
                    strConnectionString = "Provider=SQLNCLI;Server=" & strDBServer & ";Database=" & strDBDatabase & ";Uid=" & strDBUser & ";Pwd=" & strDBPassword & ";DataTypeCompatibility=80;"
                Case 3 'SQL 2005 Express
                    strConnectionString = "Provider=SQLOLEDB;Data Source=" & strDBServer & ";Persist Security Info=True;Password=" & strDBPassword & ";User ID=" & strDBUser & ";Initial Catalog=" & strDBDatabase & ";DataTypeCompatibility=80"
                Case 4 'MS Access
                    strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBDatabase & ";User Id=" & strDBUser & ";Password=" & strDBPassword & ";"
                Case 6 'MS Access 2007
                    strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDBDatabase & ";Persist Security Info=False;"
                Case 8 'Borland Interbase - requires the SIBProvider to be installed on the server
                    strConnectionString = "provider=sibprovider;location=" & strDBServer & ":;data source=" & strDBDatabase & ";user id=" & strDBUser & ";Password=" & strDBPassword & ";"
                Case 7 'MySQL
                    strConnectionString = "Driver={MySQL ODBC 3.51 Driver};Server=" & strDBServer & ";Database=" & strDBDatabase & "; User=" & strDBUser & ";Password=" & strDBPassword & ";Option=3;"
            End Select
        End If
    End Function
    'Input cleaning ... just in case
    Private Function InputCleaner(ByVal strStringToClean)
        If Not (ReqValue(strStringToClean)) Then
            If InStr(1, strStringToClean, "'") > 0 Then strStringToClean = REPLACE(cast(cast(strStringToClean as nvarchar(max)) as nvarchar(max)),cast(cast( "'" as nvarchar(max)) as nvarchar(max)),cast(cast( "'" as nvarchar(max as nvarchar(max)))))
            If InStr(1, strStringToClean, Chr(34)) > 0 Then strStringToClean = REPLACE(cast(cast(strStringToClean as nvarchar(max)) as nvarchar(max)),cast(cast( Chr(34) as nvarchar(max)) as nvarchar(max)),cast(cast( """ as nvarchar(max as nvarchar(max)))))
            If InStr(1, strStringToClean, "@") > 0 Then strStringToClean = REPLACE(cast(cast(strStringToClean as nvarchar(max)) as nvarchar(max)),cast(cast( "@" as nvarchar(max)) as nvarchar(max)),cast(cast( "@" as nvarchar(max as nvarchar(max)))))
            If InStr(1, strStringToClean, "|") > 0 Then strStringToClean = REPLACE(cast(cast(strStringToClean as nvarchar(max)) as nvarchar(max)),cast(cast( "|" as nvarchar(max)) as nvarchar(max)),cast(cast( "|" as nvarchar(max as nvarchar(max)))))
            If InStr(1, strStringToClean, "*") > 0 Then strStringToClean = REPLACE(cast(cast(strStringToClean as nvarchar(max)) as nvarchar(max)),cast(cast( "*" as nvarchar(max)) as nvarchar(max)),cast(cast( "*" as nvarchar(max as nvarchar(max)))))
            If InStr(1, strStringToClean, "--") > 0 Then strStringToClean = REPLACE(cast(cast(strStringToClean as nvarchar(max)) as nvarchar(max)),cast(cast( "--" as nvarchar(max)) as nvarchar(max)),cast(cast( "--" as nvarchar(max as nvarchar(max)))))
            If InStr(1, strStringToClean, "(") > 0 Then strStringToClean = REPLACE(cast(cast(strStringToClean as nvarchar(max)) as nvarchar(max)),cast(cast( "(" as nvarchar(max)) as nvarchar(max)),cast(cast( "(" as nvarchar(max as nvarchar(max)))))
            If InStr(1, strStringToClean, ")") > 0 Then strStringToClean = REPLACE(cast(cast(strStringToClean as nvarchar(max)) as nvarchar(max)),cast(cast( ")" as nvarchar(max)) as nvarchar(max)),cast(cast( ")" as nvarchar(max as nvarchar(max)))))
        End If
        InputCleaner = strStringToClean
    End Function
    'Required Value?
    Private Function ReqValue(ByVal strValue)
        ReqValue = True 'by default
        If strValue = "" Then ReqValue = False
        If IsNull(strValue) Then ReqValue = False
        If Len(strValue) < = 0 Then ReqValue = False
        If IsEmpty(strValue) Then ReqValue = False
    End Function
    'Valid Number?
    Private Function validNumber(ByVal strValue)
        If ReqValue(strValue) Then
            validNumber = False 'Default
            Set objRegExp = New RegExp
                objRegExp.Pattern = "^(?:-?(?:[0-9]+.?|[0-9]*(?:.[0-9]+){1}))$"
                validNumber = objRegExp.Test(strValue)
            Set objRegExp = Nothing
        End If
    End Function
End Class
%>

Open in new window

0
Comment
Author:kevp75
  • 2
4 Comments
 
LVL 25

Author Comment

by:kevp75
As a slight followup to this article I thought I might post, that I have experienced a 12% increase in performance by using this class in my Classic ASP applications over the conventional method of coding this out everytime it is needed.

Also, the performance increased to about 18% when the code was compiled into a COM Object.

Keep tuned, I have also ported this to VB.NET, and will probably be posting another article soon with the results of that code.  (I have some more testing to do...  trying to incorporate Linq into it =))
0
 

Expert Comment

by:usnow2
Nice article. However, why would you cast to nvarchar, which doubles the size of the string right away?
Also, wouldn't looking for SQL Injection code be sufficient, such as "</", "script", "--" and their ascii counterparts in case the code has been hidden?

0
 
LVL 25

Author Comment

by:kevp75
Unfortunately, simply removing the invalid characters may not be effective at cleaning, as well as there may actually be a need to store html, script, etc...

Casting as nvarchar, yes it does double the string, but it also makes it so the code can be used internationally, and keep special characters.
0
 
LVL 10

Expert Comment

by:Banthor
If you are going against MSSQL Server, you should always use stored procedures rather than dynamic SQL, Regardless of Injection issues and data type performance. You are missing out on internal caching and encapsulation.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Next Article:

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month