How to prevent sql injection using parameterized query in ASP.Net c#?

In this article we will discuss, How to prevent sql injection using parameterized query in ASP.Net c#. In order to prevent sql injection attack, query that uses parameter @name. The value for the parameter is provided using AddwithValue() method.

We will be using employee table

Step 1: Create a table using the following script with data:

CREATE TABLE [dbo].[Employee](
      [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
      [Name] [nvarchar](50) NULL,
      [Gender] [nvarchar](50) NULL,
      [City] [nvarchar](50) NULL,
 CONSTRAINT [PK_tbl_Employee] PRIMARY KEY CLUSTERED 
(
      [EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Employee] ON
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (1, N'Thivan', N'male', N'tirunelveli')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (2, N'Rasik', N'male', N'Tuticorin')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (3, N'Usman', N'male', N'tirunelveli')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (4, N'karishma', N'female', N'mumbai')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (5, N'chaitrali', N'female ', N'mumbai')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (6, N'mansoor', N'male', N'gujarat')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (7, N'mydeen', N'male', N'chennai')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (8, N'zubair', N'male', N'melapalayam')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (9, N'matkar', N'male', N'mumbai')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (10, N'Rahim', N'male', N'mumbai')
SET IDENTITY_INSERT [dbo].[Employee] OFF 

Step 2: Copy and paste the following code.

Default.aspx:

<table style="border: 1px solid #e2e2e2; font-family: Arial">
        <tr>
            <td>
               <asp:TextBox ID="txtName" runat="server"></asp:TextBox>
                <asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="btnSearch_Click" />
            </td>
        </tr>
        <tr>
            <td colspan="2">
                <asp:GridView ID="GridView1" runat="server"></asp:GridView>
            </td>
        </tr>
    </table>

Default.aspx.cs:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.DataVisualization.Charting;
using System.Web.UI.WebControls;

public partial class _Default : Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindData();
        }
    }
    private void BindData()
    {
        //Create the connection object
        SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ShoppingZone"].ConnectionString); ;
        // Pass the connection to the command object, so the command object knows on which
        // connection to execute the command
        string str = "Select * from employee where Name like @Name";
        SqlCommand cmd = new SqlCommand(str, connection);
        // Provide the value for the parameter
        cmd.Parameters.AddWithValue("@Name","%"+ txtName.Text + "%");
        // Open the connection. Otherwise you get a runtime error. An open connection is
        // required to execute the command
        connection.Open();
        GridView1.DataSource = cmd.ExecuteReader();
        GridView1.DataBind();
        connection.Close();
    }

   protected void btnSearch_Click(object sender, EventArgs e)
    {
        BindData();
    }
}

Output:

If we input sql injection string, it won’t affect the table.

 t'; Delete from Employee