COM / Automation

COM defines a standard way for software components to interact with each other, independent of the programming language in which they are written or consumed. Environments and languages which can be used to consume COM objects include:

VBA / VB6

/ .NET languages

Microsoft scripting hosts , which can support multiple scripting languages, including VBScript and JScript (available by default) Internet Explorer (and Microsoft HTML Applications (MSHTA)) "classic" ASP Windows Script Host

, which can support multiple scripting languages, including VBScript and JScript (available by default) Borland Delphi

Python with the PyWin32 extensions, using the win32com package

with the PyWin32 extensions, using the package Unmanaged C++

There are currently two data access technologies for COM environments in common use:

Data Access Objects (DAO), later renamed to Access Database Engine (ACE)

ActiveX Data Objects (ADO)

DAO / ACE

DAO allows direct execution of raw SQL in a number of places:

the Execute method, on the Database and Connection objects

method, on the and objects the OpenRecordset method, on the Database and Connection objects

method, on the and objects QueryDef objects the CreateQueryDef method, on the Database and Connection objects the QueryDef.SQL property

objects

but only a QueryDef can make use of parameters, and thus make SQL safe from SQL injection.

DAO parses the SQL statement passed into the CreateQueryDef method or set as the value of the SQL property, and automatically determines the number, names, and types of the parameters.

The value of the parameter can then be set.

' VBA example -- SELECT with placeholder ' dbs refers to an instance of DAO.Database Dim qdf As QueryDef Set qdf = dbs.CreateQueryDef("", "SELECT * FROM Students WHERE FirstName = ?") ' Using default properties qdf.Parameters(0) = "Robert' OR 1=1; --" ' The equivalent without default properties would be: ' qdf.Parameters.Item(0).Value = "Robert' OR 1=1; --" Dim rs As DAO.Recordset Set rs = qdf.OpenRecordset

DAO also supports the use of named parameters. Any unrecognized identifier in the SQL statement will be treated as a named parameter:

' VBA example -- action query; doesn't return results ' dbs refers to an instance of DAO.Database Dim qdf As QueryDef Set qdf = dbs.CreateQueryDef("", "DELETE * FROM Students WHERE FirstName = MatchingFirstName") qdf.Parameters("MatchingFirstName") = "Robert' OR 1=1; --" qdf.Execute

In order to explicitly define the types and/or names of the parameters, use the PARAMETERS clause in the SQL:

' VBA Example -- explicit parameters ' dbs refers to an instance of DAO.Database Const sql = _ "PARAMETERS MatchingFirstName TEXT; " & _ "SELECT * " & _ "FROM Students " & _ "WHERE FirstName = MatchingFirstName" Dim qdf As QueryDef Set qdf = dbs.CreateQueryDef("", sql) qdf.Parameters("MatchingFirstName") = "Robert' OR 1=1; --" Dim rs As DAO.Recordset Set rs = qdf.OpenRecordset

ActiveX Data Objects (ADO)

ADO provides three ways in which raw SQL can be passed to the data source:

but only with commands can SQL injection be prevented, by using placeholders for values within the SQL, and parameters:

' VBA example -- using commands with explicit parameter objects ' Add a reference to "Microsoft ActiveX Data Objects" Const connectionString = "..." 'fill connection string here Const sql = "SELECT * FROM Students WHERE FirstName = ?" Dim cmd As New ADODB.Command cmd.ActiveConnection = connectionString cmd.CommandText = sql Dim prm As New ADODB.Parameter prm.Type = adVarChar prm.Size = 255 prm.Value = "Robert' OR 1=1; --" cmd.Parameters.Append prm ' Instead of instantiating a new Parameter object, you can also use the command's ' CreateParameter method, but you still must manually add the parameter to the ' command's collection of parameters, as above Dim rs As ADODB.Recordset Set rs = cmd.Execute

Parameter values can also be passed in to the Command.Execute method (wrapped in a SAFEARRAY):

'VBScript example -- passing parameter values wrapped in a SAFEARRAY, to the Execute method Const connectionString = "..." 'fill connection string here Const sql = "SELECT * FROM Students WHERE FirstName = ?" Dim cmd Set cmd = CreateObject("ADODB.Command") cmd.ActiveConnection = connectionString cmd.CommandText = sql Dim rs Set rs = cmd.Execute(, Array("Robert' OR 1=1; --"))

In addition, if the command's Name property has been set, the command can be executed directly from the connection object using the name; this is called a named command. Values which are passed into the command call will be used as parameter values: