Fixing SQL Injection in .NET and MS SQL

Note: This post is part of our series on “How to Fix SQL Injection Vulnerabilities“. The series contains examples on how to fix SQL Injection Vulnerabilities in various programming languages.

An SQL Injection attack is a code injection attack when input from an attacker reaches one of your databases without any filteration or validation. As a result, a malicious user can execute Read / Write / Delete / Update query in your database. In addition to this he can also run system level commands. Listed below are examples on how to prevent SQL Injection in C#, VB.NET & ASP.NET

Parameterized Query

C#

string queryText = "SELECT * FROM Students WHERE [email protected]";
SqlCommand cmd = new SqlCommand(queryText, conn);
cmd.Parameters.Add("@City",City);
SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();
da.Fill(ds);
return ds;

VB.NET

Dim queryText As String = "SELECT * FROM Students WHERE [email protected]"
Dim cmd As SqlCommand = New SqlCommand(queryText, conn)
cmd.Parameters.Add("@City",City)
Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)

GetCustomersFromCountry = New DataSet
da.Fill(GetCustomersFromCountry)

ASP.NET

/*Parameterized Query*/

string commandText = "SELECT * FROM Customers WHERE [email protected]";
SqlCommand cmd = new SqlCommand(commandText, conn);
cmd.Parameters.Add("@CountryName",countryName);

Stored Procedure

var connect = ConfigurationManager.ConnectionStrings["NorthWind"].ToString();
var query = "GetProductByID";

using (var conn = new SqlConnection(connect))
{
  using (var cmd = new SqlCommand(query, conn))
  {
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = Convert.ToInt32(Request["ProductID"]);
    conn.Open();
    //Process results
  }
}