I need to use SQL extensively in VBA macros that I write. Since the DB is from our enterprise application, I use vendor's library for this.
I generate dynamic queries, sometimes with many JOIN
s and UNION
s, and the queries can be quite complex.
The procedure that I coded screams "I'm open to SQL injection". The macros using this function are all internal but I need to make it safe anyway.
How can I make the below code only run SELECT
statements and safe against injections?
Please note that it is not possible to make views for all possible SELECT
statements and use them instead, according to the parameters, one or more DB's need to be queried and UNION
'd sometimes.
How can I safely query an arbitrary number of columns with an arbitrary number of constraints from an arbitrary number of tables in an arbitrary number of DBs?
'***************************************************************************'
'Evaluates and executes the passed query and returns the results '
'Parameters: '
' Query: The main query body with optional placeholders like #0 starting '
' with 0 '
' Args : Optional arguments to replace placeholders '
'Returns: '
' The resulting recordset '
'***************************************************************************'
Public Function DirectQuery( _
Query As String, _
ParamArray Args() As Variant _
) As NetRS
'Declarations **************************************************************'
Dim ReturnRs As NetRS
Dim Params As Variant
'***************************************************************************'
On Error GoTo ErrorHandler
'This code shouldn't be used
MsgBox "This code is dangerous. Don't copy-paste and use it."
Exit Function
Params = Args
Set ReturnRs = 3rdPartyObject.NewRecordset(DBConnection)
ReturnRs.Open EvaluateQuery(Query, Params)
Set DirectQuery = ReturnRs
Set ReturnRs = Nothing
Exit Function
ErrorHandler:
End Function
'***************************************************************************'
'***************************************************************************'
'Evaluates the arguments in a query string and returns the formed query '
'Parameters: '
' Query: The main query body with optional placeholders like #0 starting '
' with 0 '
' Args : Optional arguments to replace placeholders '
'Returns: '
' The formed query string '
'***************************************************************************'
Public Function EvaluateQuery( _
Query As String, _
ParamArray Args() As Variant _
) As String
'Declarations **************************************************************'
Dim i As Integer
Dim Params As Variant
'***************************************************************************'
On Error GoTo ErrorHandler
Params = Args
EvaluateQuery = Query
For i = LBound(Params(0)) To UBound(Params(0))
EvaluateQuery = Replace(EvaluateQuery, "#" & i, Params(0)(i))
Next i
Exit Function
ErrorHandler:
EvaluateQuery = ""
End Function
'***************************************************************************'