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

 

Consider the following action method that validates 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 attacks. 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 at least one row in the table thereby displaying the "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 queries will prevent such injection. Using parameterized queries is a three-step process:

1. Construct the SqlCommand command string with parameters.

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

3. Assign the SqlParameter object to the SqlCommand object's Parameters 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");

    }

}