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:

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:

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: 

// JScript example -- passing parameter values to a named command

var connectionString = '...'; // fill connection string here
var sql = 'SELECT * FROM Students WHERE FirstName = ?';

var conn = new ActiveXObject('ADODB.Connection');
conn.Open(connectionString);

var cmd = new ActiveXObject('ADODB.Command');
cmd.Name = 'GetStudents';
cmd.CommandText = sql;
cmd.ActiveConnection = conn;

// We need to instantiate the Recordset object in Javascript, because Javascript
// has no compile-time types, and ADO has no way of knowing that we're trying to
// pass in a Recordset
var rs = new ActiveXObject('ADODB.Recordset');
conn.GetStudents('Robert\' OR 1=1; --', rs);