What is SQL Injection attack with example and measures to prevent it in ASP.Net

Consider this example

HTML

Customer Id: <asp:TextBox ID="txtCustomerId" runat="server" />
<asp:Button Text="Submit" runat="server" OnClick="Submit" />
<hr />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="CustomerId" HeaderText="Customer Id" />
        <asp:BoundField DataField="ContactName" HeaderText="Contact Name" />
    </Columns>
</asp:GridView>

Namespaces

using System.Configuration;
using System.Data.SqlClient;

Code

In the code you will see I am passing value of TextBox in Where condition

protected void Submit(object sender, EventArgs e)
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers WHERE CustomerId = '" + txtCustomerId.Text + "'"))
    {
        using (SqlConnection con = new SqlConnection(conString))
        {
            con.Open();
            cmd.Connection = con;
            GridView1.DataSource = cmd.ExecuteReader();
            GridView1.DataBind();
            con.Close();
        }
    }
}

Database

I am making use of Northwind Database

Consider the following cases

Case 1: Valid CustomerId

It works as intended.

Case 2: Hack to get all records from table

If I add the 'OR 1 = 1;-- to the TextBox then it will list all records

The reason this happens is because here we are passing values using concatenation and which results in following query

SELECT * FROM Customers WHERE CustomerId = '' OR 1 = 1;--'

Thus here OR 1 = 1 becomes TRUE and hence all records are listed.

Similar way

1. One can get details of some other table

SELECT * FROM Customers WHERE CustomerId = '';SELECT * FROM Employees;--'

2. One can Drop a table

SELECT * FROM Customers WHERE CustomerId = '';DROP TABLE Persons;--'

Thus the solution to this is using Parameterized queries and which is well explained here

Using Parameterized queries to prevent sql injection Attacks in SQL Server