SqlCommand Parameter And Sql injection

Sql injection can be prevented using sqlCommand parameter, and i will show you how can we use sqlCommand paramenter to avoid the sql injection.When i think of sql injection first question that came to my mind is what is sql injection. Definition of sql injection is (Wikipedia)

A SQL injection attack consists of insertion or "injection" of a SQL query via the input data from the client to the application.

A successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file present on the DBMS file system and in some cases issue commands to the operating system. SQL injection attacks are a type of injection attack, in which SQL commands are injected into data-plane input in order to effect the execution of predefined SQL commands.
So in order to avoid such attack from hackers we need to check for invalid input from the user. Some programmer who are really good convert single quote to double quote, by writing the function, Some programmer not allowed user to enter such characters in input fields and so on and on.But the way today i will discuss is using sqlCommand parameter to avoid such attacks. Input used for this test application are as fellow.

User Name: asimsajjad';Drop Table tblUser;'

Password: 123

I have used Same database of northwind and same tblUser which i have show in my sqlCommand parameter post.First i have tested for the sqlCommand parameter so that i don't need to create tblUser table again. Hope you will create the HTML for your page as i will not write HTML code of the my test here. The .cs code which i have write on the cmdSave_Click event handler is pasted here, you can copy paste the code and change the UID and pwd with the sql server user name and password.

protected void cmdSave_Click(object sender, EventArgs e)

{

try

{

SqlConnection sqlConnection = new SqlConnection("Data Source=localhost; UID=UserName; PWD=Password; Initial Catalog=northwind;");

sqlConnection.Open();

string strQuery = "Select userID from tblUser where UserLoginName=@UserLoginName";

SqlCommand sqlCommand = new SqlCommand (strQuery, sqlConnection);

sqlCommand.Parameters.AddWithValue("@UserLoginName", txtLoginName.Text);

int intUserID=Convert .ToInt32(sqlCommand.ExecuteScalar());
}

catch (Exception Ex)

{
//handle the exception here;

}

}

The above code is simple one. i have declared and initialize the sqlConnection object and pass the connection string to the sqlConnection constructor. After initializing the sqlConnection object i have open the connection object. Then i have declared the string object to hold the sqlCommand. Next i have declared the sqlCommand object and initialized the sqlCommand with query and sqlCommand object. In the next line i have added the the value of the parameter which i have used in the query. With the above input the value is saved in the tblUser table and there is no error and no sql injection as , i have inserted Drop Table tblUser; statement in the user name field. But using sqlCommand parameter the table is not dropped.
But when i use the code below with same parameters as above then the tblUser table is drop from the database. Which confirms that dynamic query can't stop the sql injection.

protected void cmdSave_Click(object sender, EventArgs e)

{

try

{

SqlConnection sqlConnection = new SqlConnection("Data Source=localhost; UID=UserName; PWD=Password; Initial Catalog=northwind;");

sqlConnection.Open();

string strQuery = string.Concat("Select userID from tblUser where UserLoginName='", txtLoginName.Text,"'");

SqlCommand sqlCommand = new SqlCommand (strQuery, sqlConnection);
int intUserID=Convert .ToInt32(sqlCommand.ExecuteScalar());
}

catch (Exception Ex)

{
//handle the exception here;

}

}

When i posted my first post on sqlCommand parameter one of my reader ask question "does using parameters in sql command prevent sql injection?" , I also mention is that post the using sqlCommand parameter we can prevent the sql Injection, So from my above test i can answer you confidently yes.
The Guidlines of avoiding the such attacks are (How To: Protect From SQL Injection in ASP.NET)

• Constrain and sanitize input data. Check for known good data by validating for type, length, format, and range.
• Use type-safe SQL parameters for data access. You can use these parameters with stored procedures or dynamically constructed SQL command strings. Parameter collections such as SqlParameterCollection provide type checking and length validation. If you use a parameters collection, input is treated as a literal value, and SQL Server does not treat it as executable code. An additional benefit of using a parameters collection is that you can enforce type and length checks. Values outside of the range trigger an exception. This is a good example of defense in depth.
• Use an account that has restricted permissions in the database. Ideally, you should only grant execute permissions to selected stored procedures in the database and provide no direct table access.
• Avoid disclosing database error information. In the event of database errors, make sure you do not disclose detailed error messages to the user.

Some of the benefit other then i have mention in sqlCommand parameter are
1) It is faster when executing parametrized command over query string..("MSDN Forum")
2) The second-order attack would not have been possible if parameterised queries had been used.(Code Project). Second-order attack is "it may be possible for an attacker to inject their malicious code into a data storage area that may be executed at a later date or time. Depending upon the nature of the application and the way the malicious data is stored or rendered, the attacker may be able to conduct a second-order code injection attack. " (Source)

Hope this will help.