Optimized way to transfer from String to StringBuilder

Level Extreme platform

Subscription

Corporate profile

Products & Services

Support

Legal

Français

Optimized way to transfer from String to StringBuilder

Message

From

05/02/2014 14:11:52

Mike Yearwood
Toronto, Ontario, Canada

05/02/2014 13:36:50

Michel Fournier
Level Extreme Inc.
Petit-Rocher, New Brunswick, Canada

General information

Forum:

ASP.NET

Category:

Coding, syntax and commands

Title:

Re: Optimized way to transfer from String to StringBuilder

Environment versions

Environment:

VB 9.0

OS:

Windows 7

Network:

Windows 2003 Server

Database:

MS SQL Server

Application:

Web

Miscellaneous

Thread ID:

01593079

Message ID:

01593318

Views:

Michel

Are you allowing the user to type SQL which you are then executing? Is that why you are trying to strip out reserved words?

If you parameterize the user's input, you do not need to parse it to remove keywords or any other kind of SQL Injection attack prevention. Such methods are ineffective in the end.

All you have to do is construct your SQL. Accept user inputs and pass those inputs as parameters to SQL itself. Never concatenate user inputs into SQL command strings and you will not have to run such code.

>>Don't understand. What doesn't my StringBuilder() do that requires more work ?
>
>This is the actual method:
>
>

