String builder SELECT query when a variable has an apostrophe

Question

A colleague of mine has created a program that reads a text file and assigns various values from it to variables that are used in SQL statements. One of these variables, gsAccounts is a string variable.

Using a string builder, a SELECT statement is being built up with sql.append. At the end of the string, there is the following line:

sql.Append(" WHERE L.Account_Code IN(" & gsAccounts & ")"

The problem that I'm having is that sometimes, not always, gsAccounts (a list of account codes) may contain an account code with an apostrophe, so the query becomes

"WHERE L.Account_Code IN('test'123')"

when the account code is test'123

I have tried using double quotes to get around it in a "WHERE L.Account_Code IN("""" & gsAccounts & """")" way (using 4 and 6 " next to each other, but neither worked)

How can I get around this? The account_Code is the Primary Key in the table, so I can't just remove it as there are years worth of transactions and data connected to it.

To avoid SQL injection, you should use parameters instead of joining a string by yourself. — muffi, Jun 07 '18 at 09:31
@muffi I usually do, this wasn't a program I wrote, a colleague did - how do you use parameters in string builders? — Harambe, Jun 07 '18 at 09:33
If you know how to use parameters, it should be no problem to replace your variable with a parameter?!? — muffi, Jun 07 '18 at 09:35
You need one parameter for every item: https://stackoverflow.com/a/4502866/284240 — Tim Schmelter, Jun 07 '18 at 09:56
Here is an example of exactly what you're doing, done the right way: http://www.vbforums.com/showthread.php?503298-Using-Parameters-with-an-SQL-IN-Clause — jmcilhinney, Jun 07 '18 at 10:48

jmcilhinney · Accepted Answer · 2018-06-07T10:58:35.340

I posted the following example here 10 years ago, almost to the day. (Oops! thought it was Jun 5 but it was Jan 5. 10.5 years then.)

Dim connection As New SqlConnection("connection string here")
Dim command As New SqlCommand
Dim query As New StringBuilder("SELECT * FROM MyTable")

Select Case Me.ListBox1.SelectedItems.Count
    Case 1
        'Only one item is selected so we only need one parameter.
        query.Append(" WHERE MyColumn = @MyColumn")
        command.Parameters.AddWithValue("@MyColumn", Me.ListBox1.SelectedItem)
    Case Is > 1
        'Multiple items are selected so include a parameter for each.
        query.Append(" WHERE MyColumn IN (")

        Dim paramName As String

        For index As Integer = 0 To Me.ListBox1.SelectedItems.Count - 1 Step 1
            'Name all parameters for the column with a numeric suffix.
            paramName = "@MyColumn" & index

            'Add a comma before all but the first value.
            If index > 0 Then
                query.Append(", ")
            End If

            'Append the placeholder to the SQL and add the parameter to the command
            query.Append(paramName)
            command.Parameters.AddWithValue(paramName, Me.ListBox1.SelectedItems(index))
        Next index

        query.Append(")")
End Select

command.CommandText = query.ToString()
command.Connection = connection

score 0 · Answer 2 · answered Jun 07 '18 at 09:36

0

Single quotes can be "escaped" by making them double single quotes. E.g. ' becomes ''.

However this approach is generally not recommended due to the high risk of SQL injection - a very dangerous and prevalent issues. See: https://www.owasp.org/index.php/SQL_Injection

To avoid this most libraries will include some type of escaping mechanism including the use of things like prepared statements in the Java world. In the .net world this may be of use: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.prepare(v=vs.110).aspx

answered Jun 07 '18 at 09:36

David

7,652
21
60
98

The main challenge here is that OP needs to pass one sql-parameter for every item in `gsAccounts.Split(',')` – Tim Schmelter Jun 07 '18 at 09:59
Not sure I follow, if each component (individual account code) is "escaped" prior to building the string by replacing single ' with '' wouldn't it end up as: `WHERE L.Account_Code IN('test''123', 'test''456', 'test''789')` and so on. – David Jun 07 '18 at 10:09

Alireza Nemeti · Answer 3 · 2018-06-07T11:47:38.530

If you only have one with a field, this is the easiest solution

    Private Function gsAccountsConvert(ByVal gsAccounts As String)
    Dim gsAccountsString As String = ""
    Dim StringTemp
    StringTemp = gsAccounts.Split(",")
    Dim i As Integer
    For i = 0 To UBound(StringTemp)
        StringTemp(i) = StringTemp(i).ToString.Trim
        If StringTemp(i) <> "" Then
            If StringTemp(i).ToString.Substring(0, 1) = "'" Then
                StringTemp(i) = """" & StringTemp(i).ToString.Substring(1, Len(StringTemp(i).ToString) - 2) & """"
            End If
        End If
        If i <> UBound(StringTemp) Then
            gsAccountsString = gsAccountsString & StringTemp(i).ToString.Replace("'", "''") & ","
        Else
            gsAccountsString = gsAccountsString & StringTemp(i).ToString.Replace("'", "''") & ""
        End If
    Next
    gsAccountsString = gsAccountsString.Replace("""", "'")
    Return gsAccountsString
End Function

String builder SELECT query when a variable has an apostrophe

3 Answers3