Use Stored Procedures
In many ways, using stored procedures is like getting something for nothing. Stored procedures enforce strong data typing, allow for abstracted access to other SQL Server objects, and improve performance by precompiling your SQL code. I recommend you use stored procedures as much as possible: They give improved security with no performance penalty, a combination that doesn't come along often.
Despite the potential benefits, it's important to note that if you use stored procedures but still use string-building techniques to construct your queries, you're no better off than before. Stored procedures are most effective when you use them in conjunction with the other techniques in this article and employ the ADO Command object. If you simply assemble a string and pass it to the Connection object for execution (as in the first ASP example in this article), your system is just as vulnerable as it would be if you were using raw SQL. So remember to combine these strategies for effective security.
Here's a simple strategy I use when developing a new application. First, record all the ways you predict that users will need to access, update, or delete SQL Server data; then, code stored procedures for all the scenarios. If you're diligent, you can give users execute rights only to the stored procedures and never give them direct rights to any other database objects. But be sure to avoid breaking ownership chains. A broken ownership chain occurs when the creator of a stored procedure or view doesn't own the underlying objects. When SQL Server detects a broken ownership chain, it verifies that the person executing the stored procedure or view has the necessary rights to those objects. Making sure that dbo owns all database objects solves this problem nicely. The benefits of this strategy are obvious. Even a successful SQL injection attack might yield no fruit because the malicious user can't access the tables that contain the data.
Test, Test, Test
So you think you've done everything right, and you're ready to head out the door. Whoa there, my friend! If you're human, you forgot something somewhere. But when you're dealing with a finished application, how do you search for those chinks in the armor? You could approach your system the same way the attackers do: by trial and error. One method I use is creating a unique piece of SQL injection code, then pasting it into all my input fields while I run SQL Server Profiler with a filter containing my unique piece of SQL. By using this technique, I get immediate feedback about what SQL was passed to the server.
The testing phase is your chance to stay one step ahead of the crackers. You have all the code, you can trace all the events, and you have a firm grasp of the entire architecture. Put yourself in the position of the attacker and do your best to break in, using your deep knowledge of the internals of the application. Take advantage of your testing phase—a good quality-assurance staff can be a great asset in making sure an application ships secure.
Rest Secure
To keep your SQL Server—based applications secure, you need to be aware of what code is getting back to the server at every tier. Remember to limit user privileges, validate thoroughly, use ADO to maintain strong data typing, and keep your data access centralized. Use stored procedures as often as you can, and when you think you're through, test and test again. Get serious about securing your existing architectures—and maybe, just maybe, you can catch a few winks.
End of Article
Prev. page
1
2
3
[4]
next page -->
