Solved

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

Posted on 2011-02-18
5
322 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 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 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

730 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