My previous post on this topic generated so much discussion that I thought I should post about it some more. Specifically I wanted to write about means of checking your code for possible SQL Injection problems.
The first means of checking, if you are using Visual Studio 2008, is to use the Team System environment. Specifically, there is a code analysis rule in there that will check for some SQL issues.
Note: This set of rules is not a complete set of possible security rules, nor are the existing rules 100% guaranteed to find all the problems in their area of analysis. No code analysis tool can replace a formal security audit. Nevertheless, we have found inside Microsoft that this can be of great help finding potential security issues.
This is a very useful first pass that you can use to check for some issues. It will find code that you have that looks like:
someCommand.CommandText = "SELECT AccountNumber FROM Users " + "WHERE Username='" + name + "' AND Password='" + password + "'";
If you aren't using Visual Studio 2008, which you should use as it is our best development environment yet, there are some other alternatives that you can use to check for SQL Injection vulnerabilities.
I haven't tested any of these, so feel free to comment on them if you have used them or add additional suggestions.
Microsoft® Visual Studio Team System 2008 Database Edition Power Tools – this includes a T-SQL Static Code Analysis feature.