Use ADO to Enforce Strong Typing
Strong data typing is a valuable weapon in the fight against SQL injection attacks. Using the ADO Command object can help you ensure not only that you're passing the right data but that SQL Server can tell which part of a query is SQL and which is data. Let's look at two ways you might invoke a stored procedure. The following procedure is a call directly from an ASP page:
<%
Set Conn =
Server.CreateObject("ADODB.Connection")
Conn.open application("connection_string")
Set RS = Conn.Execute("exec
sp_checkloginrights " & param1 &
"," & param2 )
%>
This code is a poor example of invoking a stored procedure from Visual Basic (VB). The code uses a "string-building" technique that lends itself to mischief because of poor input validation.
The second call, which Listing 2 shows, is a sample of VB code that you can use to invoke the same stored procedure from a COM DLL. How does the second procedure call provide an added level of security? The answer lies in the fact that you're no longer "string building" as you are in the ASP code above. In string-building SQL calls, you construct a text string and pass it to SQL Server in one call. In the COM DLL example, however, you use the Command object to enforce your data types and let ADO build the string for you. The primary advantage of the VB-COM approach is that it catches any mistakes caused by front-end programmers not properly validating input.
Note that you need to handle errors in a way that doesn't give a potential attacker any clues about your table structure. Any database-related errors should return a generic error message and log the details for developers to analyze. Displaying ADO error messages gives the end user too much information and could be dangerous in the wrong hands. Also, never assume the people on the other tier are doing good validation: They're probably assuming the same thing about you. If you apply good validation at every tier, a neglected piece of code is less likely to expose your entire application.
Centralize Data Access
Now that you know the safest way to assemble your ADO queries to SQL Server, why not use a COM data-access layer to centralize the data-access routines? A centralized data-access strategy might not seem like a security concern, but unifying data access through a controlled set of components lets you more strictly control how users access data and helps ensure that you're following best practices. As a side benefit, if you find a more secure data-access method later, a centralized data-access strategy will ease the conversion because your application's other components don't need to be affected by any changes you make.
Figure 3 shows a high-level view of what your overall application architecture should look like. In this architecture, an ASP page calls a COM DLL method such as Basket.AddItem and passes some parameters. Your application should validate those parameters before passing them to a data-access component (another COM DLL) such as OrdersDB.AddLine. This COM DLL uses ADO to execute a stored procedure such as sp_lineitems_table_add on SQL Server. You might think that this process sounds like a lot more work than just calling SQL Server from your ASP page. The first time you write the necessary code, that assumption might be true, but these components are reusable, so you'll soon make up that time when you need to perform the same actions later.
If you've ever examined Microsoft's reference applications, such as Duwamish Books or Fitch and Mather (available at http://msdn.microsoft.com/code), you'll find the centralized data-access theme repeated over and over. Using a COM component is a good design practice for many reasons, one of which is that such a design lets you control access to SQL Server at every tier. And the more you centralize, the easier SQL Server is to secure and maintain. If complexity is the enemy of security, then centralized, simplified data access might be one of security's best friends.
Prev. page
1
2
[3]
4
next page 
