Prevent Sql Injection

What is Sql Injection
Take the page log in as an example:

protected void Button1_Click(object sender, EventArgs e)
{
string cmdstr="Select Count(*) From Users Where Username = '" + UserName.Text + "' And Password = '" + Password.Text + "'";
int result = 0;
using (var conn = new SqlConnection(connect))
{
using (var cmd = new SqlCommand(cmdstr, conn))
{
conn.Open();
result = (int)cmd.ExecuteScalar();
}
}
if (result > 0)
{
Response.Redirect("LoggedIn.aspx");
}
else
{
Label1.Text = "Invalid credentials";
}
}

This kind of code is easy to be get sql injection to be hacked.

For example if a hacker input in the control Username text box the following text:

admin' --

Like the following:



In this case the cmdstr becomes like the following



The hacker bypass the password and use admin account. Here is a link on sql injection cheat sheet:

http://ferruh.mavituna.com/sql-injection-cheatsheet-oku/

In order to prevent Sql Injection, we have 3 method:
1. Use Parameterized Queries
2. Use Stored Procedure
3. Use Linq to Sql

Use Parameterized Queries:

The above code changed to be:

....
string query = "Select * From User Where UserName= @UserName and Password=@PassWord";
using (var conn = new SqlConnection(connect))
{
using (var cmd = new SqlCommand(query, conn))
{
cmd.Parameters.Add("@UserName", SqlDbType.VarChar);
cmd.Parameters["@UserName"].Value = UserName.Text.ToString();

cmd.Parameters.Add("@PassWord", SqlDbType.VarChar);
cmd.Parameters["@PassWord"].Value = PassWord.Text.ToString();
conn.Open()
...

For the Stored Procedure which as parameters, it the similar.

For the Linq to Sql, it need another topic.