?
Solved

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

Posted on 2011-02-18
5
Medium Priority
?
325 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 23

Accepted Solution

by:
wdosanjos earned 2000 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 75

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 75

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

752 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