>    ' Parse a string for SQL Server
>    ' expC1 String
>    Public Function ParseForSQLServer(ByVal tcString As String) As String
>        Dim laReservedWord(28) As Object
>        Dim laReservedWordForFieldOnly(1) As Object
>        Dim lnCounter As Integer = 0
>        Dim lcString As String = ""
>        Dim lcWord As String = ""
>
>        ' Define the reserved words
>        laReservedWord(1) = "Order"
>        laReservedWord(2) = "All"
>        laReservedWord(3) = "Table"
>        laReservedWord(4) = "From"
>        laReservedWord(5) = "To"
>        laReservedWord(6) = "Delete"
>        laReservedWord(7) = "Insert"
>        laReservedWord(8) = "Default"
>        laReservedWord(9) = "End"
>        laReservedWord(10) = "Index"
>        laReservedWord(11) = "File"
>        laReservedWord(12) = "Current"
>        laReservedWord(13) = "Expand"
>        laReservedWord(14) = "Image"
>        laReservedWord(15) = "Read"
>        laReservedWord(16) = "View"
>        laReservedWord(17) = "Desc"
>        laReservedWord(18) = "Update"
>        laReservedWord(19) = "User"
>        laReservedWord(20) = "In"
>        laReservedWord(21) = "Public"
>        laReservedWord(22) = "Unique"
>        laReservedWord(23) = "Read"
>        laReservedWord(24) = "Site"
>        laReservedWord(25) = "Full"
>        laReservedWord(26) = "Key"
>        laReservedWord(27) = "Group"
>        laReservedWord(28) = "Like"
>
>        ' Define the reserved words for fields only
>        laReservedWordForFieldOnly(1) = "As"
>
>        lcString = tcString
>
>        ' Make sure we have a value
>        If lcString.Length = 0 Then
>            Return ""
>        End If
>
>        ' Add a space at the end to make sure to include the last word
>        lcString = lcString + " "
>
>        ' Scan all reserved words
>        For lnCounter = 1 To 28
>            lcWord = laReservedWord(lnCounter)
>
>            ' Field
>            lcString = oApp.StrTran(lcString, "," + lcWord + ".", ",[" + lcWord + "].")
>            lcString = oApp.StrTran(lcString, "." + lcWord + ",", ".[" + lcWord + "],")
>            lcString = oApp.StrTran(lcString, "." + lcWord + " ", ".[" + lcWord + "] ")
>            lcString = oApp.StrTran(lcString, " " + lcWord + ",", " [" + lcWord + "],")
>            lcString = oApp.StrTran(lcString, "," + lcWord + ")", ",[" + lcWord + "])")
>
>            ' INNER JOIN or AS clause in the fields
>            lcString = oApp.StrTran(lcString, " " + lcWord + " ", " [" + lcWord + "] ")
>            lcString = oApp.StrTran(lcString, " " + lcWord + ".", " [" + lcWord + "].")
>            lcString = oApp.StrTran(lcString, "." + lcWord + "=", ".[" + lcWord + "]=")
>            lcString = oApp.StrTran(lcString, "=" + lcWord + ".", "=[" + lcWord + "].")
>            lcString = oApp.StrTran(lcString, "." + lcWord + " ", ".[" + lcWord + "] ")
>
>            ' Where
>            lcString = oApp.StrTran(lcString, "(" + lcWord + ".", "([" + lcWord + "].")
>            lcString = oApp.StrTran(lcString, "." + lcWord + ")", ".[" + lcWord + "])")
>            lcString = oApp.StrTran(lcString, "." + lcWord + ">", ".[" + lcWord + "]>")
>            lcString = oApp.StrTran(lcString, "." + lcWord + "<", ".[" + lcWord + "]<")
>            lcString = oApp.StrTran(lcString, "." + lcWord + "=", ".[" + lcWord + "]=")
>
>            ' Update
>            lcString = oApp.StrTran(lcString, " " + lcWord + "=", " [" + lcWord + "]=")
>
>            ' Order
>            lcString = oApp.StrTran(lcString, "," + lcWord + " ", ",[" + lcWord + "] ")
>            lcString = oApp.StrTran(lcString, "," + lcWord + ",", ",[" + lcWord + "],")
>
>            ' Check for the last occurrence
>            If Mid(lcString, lcString.Length - lcWord.Length) = "." + lcWord Then
>                lcString = Mid(lcString, 1, lcString.Length - lcWord.Length) + "[" + lcWord + "] "
>            End If
>
>            ' Check for the first occurrence
>            If Mid(lcString, 1, lcWord.Length + 1) = lcWord + "." Then
>                lcString = "[" + Mid(lcString, 1, lcWord.Length) + "]" + Mid(lcString, lcWord.Length + 1)
>            End If
>
>            ' Field in update
>            lcString = oApp.StrTran(lcString, "," + lcWord + "=", ",[" + lcWord + "]=")
>
>        Next
>
>        ' Scan all reserved words for fields only
>        For lnCounter = 1 To 1
>            lcWord = laReservedWordForFieldOnly(lnCounter)
>
>            ' Field
>            lcString = oApp.StrTran(lcString, "," + lcWord + ".", ",[" + lcWord + "].")
>            lcString = oApp.StrTran(lcString, "." + lcWord + ",", ".[" + lcWord + "],")
>            lcString = oApp.StrTran(lcString, "." + lcWord + " ", ".[" + lcWord + "] ")
>            lcString = oApp.StrTran(lcString, " " + lcWord + ",", " [" + lcWord + "],")
>            lcString = oApp.StrTran(lcString, "," + lcWord + ")", ",[" + lcWord + "])")
>
>            ' INNER JOIN or AS clause in the fields
>            lcString = oApp.StrTran(lcString, " " + lcWord + ".", " [" + lcWord + "].")
>            lcString = oApp.StrTran(lcString, "." + lcWord + "=", ".[" + lcWord + "]=")
>            lcString = oApp.StrTran(lcString, "=" + lcWord + ".", "=[" + lcWord + "].")
>            lcString = oApp.StrTran(lcString, "." + lcWord + " ", ".[" + lcWord + "] ")
>
>            ' Where
>            lcString = oApp.StrTran(lcString, "(" + lcWord + ".", "([" + lcWord + "].")
>            lcString = oApp.StrTran(lcString, "." + lcWord + ")", ".[" + lcWord + "])")
>            lcString = oApp.StrTran(lcString, "." + lcWord + ">", ".[" + lcWord + "]>")
>            lcString = oApp.StrTran(lcString, "." + lcWord + "<", ".[" + lcWord + "]<")
>            lcString = oApp.StrTran(lcString, "." + lcWord + "=", ".[" + lcWord + "]=")
>
>            ' Update
>            lcString = oApp.StrTran(lcString, " " + lcWord + "=", " [" + lcWord + "]=")
>
>            ' Order
>            lcString = oApp.StrTran(lcString, "," + lcWord + " ", ",[" + lcWord + "] ")
>            lcString = oApp.StrTran(lcString, "," + lcWord + ",", ",[" + lcWord + "],")
>
>            ' Check for the last occurrence
>            If Mid(lcString, lcString.Length - lcWord.Length) = "." + lcWord Then
>                lcString = Mid(lcString, 1, lcString.Length - lcWord.Length) + "[" + lcWord + "] "
>            End If
>
>            ' Check for the first occurrence
>            If Mid(lcString, 1, lcWord.Length + 1) = lcWord + "." Then
>                lcString = "[" + Mid(lcString, 1, lcWord.Length) + "]" + Mid(lcString, lcWord.Length + 1)
>            End If
>
>            ' Field in update
>            lcString = oApp.StrTran(lcString, "," + lcWord + "=", ",[" + lcWord + "]=")
>
>        Next
>
>        ' Trim everything
>        lcString = Trim(lcString)
>
>        Return lcString
>    End Function
>

