1

I am attempting to query a DB from VBA, in order to avoid such issues a SQL injection I have been told I should parametrise the SQL string of code. The code I have at the moment, which returns no error, but no result either (!) is:

Sub ImportData_Click()


Dim strSql As String
Dim strDate As String
Dim StrDatetwo As String
Dim strinfo As String
Dim strinfotwo As String
Dim strgroup As String
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String

'Parameters
strDate = Format(Range("Date"), "DDMMMYY")
StrDatetwo = Format(Range("Datetwo"), "DDMMMYY")
strinfo = "someinfo"
strinfotwo = "total"
strgroup = "total"

'open connection
ConnectionString = "Provider=name ;Data Source=name;Initial Catalog=name;Integrated Security=SSPI"
objConn.Open ConnectionString

'SQL string
 strSql = "Declare @mindate date," & _
                  "@maxdate date " & _
         "set @mindate ='" & strDate & _
         "'; set @maxdate ='" & StrDatetwo & _
         "'; SELECT MIN([results].run_id) " & _
         "FROM [results] " & _
         "inner join [official_run_table] on ([results].run_id=[official_run_table].run_id and [official_run_table].run_type_id='1' )" & _
         " WHERE Info ='" & strinfo & _
         "' and two ='" & strinfotwo & _
         "' and group= '" & strgroup & _
         "' AND [results].date between @mindate and @maxdate"

'MsgBox strSQL '- to check data

rst.Open strSql, objConn


If Not rst.EOF Then
Worksheets("Reference").Range("E2").CopyFromRecordset rst
rst.Close
Else
MsgBox "Error: No records returned.", vbCritical
End If


'Clean up
 objConn.Close
 Set objConn = Nothing
 Set rst = Nothing

 End Sub

No error but no result would imply no values exist in the DB for this query, but I know otherwise! It is my intention to a parametrise strinfo, strinfotwo, strgroup as well, but first I would like to get the above working. How am I getting this wrong? Thanks in advance

Improve this question

2 Answers 2

Reset to default
1

First, parameterize the query string by using ? parameter tokens (for OLE DB and ODBC drivers) instead of string literals. Note parameter values within the query string are not enclosed in quotes. I enclosed the group column name in square brackets because it is a reserved SQL keyword and doesn't conform to the rules for identifiers. 

 strSql = "SELECT MIN([results].run_id) " & _
         "FROM [results] " & _
         "inner join [official_run_table] on ([results].run_id=[official_run_table].run_id and [official_run_table].run_type_id='1' )" & _
         " WHERE Info = ?" & _
         " AND two = ?" & _
         " AND [group] = ?" & _
         " AND [results].date between ? and ?;"

The example below uses a ADODB.Command object and adds parameters to the query. I guessed at your actual data types so change the parameter types and lengths to match your actual table schema. This is one of many ways to accomplish this task but should get you started. See the ADODB Command reference for more information and examples.

Set parmInfo = command.CreateParameter("@Info", adVarChar, adParamInput, 10, strinfo)
Set parmInfoTwo = command.CreateParameter("@InfoTwo", adVarChar, adParamInput, 10, strinfotwo)
Set parmGroup = command.CreateParameter("@Group", adVarChar, adParamInput, 10, strgroup)
Set parmMinDate = command.CreateParameter("@MinDate", adDBTimeStamp, adParamInput, , strDate)
Set parmMaxDate = command.CreateParameter("@MaxDate", adDBTimeStamp, adParamInput, , strDatetwo)

command.Parameters.Append(parmInfo)
command.Parameters.Append(parmInfoTwo)
command.Parameters.Append(parmGroup)
command.Parameters.Append(parmMinDate)
command.Parameters.Append(parmMaxDate)

Set rst = command.Execute()
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Cheers Dan. Appreciated
0

The best way is to build the stored procedure on the server, then pass the parameters. It's definitely open to injection as-is.

Improve this answer

6 Comments

I agree at the moment it is. I, unfortunately, do not have access to the DB...so I am forced to try to improve the above and place input checks in the s/s plus lock the VBA code. Not ideal...but I have no choice.
how do I parametrise this above properly? I have been searching the net and only really come up with mssqltips.com/sqlservertip/2981/… but that doesn't help me beyond what I already know.
perhaps I need a Parameter Object?
Declare the parameter in vba, then your sql string would be sp_executesql([function],[parameters])
sorry to be tedious, do you have an example?
|