Running a sql select command with a vba string variable that contains an apostrophe

Question

Using VBA, I'm trying to select data from a SQL table, but am running into an issue because the Customer name I'm looking up contains an apostrophe (McDonald's). Instead of using a replace function, I'm trying to setup customerName as a parameter to avoid this issue.

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Dim cmd As New ADODB.Command
        customerName = .Range("customerName")

        queryCustomers = "select * from Customers where CustomerName = '" & customerName & "'"

        'Open a connection to SQL Server
        conn.Open cnstring

        With cmd
         .CommandText = queryCustomers
         .Parameters.Append .CreateParameter("@param1", adVarChar, adParamInput, 50, customerName)
         .ActiveConnection = conn
         .CommandType = adCmdText
        End With

        Set rs = cmd.Execute

I'm getting the same error because of the apostrophe which means the parameter setup I'm using is incorrect. In summary, I need help to make my queryCustomers string pass customerName as a parameter so the apostrophe in customerName does not affect the SQL command.

You need to pass these values as parameters, not because it causes you problems, but because of the problems it will avoid. Your code is wide open to sql injection. You can read more about how bad this anti-pattern is here. That site also has examples of how to properly parameterize values. — Sean Lange
– Sean Lange, Commented Jul 15, 2019 at 19:19
Check out Little Bobby Tables - absolute classic!! And then learn from it and NEVER EVER do that again! — marc_s
– marc_s, Commented Jul 15, 2019 at 19:21
When you use a parameter the code will automatically escape any characters, if it needs to. The problem is because you aren't using parameters. — Thom A
– Thom A ♦, Commented Jul 15, 2019 at 19:21
try this evona.nl/simple-parameterized-queries-using-ado-in-vba — Alex Kudryashev
– Alex Kudryashev, Commented Jul 15, 2019 at 19:28
@Larnu so do you know how to fix that? I'm not familiar with using parameters here. Thanks — Codelinsky
– Codelinsky, Commented Jul 15, 2019 at 19:29

Matus · Accepted Answer · 2019-07-15 19:29:05Z

1

Your

queryCustomers = "select * from Customers where CustomerName = '" & customerName & "'"

gets evaluated to

"select * from Customers where CustomerName = 'McDonald's'"

There is no variable where the value can be placed. This is what is executed

Try something like this

cmd.CommandText = "select * from Customers where CustomerName = @Value1"
Dim param1 As ADODB.Parameter
Set param1 = cmd.CreateParameter("@Value1", adVarWChar, adParamInput, 8, "McDonald's")
cmd.Parameters.Append param1

This approach should escape your McDonald's -> McDonald''s automatically.

answered Jul 15, 2019 at 19:29

Matus

1812 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Codelinsky Over a year ago

Thank you for your reply. cmd.CommandText = "select * from Customers where CustomerName = @Value1" Dim param1 As ADODB.Parameter Set param1 = cmd.CreateParameter("@Value1", adVarWChar, adParamInput, 50, customerName) cmd.Parameters.Append param1 Set rs = cmd.Execute returns an error on the execute line: "the connection cannot be used to perform this operation. it is either closed or invalid in this context."

Codelinsky Over a year ago

Ah I had to set cmd activeconnection to fix that. But I still get an error: "Must declare the scalar variable "@Value1"

Mathieu Guindon Over a year ago

@Codelinsky use ordinal parameters instead of named ones. cmd.CommandText = "select * from Customers where CustomerName = ?"

Codelinsky Over a year ago

Worked. Thank you :)

Collectives™ on Stack Overflow

Running a sql select command with a vba string variable that contains an apostrophe

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related