Did you find this useful?
Socialize it today.


ADO Command Object

Monday, September 24, 2012

tags ADO

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's Open method. Each of these methods accepts as an argument of "Command Text." While the Command Text 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 sting as literal text. So even if the attacker attempted to modify the query string and include other SQL statements, 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

PropertyDescription
ActiveConnectionSets or returns a definition for a connection
CommandTextSets or returns a provider command
CommandTimeoutSets or returns the number of seconds to wait while attempting to execute a command
CommandTypeSets or returns the type of a Command object
NameSets or returns the name of a Command object
PreparedSets or returns a Boolean value. True improves performance on subsequent queries.
StateReturns the state of the object (open, closed, connecting, executing or retrieving data)


Command Object Methods

MethodDescription
CancelCancels an execution method
CreateParameterCreates a parameter
ExecuteExecutes the query, SQL statement or procedure in the CommandText property


Command Object Collections

CollectionDescription
ParametersContains the Parameter objects
PropertiesContains the Property objects


Please help us spread the word by socializing it today!

email contact us

Did you find something wrong with the information on this page? Please take a moment to report it to us so that we can continue to improve the quality of the information on this site. Click here to report an issue with this page.



Recommended Books & Training Resources

ADO: ActiveX Data Objects ADO Programmers Reference ADO Programming for Dummies