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. http://bobby-tables.com/ — Sean Lange, Mar 01 '18 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, Mar 01 '18 at 17:44
@Sean Lange, preventing SQL injection is useless here since the end user has directly access to the connection and can run his own queries. — Florent B., Mar 01 '18 at 17:59
@FlorentB. SQL injection isn't solely about security. Ask Mr. O'Neil and Mrs. Null. — Mathieu Guindon, Mar 01 '18 at 18:03
@FlorentB. I don't care what the pedantic definition is. Turning user input into executable SQL, accidentally or not, *is* SQL injection. The only difference is whether it's an "attack" or not. The bottom line is that the very same mechanics can be responsible for stupid and easily avoidable bugs, by correctly dealing with /parameterizing user input in SQL statements. — Mathieu Guindon, Mar 01 '18 at 18:09
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, Mar 01 '18 at 18:23
Thanks to all 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 though!! — Dave, Mar 10 '18 at 14:45

score 3 · Answer 1 · edited Mar 01 '18 at 18:04

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 01 '18 at 18:04

Mathieu Guindon

69,817
8
107
235

answered Mar 01 '18 at 17:49

Tim Williams

154,628
8
97
125

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!! – Dave Mar 10 '18 at 14:44

score 0 · Answer 2 · answered Mar 01 '18 at 20:25

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!! — Dave, Mar 10 '18 at 14:42

VBA - SQL Query String

2 Answers2