Prevent SQL injection in C#

SQL injection is one of the popular hacking technique in any programming language. And any application may affect by SQLInjection if there is no prevention for that. In each and every language there is different solution to prevent that. Here we will see in C# how can we prevent SQLInjection .

Lets see one vulnerable script. And if you give sample input as Sourav’s ,100 in this application it will show error.

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
namespace Test1
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=SOURAV-PC\\SQL_INSTANCE2;Initial
		Catalog=test;Integrated Security=True";
            con.Open();
 
            String name =Console.ReadLine(); 
            Int32 sal = Convert.ToInt32(Console.ReadLine());
 
            SqlCommand cmd = new SqlCommand("insert into test(name,sal) values('" + name + "', " + 		    sal + ")" ,con);
            cmd.ExecuteNonQuery();
 
            Console.ReadLine();
        }
    }
}
 

Where is actual problem in this code? problem is in below line

SqlCommand cmd = new SqlCommand("insert into test(name,sal) values('" + name + "', " + sal + ")" ,con);

Yes, We are preparing embedded query using input string directly. And when its getting some special character like ‘ or – its throwing error.  And in below code we have solution for same. We have use parameterized query to solve this issue. 

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
namespace Test1
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=SOURAV-PC\\SQL_INSTANCE2;Initial
		Catalog=test;Integrated Security=True";
            con.Open();
 
            String name =Console.ReadLine(); 
            Int32 sal = Convert.ToInt32(Console.ReadLine());
             SqlCommand cmd = new SqlCommand("insert into test(name,sal) values(@name,@sal)"
		,con);
            cmd.Parameters.Add(new SqlParameter(@name, name));
            SqlParameter p = new SqlParameter();
            p.ParameterName = "sal";
            p.DbType = System.Data.DbType.Int32;
            p.Value = sal;
             cmd.Parameters.Add(p);
            cmd.ExecuteNonQuery();
            Console.ReadLine();
        }
    }
}
 

It has solved the problem and data is inserted in database.

Conclusion :

This is the process to solve SQLInjection problem in C#.