For many developers, building applications is a lot like building a family. The Business tier is like the oldest child: mature and responsible, it knows how to handle everything, and is good at telling people what to do. The Presentation tier is like the youngest child. The baby of the family, this one is cute, flashy, and gets all the attention. The Data tier is often like the middle child: pivotal to the family unit but largely unnoticed, insecure, and left to fend for itself. In this article, I'm going to show you how to nurture your middle child, the Data tier, and give it the tools it needs to survive in harsh environments.
Lock down data access
You can never be 100-percent secure when your database is connected to the Internet. The goal is to minimize your surface exposure to reduce the number of ways an intruder can gain access. It can be difficult to stay on top of security threats, but there are some very basic ways to keep your data safe and write more maintainable code.
The most popular method of intruder attack is called an "injection attack." These attacks take advantage of your user interface and direct-query methods to pass unexpected information to your database. By direct queries, I mean the kind in which your code looks like the code in Listing A.
The code is executed directly against the database. Now, if your Web application (or really any application for that matter) has a registration system that asks for your first name, an attacker can enter DELETE * FROM Users into that field and submit the form. With all the users deleted, you'd better hope you have a nightly backup.
These attacks are easily thwarted, however, by using parameterized stored procedures, or SPROCs, instead of direct queries against your tables. Besides the increased performance you get from executing precompiled routines, using SPROCs with parameters gives you a greater level of abstraction in your applications. You can change your table names or data processing routines without having to change anything in your data access layer, and consequently having to recompile and redeploy your application. Further, if someone tries to introduce SQL statements into your form, the SPROC will throw a syntax error and halt execution. This is the simplest method to guard your resources from an attack, and it carries my highest recommendation.
The greatest predator for your database is the improper handling of user permissions, combined with my previous example of the use of direct queries against database tables. Most developers use the same SQL login information to design their database as they use to give their application access to it.
In our real-world example, it's like locking up your shotgun in your gun rack and then giving your kids the key. If you already use separate logins, then you're on the right track. But if you allow that restricted user to have direct query permissions because you got lazy and didn't want to build a data access layer or SPROCs, you might as well leave the door to your database wide open, because you've already left the key under the mat.
Resolving this dilemma requires a few simple steps. First, create a separate user account on your SQL Server for your Web applications. If you're hosted by an outside provider, some companies charge extra for this service. Assign this user to the "public" role, and give it execute permissions only on the SPROCS.
After that, explicitly deny access to all direct query types (e.g., SELECT, INSERT, UPDATE, DELETE). Finally, update your connection string, and your application will be secure. This way, if your Web site is ever physically compromised, you can be safe in knowing that you didn't just hand your intruder db_owner access permissions. In conjunction with my first tip, implementation of this method can reduce your exposure to near zero.
Take care of the code
Once you've dealt with security, it's time to discuss discipline. In the .NET world of managed code, the garbage collector is like the nanny. The nanny is there to pick up after the little ones, and generally ensure that resources are readily available when they are needed. As the children grow up, however, they should be able to pick up after themselves.
The same is true with your garbage collection. You should explicitly dispose of all connections when finished with them. This increases your memory utilization and performance by freeing up the resources as soon as they are available. If you don't, you could have hundreds of connections open before the garbage collector cleans it up. Or worse, you could have closed your connections, but still have memory allocated to the connection object.
Here are some samples that illustrate my recommendations. The first example, in Listing B, is a sample from a typical Web site. Note the lazy coding, direct query building, and lack of a data access layer entirely. This code was inside the code-behind for the Web form. See if you can spot the errors in Listing B.
This code is typical of about 95 percent of the .NET developers out there, and it is a horrible practice left over from the ASP era. It almost completely negates the object-oriented powers of .NET.
The second example, shown in Listing C, takes the same concepts as in the first example, and refactors them into a three-tiered, five-layered system. Many of Microsoft's reference applications use this architecture, including the ASP.NET Starter Kits. For brevity, the table structure and stored procedures are omitted.
As you can see, the architecture in Listing C is extremely clean, readable, and robust. It's worth noting that Microsoft has also provided the Data Access Application Block, which can do most of the legwork for you.
You might have noticed in Listing C that I wrapped the actual work in a Try/Catch statement. When you catch the SqlException, you throw a new DataAccessLayerException. Don't worry—it's nothing more than a basic custom exception. This allows you to track and handle problems by persisting them up the layers in an understandable format.
However, the example is not yet complete. In Listing B, the function did some extra work outside the realm of data access. The code in Listing D completes the original function.
At this point, you have a complete, secure solution with clean, efficient code. If you need to change how your data is processed, as long as you use the same SPROC and the same parameters, your Login function will always be valid.
Keep the system updated
For those developers with access to their own server, there are several ways to further protect your applications. First, be sure to update your servers with software patches when they become available. Second, set up a second NIC in your Web server that attaches to the SQL cluster exclusively. This puts data layer in what I like to call a playpen.
The only downfall to this configuration is that you either have to install a Web-based knockoff of Enterprise Manager, or do your database work through a remote desktop connection to your Web server. It is a little bit more of a hassle from a management standpoint, but it can be well worth it for the peace of mind.