DataStream
LANGUAGES: VB
TECHNOLOGIES: ASP.NET | ADO.NET | SQL Server| Access
Use Access With ASP.NET: Part II
Learn to prevent SQL Injection Attacks from affecting your Access-based apps.
By Wayne S. Freeze
One of the biggest limitations of using Microsoft Access as a database engine for ASP.NET applications is it doesn't support stored procedures. This makes building your application a little bit harder.
If you're building an ASP.NET application that uses SQL Server, there are many advantages to using stored procedures. First, stored procedures are compiled, which improves system performance. Second, stored procedures offer a way to isolate your application from the underlying database structures. Third, the way stored procedures are invoked from ASP.NET helps ensure your program is protected against SQL Injection attacks.
A SQL Injection attack occurs when you construct SQL strings in your application using data provided by the user then execute the SQL directly. If the user provides malicious data, they can trick your program into executing SQL statements of their own choosing. This means your database is open to attack.
Prevent SQL Injection Attacks
When you call a stored procedure with parameters, the data passed by the parameters is separate from the SQL statement. This means that no matter what information the user might enter, the meaning of the SQL statement can't be changed.
Even though Access doesn't support stored procedures, ADO.NET will let you use parameters with any SQL statement. This technique is named a parameterized query - a SQL statement that contains parameters where you would normally insert user data like this:
Select * From Customers Where CustomerId=?
The question mark is a placeholder for a parameter in the query. When the query is executed, the data will be inserted into the query in place of the question mark in such a way that the data will not compromise the integrity of the SQL statement.
Figure 1 contains a simple ASP.NET application that retrieves a row based on CustomerId from the Customers table in the Access database and displays them on a Web form.
Figure 1. A simple find-and-edit
ASP.NET application retrieves a row from the Customers table based on the
CustomerId value, then updates the same row with the information from the Web
form.
Pressing the Find Customer button triggers the LinkButton2_Click event shown in Figure 2. This event uses a parameterized query with a single parameter to retrieve the information from the database. The routine begins by declaring some variables needed to access the database along with a string containing the parameterized query.
Sub LinkButton2_Click(sender As Object, e As EventArgs)
Dim ds As DataSet
Dim dr As DataRow
Dim ConnectionStr As String = _
ConfigurationSettings.AppSettings("AccessConnStr")
Dim adpt As OleDbDataAdapter
Dim conn As OleDbConnection
Dim cmd As OleDbCommand
Dim Id As Integer
Dim SelectCommand As String = _
"Select CustomerId, Name, Street, City, State, ZipCode, " & _
"Phone, EmailAddress, MailingList, Comments From Customers " & _
"Where CustomerId=?"
Try
Status.Text = "Customer retrieved"
Id = CInt(CustomerId.Text)
ds = New DataSet()
conn = New OleDbConnection(ConnectionStr)
cmd = New OleDbCommand(SelectCommand, conn)
cmd.CommandType = CommandType.Text
cmd.Parameters.Add("CustomerId", OleDbType.Integer).Value = Id
adpt = New OleDbDataAdapter(cmd)
adpt.Fill(ds, "Customer")
if ds.Tables("Customer").Rows.Count = 0 Then
Status.Text = "Customer not found."
Else
dr = ds.Tables("Customer").Rows(0)
CustomerId.Text = dr("CustomerId")
Name.Text = dr("Name")
Street.Text = dr("Street")
City.Text = dr("City")
State.Text = dr("State")
ZipCode.Text = dr("ZipCode")
Phone.Text = dr("Phone")
EMailAddress.Text = dr("EMailAddress")
MailingList.Checked = dr("MailingList")
Comments.Text = dr("Comments")
End If
Catch ex as InvalidCastException
Status.Text = "Invalid Customer Id value."
Catch ex As OleDbException
Status.Text = "Database error: " & ex.message
If cmd.Connection.State <> ConnectionState.Closed Then
cmd.Connection.Close()
End If
Catch ex As Exception
Status.Text = "General error: " & ex.message
If cmd.Connection.State <> ConnectionState.Closed Then
cmd.Connection.Close()
End If
End Try
End Sub
Figure 2. This is how to load a customer's information from an Access database using OleDb and a parameterized query.
Next, a Try statement is used to trap any errors that might occur while processing the query. The CustomerId value from the form is converted to an Integer. If the user had entered a nonnumeric value, an InvalidCastException would occur, which would display an error to the user.
After creating new DataSet and OleDbConnection objects, a new OleDbCommand object is created using the parameterized query and the OleDbConnection. The CommandType property is set to CommandType.Text because the command is an SQL statement, not a reference to a stored procedure or a table.
An OleDbParameter object is added to the command's parameters collection. Even though the parameter was given the name CustomerId, the actual name of the parameter doesn't matter because the name isn't stored in the SQL statement. But the data type must be specified and it must agree with the data type of the column in the database.
The rest of this routine extracts the data from the database using an OleDbDataAdapter into a DataSet object, then copies the data from the first row to the fields on the form.
Use Multiple Parameters
You also can insert multiple parameters into an SQL statement:
Update Customers Set Name=?, Street=?, City=?, State=?,
ZipCode=?, Phone=?, EMailAddress=?, MailingList=?, Comments=?
Where CustomerId=?
Notice that a question mark is used to mark each parameter. Although this sounds confusing, as long as you add the parameters to the parameters collection in the same order as the parameters appear in the query, everything will work fine:
conn = New OleDbConnection(ConnectionStr)
cmd = New OleDbCommand(UpdateCommand, conn)
cmd.CommandType = CommandType.Text
cmd.Parameters.Add("Name", OleDbType.VarChar, 64).Value = Name.Text
cmd.Parameters.Add("Street", OleDbType.Varchar, 64).Value = Street.Text
cmd.Parameters.Add("City", OleDbType.Varchar, 64).Value = City.Text
cmd.Parameters.Add("State", OleDbType.Varchar, 2).Value = State.Text
cmd.Parameters.Add("ZipCode", OleDbType.Varchar, 10).Value = ZipCode.Text
cmd.Parameters.Add("Phone", OleDbType.Varchar, 32).Value = Phone.Text
cmd.Parameters.Add("EMailAddress", OleDbType.Varchar, 128).Value = EMailAddress.Text
cmd.Parameters.Add("MailingList", OleDbType.Boolean).Value = MailingList.Checked
cmd.Parameters.Add("Comments", OleDbType.LongVarChar).Value = Comments.Text
cmd.Parameters.Add("CustomerId", OleDbType.Numeric).Value = Id
As a side note, you also can use parameterized queries with SQL Server and the SqlClient namespace. The main difference in using parameterized queries in Access and SQL Server is that parameterized queries in SQL Server use names that begin with @, such as @CustomerId. This means you don't have to worry about ensuring the order of the parameters is the same as the query string because the parameters are matched by name rather than by order.
The sample code in this article is available for download.
Wayne S. Freeze is a full-time computer book author with more than a dozen titles to his credit, including Windows Game Programming with Visual Basic and DirectX (Que) and Unlocking OLAP with SQL Server and Excel 2000 (Hungry Minds). He has more than 25 years of experience using all types of computers, from small, embedded microprocessor control systems to large-scale IBM mainframes. Freeze has a master's degree in management information systems as well as degrees in computer science and engineering. You can visit his Web site at http://www.JustPC.com and send e-mail to mailto:[email protected]. He loves reading e-mail from his readers, whose ideas, questions, and insights often provide inspiration for future books and articles.
