VBA - SQL Query String

Question

I have declared variables that store the name of columns from a SQL Table as well as variables that store their corresponding "values to find".

Dim sColumn1 As String
Dim sColumn2 As String
Dim sColumn3 As String
Dim sValue1 As String
Dim sValue2 As String
Dim sValue3 As String

sColumn1 = Sheets(1).Range("A1").Value
sColumn2 = Sheets(1).Range("B1").Value
sColumn3 = Sheets(1).Range("C1").Value
sValue1 = Sheets(1).Range("A2").Value
sValue2 = Sheets(1).Range("B2").Value
sValue3 = Sheets(1).Range("C2").Value

I want to make a dynamic query like this:

StrQuery = "SELECT * FROM dbo.Table1 WHERE ('" & sColumn1 & "') LIKE ('" & sValue1 & "') AND ('" & sColumn2 & "') LIKE ('" & sValue2 & "') AND ('" & sColumn3 & "') LIKE ('" & sValue3 & "')"

This code does not generate any errors but IT DOES NOT pull any records either. I have confirmed and all the variables are being assigned the right values.

The query above works fine if I replace the Column variables for the actual column names in the SQL Table. Like this:

StrQuery = "SELECT * FROM dbo.Table1 WHERE Column1 LIKE ('" & sValue & "') AND Column2 LIKE ('" & sValue2 & "') AND Column3 LIKE ('" & sValue3 & "')"

With this string I get results without any problem but the columns will be dynamic. Users will choose from a variety of 15 columns.

Why is it that when I use the Variable it does not work even though I know the value of the variables matches exactly the names of the Columns in the SQL Table?

Am I using the wrong format in the string so that it reads the actual value stored within the variables?

You need to read about, understand and start using parameterized queries. This is wide open to sql injection. My friend bobby tables loves this style of coding. bobby-tables.com — Sean Lange
– Sean Lange, Commented Mar 1, 2018 at 17:42
To add to what @MarkPM is saying, when you put your column names inside single quotes they become a string literal. This isn't what you want, you want to look at the value in the column. :) — Sean Lange
– Sean Lange, Commented Mar 1, 2018 at 17:44
@FlorentB. SQL injection isn't solely about security. Ask Mr. O'Neil and Mrs. Null. — Mathieu Guindon
– Mathieu Guindon, Commented Mar 1, 2018 at 18:03
To be clear: the VBA code gets to deal with an obscure syntax error in the SQL statement that was sent to the database. "we don't need to protect against SQL injection" is just wrong - any vulnerable code is code waiting to blow up, be it in PHP, Java, C#, ...or VBA. Doing it right is simple enough that I can't fathom a reason to perpetuate the notion that concatenating user input is acceptable under any circumstance, especially on this site. — Mathieu Guindon
– Mathieu Guindon, Commented Mar 1, 2018 at 18:23

Mathieu Guindon · Accepted Answer · 2018-03-01 18:04:26Z

3

Warnings above about using parameterized queries still apply but this is how you would get this to work:

StrQuery = "SELECT * FROM dbo.Table1 WHERE " & _
                      sColumn1 & " LIKE ('%" & sValue & "%')" & _
            " AND " & sColumn2 & " LIKE ('%" & sValue2 & "%')" & _
            " AND " & sColumn3 & " LIKE ('%" & sValue3 & "%')"

edited Mar 1, 2018 at 18:04

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

answered Mar 1, 2018 at 17:49

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

1 Comment

Dave Over a year ago

Thanks for your help! I'm sorry I did not respond earlier. Lots of changes happening at work. This project had been placed on hold. Now that I'm getting back to it I will definitely be rewriting this part of the code to implement parameterized queries. This instruction did not seem to fix the problem though. Queries did not generate any errors but no results either. Thanks a lot though!!

M.Doerner · Accepted Answer · 2018-03-01 20:25:48Z

The answer by @TimWilliams should address the problem of not getting any result, provided the input is valid. However, as stated in the comments, the code is a bit brittle because entering malformed or otherwise inappropriate values into the fields of the sheet might produce interesting results. So, I would like to suggest a somewhat more robust approach than simply executing the SQL string.

If you are using ADO to talk to the SQL Server, you can call stored procedures on it as explained in this SO answer. Furthermore, provided you are at least on SQL Server 2008, there is the stored procedure sp_executesql. This stored procedure allows you to execute a SQL string containing parameters. The first parameter is the SQL string, the second a string containing the parameter list and the following parameters are the actual parameters for the query. This allows you to pass in the strings representing the LIKE pattern as actual string parameters. So, no matter what the values are, they cannot break the query itself.

Regarding the column names, you should at least escape them with square brackets. That is not waterproof, but it already goes a long way regarding accidentally malformed values.

The result would be something like

sqlString = "SELECT * FROM dbo.Table1 " & _
            "WHERE [" & sColumn1 & "] LIKE @value1 " & _
            "    AND [" & sColumn2 & "] LIKE @value2 " & _
            "    AND [" & sColumn3 & "] LIKE @value3 "

parameterDeclarationString = "@value1 AS NVARCHAR(1000)," & _
                             "@value2 AS NVARCHAR(1000)," & _
                             "@value3 AS NVARCHAR(1000)"

Note that the max length of the parameters is just an arbitrary guess for a sensible upper limit.

Thanks for your help! I'm sorry I did not respond earlier. Lots of changes happening at work. This project had been placed on hold. Now that I'm getting back to it I will definitely be rewriting this part of the code to implement parameterized queries. Thanks a lot!!

Collectives™ on Stack Overflow

VBA - SQL Query String

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related