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
