ASP. NET 4 learning notes (1) SQL injection attacks and solutions.

I. Definition: the so-called SQL Injection Attack is the process that application developers did not expect to pass SQL code into the application, only applications that directly construct SQL statements using user-supplied values will be affected.

For example, the original SQL code is:

Select orders. customerid, orders. orderid, count (unitprice) as items, sum (unitprice * quantity) as total from orders inner join [Order Details] on orders. orderid = [Order Details]. orderid where orders. customerid = '"+ txtid. text + "'group by orders. orderid, orders. customerid

If. in the text box where text is located, input string: alfki 'or '1' = '1 will return all order records, even if those orders are not created by alfki, because for each line, 1 = 1 is always true.

Solution: Use parameterized commands:

For example, the preceding code is rewritten using parameterized commands:

 protected void btnQuery_Click(object sender, EventArgs e)
        {
string conStr = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            con.Open();
string strSql = "select Orders.CustomerID,Orders.OrderID,Count(UnitPrice) as Items,SUM(UnitPrice*Quantity) as Total from Orders INNER JOIN [Order Details]on Orders.OrderID=[Order Details].OrderID where Orders.CustomerID=@CustomerID GROUP BY Orders.OrderID,Orders.CustomerID";
            SqlCommand cmd = new SqlCommand(strSql, con);
            cmd.Parameters.AddWithValue("@CustomerID", txtId.Text.Trim().ToString());
            SqlDataReader reader = cmd.ExecuteReader();
            GridView1.DataSource = reader;
            GridView1.DataBind();
            reader.Close();
            con.Close();
        }

This prevents SQL injection attacks.