ADO Command Object

The ADO Command object has the functionality of executing commands against the data source, typically a database. In general, the Command object is created implicitly when executing an operation against the database. Therefore, you usually do not create this object in an explicit manner.

Implicitly, you can do this with the Connection object’s Execute() method, or with the RecordSet Open method. Each of these methods accepts as an argument of CommandText.

While the CommandText is usually a SQL statement, it can also be a stored procedure. The Command object contains the Parameters collection which allows for the use of parameters in conjunction with the Command object.

Syntax

To create the Command object, use the following syntax.

<%
Set oCmd = Server.CreateObject("ADODB.Command")
%>

Examples

In this example, we will explicitly create both the Command and Recordset object.

<%
Dim oConn, oRS, oCmd, ds, sql
ds = "Driver={MySQL ODBC 3.51 Driver};SERVER=db-hostname;DATABASE=db-name;UID=userID;PWD=password"
sql = "SELECT empName, empTitle FROM employees"

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open ds

Set oCmd = Server.CreateObject("ADODB.Command") 
oCmd.ActiveConnection = oConn
oCmd.CommandText = sql
oCmd.CommandType = 1
oCmd.Prepared = True

Set oRS = Server.CreateObject("ADODB.Recordset")
oRS.Open oCmd
%>

In the following example, we create the Command object explicitly, but the Recordset object implicitly.

<%
Dim oConn, oRS, oCmd, datasource, sql
datasource = "Driver={MySQL ODBC 3.51 Driver};SERVER=db-hostname;DATABASE=db-name;UID=userID;PWD=password"
sql = "SELECT empName, empTitle FROM employees"

Set oConn = Server.CreateObject("ADODB.Connection")
Set oCmd = Server.CreateObject("ADODB.Command") 

oCmd.ActiveConnection = oConn
oCmd.CommandText = sql
oCmd.CommandType = 1
oCmd.Prepared = True

oConn.Open datasource
set oRs = oCmd.Execute()
%>

The Command object gives us the ability to work with parameters. This approach is recommended and is one of the components you need to include in your strategy when protecting your application from SQL Injection Attacks.

<%
Dim oConn, oRS, oCmd, ds, sql, qs
ds = "Driver={MySQL ODBC 3.51 Driver};SERVER=db-hostname;DATABASE=db-name;UID=userID;PWD=password"
sql = "SELECT empName, empTitle FROM employees WHERE empID = ?"
qs = Request.Querystring("id")
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open ds

Set oCmd = Server.CreateObject("ADODB.Command") 
oCmd.ActiveConnection = oConn
oCmd.CommandText = sql
oCmd.CommandType = 1
oCmd.Prepared = True
oCmd.Parameters(0) = qs
oCmd.CommandTimeout = 900

set oRs = oCmd.Execute()
%>

As you can see in the previous example, our SQL query includes the WHERE clause so that your results only include data from a specific employee. The use of parameters allows us to treat the information stored in the query string as literal text.

So even if the attacker attempted to modify the query string and include other SQL statements, the SQL server would not execute the code. The following approach to constructing a SQL query is not recommended.

sql = SELECT empName, empTitle FROM employees WHERE empID = " & [Input from the User]

This input can contain SQL code which will be processed by the SQL server. For example, a malicious user can gain access to additional records, or DROP a table.

To protect against SQL Injection, constrain and validate the input from the user and use parameters with stored procedures, or use parameters with dynamic SQL.

Command Object Properties

Command Object Methods

Command Object Collections