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 + "'";
This is based on FxCop which is a tool that you can download for free here. There is also a blog about this tool that has a lot of useful information, including updates, here.
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.
Acunetix WVS
Cenzic Hailstorm
Wapiti
Microsoft® Visual Studio Team System 2008 Database Edition Power Tools – this includes a T-SQL Static Code Analysis feature.
SQL Injection and how to avoid it
It isn't as big of a deal at the moment, but it is always good to make sure everyone is aware of this and how dangerous it can be. There is some very good information on it located on MSDN here. The important part is to remember that anytime you take input from an external source (someone typing on a web page), they don't always have to put in what you expect.
The safest way to keep yourself safe from SQL Injection is to always use stored procedures to accept input from user-input variables. It is really simple to do this, for example, this is how you don't want to code things:
var Shipcity;
ShipCity = Request.form ("ShipCity");var sql = "select * from OrdersTable where ShipCity = '" +
ShipCity + "'";
This allows someone to use SQL Injection to gain access to your database. For example, imagine if someone put in the following for the "ShipCity":
Redmond'; drop table OrdersTable--
This would delete the entire table! If you have seen much on SQL Injection, they have figured out all kinds of ways to get information about your database or server, so don't think they can't find the names of tables, etc.
The correct way to do this would be using a stored procedure as follows:
SqlDataAdapter myCommand = new SqlDataAdapter("AuthorLogin", conn);myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parm = myCommand.SelectCommand.Parameters.Add("@au_id",SqlDbType.VarChar, 11);
parm.Value = Login.Text;
Then you will be protected. Be sure to use parameterized stored procedures to keep the stored procedure from having the same problem as before.
-- Update --
The above code would call a stored procedure that would be something like:
CREATE PROCEDURE AuthorLogin @au_id varchar(11)
AS
SET NOCOUNT ON
SELECT Author from AuthorTable WHERE au_id = @au_id
GO
Note: the "SET NOCOUNT ON" will prevent SQL Server from sending the DONE_IN_PROC message for each statement in a stored procedure which will improve performance especially for large stored procedures.
-- End Update --
There are other hints and advice on the MSDN article that you can check out, but this is the major piece of advice to know.
There is also some additional information that you can find here. You can find more information and a video at Explained – SQL Injection and another video about it here. There are tons of links on the web so feel free to research this more to be sure you are safe from this problem.
Here are a few other links to help on the subject:
SQL Injection Attack from the SWI team at Microsoft
Preventing SQL Injections in ASP
Filtering SQL Injection From Classic ASP
Classic ASP which is still alive and parameterized queries
ISAPI filter to protect against SQL Injection
Michael Sutton's Blog on SQL Injection