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.

Answer 1

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