[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

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

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
jsvb1977
Asked:
jsvb1977
  • 2
  • 2
1 Solution
 
wdosanjosCommented:
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
 
käµfm³d 👽Commented:
My question is why are you using string concatenation to build SQL queries  = )
0
 
jsvb1977Author Commented:
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
 
käµfm³d 👽Commented:
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
 
jsvb1977Author Commented:
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now