>
>This is an attempt I made with StringBuilder but it was slower:
>
>

>    ' Parse a string for SQL Server
>    ' expC1 String
>    Public Function ParseForSQLServer(ByVal tcString As String) As String
>        Dim laReservedWord(28) As Object
>        Dim laReservedWordForFieldOnly(1) As Object
>        Dim lnCounter As Integer = 0
>        Dim lcWord As String = ""
>        Dim loStringBuilder As StringBuilder = New StringBuilder
>
>        ' Define the reserved words
>        laReservedWord(1) = "Order"
>        laReservedWord(2) = "All"
>        laReservedWord(3) = "Table"
>        laReservedWord(4) = "From"
>        laReservedWord(5) = "To"
>        laReservedWord(6) = "Delete"
>        laReservedWord(7) = "Insert"
>        laReservedWord(8) = "Default"
>        laReservedWord(9) = "End"
>        laReservedWord(10) = "Index"
>        laReservedWord(11) = "File"
>        laReservedWord(12) = "Current"
>        laReservedWord(13) = "Expand"
>        laReservedWord(14) = "Image"
>        laReservedWord(15) = "Read"
>        laReservedWord(16) = "View"
>        laReservedWord(17) = "Desc"
>        laReservedWord(18) = "Update"
>        laReservedWord(19) = "User"
>        laReservedWord(20) = "In"
>        laReservedWord(21) = "Public"
>        laReservedWord(22) = "Unique"
>        laReservedWord(23) = "Read"
>        laReservedWord(24) = "Site"
>        laReservedWord(25) = "Full"
>        laReservedWord(26) = "Key"
>        laReservedWord(27) = "Group"
>        laReservedWord(28) = "Like"
>
>        ' Define the reserved words for fields only
>        laReservedWordForFieldOnly(1) = "As"
>
>        loStringBuilder.Append(tcString)
>
>        ' Make sure we have a value
>        If loStringBuilder.Length = 0 Then
>            Return ""
>        End If
>
>        ' Add a space at the end to make sure to include the last word
>        loStringBuilder.Append(" ")
>
>        ' For each reserved word
>        For lnCounter = 1 To 28
>            lcWord = laReservedWord(lnCounter)
>
>            ' Field
>            loStringBuilder.Replace("," + lcWord + ".", ",[" + lcWord + "].")
>            loStringBuilder.Replace("," + lcWord + ".", ",[" + lcWord + "].")
>            loStringBuilder.Replace("." + lcWord + ",", ".[" + lcWord + "],")
>            loStringBuilder.Replace("." + lcWord + " ", ".[" + lcWord + "] ")
>            loStringBuilder.Replace(" " + lcWord + ",", " [" + lcWord + "],")
>            loStringBuilder.Replace("," + lcWord + ")", ",[" + lcWord + "])")
>
>            ' INNER JOIN or AS clause in the fields
>            loStringBuilder.Replace(" " + lcWord + " ", " [" + lcWord + "] ")
>            loStringBuilder.Replace(" " + lcWord + ".", " [" + lcWord + "].")
>            loStringBuilder.Replace("." + lcWord + "=", ".[" + lcWord + "]=")
>            loStringBuilder.Replace("=" + lcWord + ".", "=[" + lcWord + "].")
>            loStringBuilder.Replace("." + lcWord + " ", ".[" + lcWord + "] ")
>
>            ' Where
>            loStringBuilder.Replace("(" + lcWord + ".", "([" + lcWord + "].")
>            loStringBuilder.Replace("." + lcWord + ")", ".[" + lcWord + "])")
>            loStringBuilder.Replace("." + lcWord + ">", ".[" + lcWord + "]>")
>            loStringBuilder.Replace("." + lcWord + "<", ".[" + lcWord + "]<")
>            loStringBuilder.Replace("." + lcWord + "=", ".[" + lcWord + "]=")
>
>            ' Update
>            loStringBuilder.Replace(" " + lcWord + "=", " [" + lcWord + "]=")
>
>            ' Order
>            loStringBuilder.Replace("," + lcWord + " ", ",[" + lcWord + "] ")
>            loStringBuilder.Replace("," + lcWord + ",", ",[" + lcWord + "],")
>
>            ' Check for the last occurrence
>            If Mid(loStringBuilder.ToString, loStringBuilder.Length - lcWord.Length) = "." + lcWord Then
>                loStringBuilder.Remove(loStringBuilder.Length - 1 - lcWord.Length, lcWord.Length)
>                loStringBuilder.Append("[" + lcWord + "] ")
>            End If
>
>            ' Check for the first occurrence
>            If Mid(loStringBuilder.ToString, 1, lcWord.Length + 1) = lcWord + "." Then
>                loStringBuilder = oApp.StuffFromStringBuilder(loStringBuilder, 1, 1, "[")
>                loStringBuilder = oApp.StuffFromStringBuilder(loStringBuilder, lcWord.Length + 2, 1, "]")
>            End If
>
>            ' Field in update
>            loStringBuilder.Replace("," + lcWord + "=", ",[" + lcWord + "]=")
>
>        Next
>
>        ' For each reserved words for fields only
>        For lnCounter = 1 To 1
>            lcWord = laReservedWordForFieldOnly(lnCounter)
>
>            ' Field
>            loStringBuilder.Replace("," + lcWord + ".", ",[" + lcWord + "].")
>            loStringBuilder.Replace("." + lcWord + ",", ".[" + lcWord + "],")
>            loStringBuilder.Replace("." + lcWord + " ", ".[" + lcWord + "] ")
>            loStringBuilder.Replace(" " + lcWord + ",", " [" + lcWord + "],")
>            loStringBuilder.Replace("," + lcWord + ")", ",[" + lcWord + "])")
>
>            ' INNER JOIN or AS clause in the fields
>            loStringBuilder.Replace(" " + lcWord + ".", " [" + lcWord + "].")
>            loStringBuilder.Replace("." + lcWord + "=", ".[" + lcWord + "]=")
>            loStringBuilder.Replace("=" + lcWord + ".", "=[" + lcWord + "].")
>            loStringBuilder.Replace("." + lcWord + " ", ".[" + lcWord + "] ")
>
>            ' Where
>            loStringBuilder.Replace("(" + lcWord + ".", "([" + lcWord + "].")
>            loStringBuilder.Replace("." + lcWord + ")", ".[" + lcWord + "])")
>            loStringBuilder.Replace("." + lcWord + ">", ".[" + lcWord + "]>")
>            loStringBuilder.Replace("." + lcWord + "<", ".[" + lcWord + "]<")
>            loStringBuilder.Replace("." + lcWord + "=", ".[" + lcWord + "]=")
>
>            ' Update
>            loStringBuilder.Replace(" " + lcWord + "=", " [" + lcWord + "]=")
>
>            ' Order
>            loStringBuilder.Replace("," + lcWord + " ", ",[" + lcWord + "] ")
>            loStringBuilder.Replace("," + lcWord + ",", ",[" + lcWord + "],")
>
>            ' Check for the last occurrence
>            If Mid(loStringBuilder.ToString, loStringBuilder.Length - lcWord.Length) = "." + lcWord Then
>                loStringBuilder.Remove(loStringBuilder.Length - 1 - lcWord.Length, lcWord.Length)
>                loStringBuilder.Append("[" + lcWord + "] ")
>            End If
>
>            ' Check for the first occurrence
>            If Mid(loStringBuilder.ToString, 1, lcWord.Length + 1) = lcWord + "." Then
>                loStringBuilder = oApp.StuffFromStringBuilder(loStringBuilder, 1, 1, "[")
>                loStringBuilder = oApp.StuffFromStringBuilder(loStringBuilder, lcWord.Length + 2, 1, "]")
>            End If
>
>            ' Field in update
>            loStringBuilder.Replace("," + lcWord + "=", ",[" + lcWord + "]=")
>
>        Next
>
>        Return loStringBuilder.ToString
>    End Function
>

>
>The part I was wondering about to see if it can be optimized was:
>
>

>            ' Check for the last occurrence
>            If Mid(loStringBuilder.ToString, loStringBuilder.Length - lcWord.Length) = "." + lcWord Then
>                loStringBuilder.Remove(loStringBuilder.Length - 1 - lcWord.Length, lcWord.Length)
>                loStringBuilder.Append("[" + lcWord + "] ")
>            End If
>
>            ' Check for the first occurrence
>            If Mid(loStringBuilder.ToString, 1, lcWord.Length + 1) = lcWord + "." Then
>                loStringBuilder = oApp.StuffFromStringBuilder(loStringBuilder, 1, 1, "[")
>                loStringBuilder = oApp.StuffFromStringBuilder(loStringBuilder, lcWord.Length + 2, 1, "]")
>            End If
>

Map

View

Click here to load this message in the networking platform