Type Safe SQL Parameters and Update/Insert of database question

[Philss100]

Hi, I have been in the process of updating my code with security methods, and I've been learning this from http://msdn.microsoft.com/en-us/library/ms998258.aspx#pagguidelines0001_authorization (or "Security Guidelines: ASP.NET 2.0"). In the middle of the page under "When Constructing SQL Queries, Use Type Safe SQL Parameters" it says "Use type safe parameters when constructing SQL queries to avoid possible SQL injection attacks that can occur with unfiltered input".

Now, what they suggested was to use code like:

"DataSet userDataset = new DataSet();
SqlDataAdapter myCommand = new SqlDataAdapter(LoginStoredProcedure", connection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
myCommand.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11);........"

But, I was already using code like:

"var dataSource = (SqlDataSource)form1.FindControl("sqlDataSource5") ;
dataSource.UpdateParameters.Add("someVal", val);"

So now, to use type safe parameters, I decided to include it like:

"var dataSource = (SqlDataSource)form1.FindControl("sqlDataSource5") ;
dataSource.UpdateParameters.Add("@someVal", DbType.Int16, val);
dataSource.UpdateParameters["@someVal"].Size = 1;"

So, that would be how I would modify my current code base to use type safe parameters in sql updating/inserting.

Getting to my actual question, as it was said "Use type safe parameters when constructing SQL queries to avoid possible SQL injection attacks that can occur with unfiltered input". First off, this suggests that this should apply to unfiltered input. Also, in their example they only did this for an ID.

So, what I'd like to know, when it comes to "unfiltered input", does this mean as long as the input is unfiltered I must use type safe parameters, or even filtered input shall have this (just to be sure), like, input that has been ran through a regularexpression check? Shall I do this for all values I insert/update into the database, or just IDs and important things?

The way I see it right now is that it would be a good precaution to just do type safe checks on everything (literally) that updates/inserts into the database just to be extra safe. But, I really am unsure if this is really the best idea, because if I did, would this possibly cause overprocessing of information? Can this cause too much strain on server resources? If my fears serve true, what would be a good suggestion of how I could implement this properly without having to worry about what I said?

Thanks for any help.

[Frinavale]

Always validate user-provided data before you try to update your database with it.

Type safe parameters help to prevent SQL injection from occurring but no where would Microsoft state that it should be used instead of validation. It should be used with validation.

A SQL Injection attack occurs when the user enters SQL instead of the intended input. It occurs if you create your SQL query by simply concatenating the unvalidated/unfiltered user input into a String that contains your SQL query.

For example:

String mySqlQuery = "SELECT * FROM AddressBook WHERE FirstName='" + txt_FirstName + "'";

Now, when you send this SQL query to the database, the database compiles it into a command. If the user input contained SQL, their SQL will also be compiled...thus allowing the user to run their own SQL queries on your database.

If you use Type Safe SQL Parameters then the input provided as the parameter is treated as a Literal as apposed to part of the SQL query. (Check out SQL Injection Attack)

You should always validate the user's input before you update your database with the data they provide. SQL Parameters just add a bit more security to the process.



-Frinny