NET Centric Computing - Old Questions

4. Write an application showing sql injection vulnerability and prevention using ado.net.

Consider the following action method that validate user login.

[HttpPost]

public IActionResult SubmitLogin1(String uname, String pwd)

{

    SqlConnection con = new SqlConnection(@"Data Source=.\\SQLEXPRESS;Initial Catalog=db_Mac1; Integrated Security=True");

    con.Open();

    SqlCommand cmd = new SqlCommand("select * from tbl_login where uname=' "+uname+" ' and password=' "+pwd+" ' ", con);

    SqlDataReader dr = cmd.ExecuteReader();

    if (dr.Read())

    {

        return Content ("Login Successful");

    }

    else

    {

        return Content("Login Unsuccessful");

    }

}

The above action method is vulnerable to SQL injection attack. It is because we've used the form input values name and pwd with no data validation at all including Empty form validations. Nothing bad will happen if we're sure that this value will only come from trusted sources, but this is not always. If the attacker doesn't know what the username is then he/she simply provides a ' ' or 1=1 for the username. So, when the user presses the submit button the resulting query will be formed as

   select * from tbl_login where Username = ' ' or 1=1-- ' and Password = ' '

The above query will return entire rows from table tbl_login if there is atleast one row in the table thereby displays "Login Successful" message. Anything placed into that TextBox control will be added to your SQL string. This situation invites a hacker to replace that string with something malicious.

Preventing SQL Injection:

Using parameterized query will prevent such injection. Using parameterized queries is a three-step process:

1. Construct the SqlCommand command string with paramters.

2. Declare a SqlParameter object, assigning values as appropriate.

3. Assign the SqlParameter object to the SqlCommand object's Paramteres property.

Program:

[HttpPost]

public IActionResult SubmitLogin1(String uname, String pwd)

{

    SqlConnection con = new SqlConnection(@"Data Source=.\\SQLEXPRESS;Initial Catalog=db_Mac1; Integrated Security=True");

    con.Open();

    SqlCommand cmd = new SqlCommand("select * from tbl_login where uname = @uname and password = @pwd ", con);

    cmd.Parameters.AddWithValue("@uname", uname);

    cmd.Paramterers.AddWithValue("@pwd", pwd);

    SqlDataReader dr = cmd.ExecuteReader();

    if (dr.Read())

    {

        return Content ("Login Successful");

    }

    else

    {

        return Content("Login Unsuccessful");

    }

}