In this blog, we are going to see, how to prevent the sql injection in CSharp.

SQL.jpg

The hacker may able to login in the particular database tables, without providing correct userid and password.

To avoid this, as a developer we need to execute this statement via using parameterised stored procedure.

1. Create parameterised user store procedure in the SQL Server.

 create procedure usp_RetrieveUserDetails
@UserId int,
@Password varchar(20)
as
begin
select * from Users
where User_ID=@UserID and [Password]=@Password
end

2. Call this store procedure in the particular event and add the parameter in command object

SqlConnection con = null;

SqlDataReader rd = null;

try

{

// Create and Open the SQL server connection object

con = new SqlConnection("Database Connection string");

con.Open();

// Create a command object and specify the Stored Procedure name and connection as well

SqlCommand cmd = new SqlCommand("usp_RetrieveUserDetails", con);

// Set the command object

cmd.CommandType = CommandType.StoredProcedure;

// Add parameter and value

cmd.Parameters.Add(new SqlParameter("@UserID", SaiDarshan));

cmd.Parameters.Add(new SqlParameter("@Password", Balaji123));

// Execute the command

rd = cmd.ExecuteReader();

rd.Read()

if(rd.HasRows())

{

Response.Write(rd["Name"].ToString(),rd["Age"].ToString(), rd["Designation"].ToString());

}

}

catch (Exception e)

{

Response.Write(e);

}

finally

{

if (con != null)

{

con.Close();

}

if (rd != null)

{

rd.Close();

}

}