Solved

How to send an array of strings through a function and have the modified verions returned

Posted on 2011-02-18
5
315 Views
Last Modified: 2012-05-11

A little background:


Working on some SQL Injection Prevention Methods. I want to sanitize all user input by using a replace function to double escape the single quotes before they are passed off to SQL Queries and Stored Procedures.

Here is the function:

    Public Function fncReplaceQuote(ByVal strToReplace As String) As String

        strToReplace = strToReplace.Replace("'", "''")

        Return strToReplace
    End Function

Open in new window


Currently I am doing this for every string on the page:

        strFullName = objStringValidation.fncReplaceQuote(strFullName)
        strGreetingName = objStringValidation.fncReplaceQuote(strGreetingName)
        strEmailAddress = objStringValidation.fncReplaceQuote(strEmailAddress)
        strUserName = objStringValidation.fncReplaceQuote(strUserName)
        strPassword = objStringValidation.fncReplaceQuote(strPassword)
        strSecurityAnswer = objStringValidation.fncReplaceQuote(strSecurityAnswer)

Open in new window


My question is this:


Is it possible to throw these strings into an array and loop through, but return the modified string for use later in my app? Maybe something like this:

        Dim i As Integer
        Dim arrSanitizeStrings() As String = {strFullName, strGreetingName, strEmailAddress, strUserName, strPassword, strSecurityAnswer}

        For i = 0 To arrSanitizeStrings.Count
            objStringValidation.fncReplaceQuote(i)
        Next i

Open in new window


The above does not work so far as it does not return each string in its modified state.
Should I be using Public Properties defined at the beginning of each page?

This is a small example, but there are some pages in my app which have many strings that need to be sanitized before they hit SQL queries -- that is why I ask if it is possible to build an array of the strings and send them all through the Replace Function.

Jason

0
Comment
Question by:jsvb1977
  • 2
  • 2
5 Comments
 
LVL 23

Accepted Solution

by:
wdosanjos earned 500 total points
ID: 34929430
You could change the function as follows to pass the value by reference:

Public Sub ReplaceQuote(ByRef str As String)
	str = str.Replace("'","''")
End Sub

Open in new window


Then you can change the rest of the code to:

        objStringValidation.ReplaceQuote(strFullName)
        objStringValidation.ReplaceQuote(strGreetingName)
        objStringValidation.ReplaceQuote(strEmailAddress)
        objStringValidation.ReplaceQuote(strUserName)
        objStringValidation.ReplaceQuote(strPassword)
        objStringValidation.ReplaceQuote(strSecurityAnswer)

Open in new window


The problem with the array solution is that there is no way to reference to the original variable.  You can change the array values, but that doesn't impact the original variable values.

I hope this helps.
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 34930979
My question is why are you using string concatenation to build SQL queries  = )
0
 

Author Comment

by:jsvb1977
ID: 34931052
kaufmed,

Please explain the error of my ways. It is important for me to know the correct way.

For simple / quick select queries I use the following in code behind:

        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        Dim sqlConnectionString As String = objDatabase.sqlConnect(strsqlConnectionString)

        con.ConnectionString = sqlConnectionString
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "SELECT EmailAddress FROM [User] WHERE EmailAddress = @EmailAddress"
        Dim parEmailAddress = New SqlParameter("@EmailAddress", SqlDbType.VarChar)
        parEmailAddress.Value = strEmailAddress
        cmd.Parameters.Add(parEmailAddress)
        cmd.ExecuteNonQuery()
        Dim sqlreader As SqlDataReader = cmd.ExecuteReader()

        If sqlreader.HasRows = True Then
            con.Close()
            Return True
        Else
            con.Close()
            Return False
        End If

Open in new window


For situations where I need to pass variables through to a sql stored procedure I do something like this:

            Dim strInsertCommand As String = "Exec spCreateAccount '" & strEmailAddress & "', '" & strEncryptedPassword & "','" & strPasswordReminderQuestion & "','" & strPasswordReminderAnswer & "', '" & strActivationKey & "'"
            objDatabase.executeSQL(strInsertCommand, strsqlConnectionString)

Open in new window


Which, I gather from your post, is not good [a point i tend to agree with - which is why I am 'sanitizing' the variiables before i send to SQL stored procedure as parameters]

The last line of the code above sends the command and a connection string to a function in a centralized class that i use throughout the code [so as to have only one place where database connections are established and datasets are populated]. The function in the database class looks like this:

    Public Function executeSQL(ByVal strExecuteCommand As String, ByVal strConnectionStringName As String)
        sqlConnectionString = sqlConnect(strConnectionStringName)

        con.ConnectionString = sqlConnectionString
        con.Open()
        cmd.Connection = con
        cmd.CommandText = strExecuteCommand
        cmd.ExecuteNonQuery()
        con.Close()

        Return Nothing
    End Function

Open in new window

0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 34932828
I retract my previous statement. You are not using string concatenation; you are using parameters, as you should be  : )

You shouldn't need to worry about escaping quotation marks when using parameter objects to send user data to your database--the SqlParameter should do the escaping for you.

Here is an article discussing Sql injection and ways to avoid it:  http://msdn.microsoft.com/en-us/library/ff648339.aspx
0
 

Author Closing Comment

by:jsvb1977
ID: 34945599
This is the best solution, since doing what i hoped to be able to do is not possible.

Thanks for info!

Jason
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now