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.
