How to prevent sql injection using store procedure in ASP.Net c#?

In this article we will discuss, How to prevent sql injection using parameterized query in ASP.Net c#. Write a store procedure, that returns the list of products. That store procedure takes input parameter as @name.

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

Store procedure:

CREATE PROCEDUREusp_SearchEmployee
@Namenvarchar(50)
AS
BEGIN

 SELECT * from Employee
 WHERE Name like '%'+ @Name +'%'

END

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 thecommand object, so the command object knows on which
        // connection to execute thecommand
        SqlCommand cmd = new SqlCommand("usp_SearchEmployee", connection);
       cmd.CommandType = CommandType.StoredProcedure;
        // Provide the value for theparameter
       cmd.Parameters.AddWithValue("@Name","%"+ txtName.Text + "%");
        // Open the connection. Otherwiseyou 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