SQL Injection prevention in Ado.net application
SQL Injection
SQL Injection refers to the malicious code that can cause permanent loss to the database and even result in the loss of the database. Injection refers to the insertion of SQL queries through input data from the client into the application. Attackers can use SQL injection to add, modify, or delete records in the database. SQL injection can also be thought of as a web-based attack to take over the database.
For example, if a web application takes input from the user and stores it into the database or conversely, we can say that the function of the web application is to retrieve data from the database and display it in the web application. To perform these functions the web application generates a SQL query that executes on the database and finally, the database sends the relevant results to the application. In the case of a SQL injection, we manipulate the SQL queries using the malicious code resulting in the output that was actually not desired by the application and even leading to the loss of the information.
To understand SQL injection, we develop an application for storing the data of employers using Ado.net which takes inputs from the user as id, name, salary, and department id. To develop this application we design the front end as follows
We have to add the connection string in the app.config file under the project. We add a new class named ‘SQL helper’ to add various functionalities. The code for this class is given below:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace Ado.net_project
{
class sqlhelper
{
public string Connectionstring
{ get
{
return ConfigurationManager.ConnectionStrings["db"].ConnectionString;
}
}
public DataTable Getrecords(string query)
{
SqlDataAdapter adp = new SqlDataAdapter(query, Connectionstring);
DataTable dt = new DataTable();
adp.Fill(dt);
return dt;
}
public int Rundml(string command)
{
SqlConnection con = new SqlConnection(Connectionstring);
SqlCommand cmd = new SqlCommand(command, con);
con.Open();
int res = cmd.ExecuteNonQuery();
con.Close();
return res;
}
}
}To provide the functionality to the button we write the following code;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace Ado.net_project
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
sqlhelper sql = new sqlhelper();
private void Form1_Load(object sender, EventArgs e)
{
DataTable d=sql.Getrecords("Select * from Imagelocker");
dataGridView1.DataSource = d;
}
private void button1_Click(object sender, EventArgs e)
{
string cmd=$"Insert into Empl values({textBox1.Text},'{textBox2.Text}',{textBox3.Text},{textBox4.Text})";
int res = sql.Rundml(cmd);
if(res > 0)
{
MessageBox.Show("Data Saved.");
}
else
{
MessageBox.Show("Error");
}
}
}
}When we take the input from the user directly into the textbox and use that textbox data in code, it opens doors to the SQL Injection attack that can cause damage to the data. For example, if a hacker tries to input the data in the above application and types the below input then it will lead to the permanent loss of data from the database.
In the above inputs, after adding the department id the hacker closed the braces and terminated the statement with a semicolon. The semicolon was added to terminate the insert statement. After that, a delete statement to delete the table is added and finally, he uses ‘-‘ to comment whatever present in the command inside the code. Now if the above instructions get executed it can lead to the deletion of the table permanently from the database. Therefore, we can say that the hacker just did a SQL injection.
SQL Injection Prevention
SQL Injection Prevention refers to the techniques and methods using which we can prevent the SQL injection attack. We can avoid the injection of malicious code by using the following methods:-
- Parameterized statements - Parameterized statements means using parameters to pass values. It makes sure the inputs are passed to the SQL statements in a safer manner. If we consider the above application, an important point to note is that the SQL injection occurs because of the way we coded the application. Instead of directly passing values from the textboxes to the code, if we would have added parameters the situation would be different.
- Stored Procedures – Using stored procedures in applications comes as a preventive measure against SQL injection. As stored procedures include the involvement of parameters it acts as a savior. The stored procedures could be provided with any number of input parameters and output parameters. Output parameters are used in case the values are to be returned.
If we try to manipulate the code of the previously mentioned application, we can use parameterized statements instead of dynamically passing values to the applications. The code for the ‘SQL helper’ class can be modified as follows:-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace Ado.net_project
{
class sqlhelper
{
public string Connectionstring
{ get
{
return ConfigurationManager.ConnectionStrings["db"].ConnectionString;
}
}
public DataTable Getrecords(string query)
{
SqlDataAdapter adp = new SqlDataAdapter(query, Connectionstring);
DataTable dt = new DataTable();
adp.Fill(dt);
return dt;
}
public int Saveemploye(int Id,string Name,int Salary,int DeptId)
{
var Dmlquery = "Insert into Empl values(@id,@name,@salary,@deptid)";
cmd.CommandText = Dmlquery;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@id", Id);
cmd.Parameters.AddWithValue("@name", Name);
cmd.Parameters.AddWithValue("@salary", Salary);
cmd.Parameters.AddWithValue("@deptid", DeptId);
con.Open();
int count = cmd.ExecuteNonQuery();
con.Close();
return count;
}
}
}In the above code, we constructed a function name ‘Saveemploye’ which takes the four inputs as parameters. In order to add the parameters we use the ‘Parameters’ property of the SqlCommand class and then use the AddWithValue() method. This method takes the parameter name and parameter value as a parameter.
The code for the Form1 class is modified as given below:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace TestAdo2
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
sqlhelper sql = new sqlhelper();
private void button1_Click(object sender, EventArgs e)
{ if (sql.Saveemploye(Convert.ToInt32(txtid.Text),nametxt.Text, Convert.ToInt32(salarytxt.Text), Convert.ToInt32(deptidtxt.Text)) > 0)
{
MessageBox.Show("Data saved ");
Clearform();
}
else
{
MessageBox.Show("Server error");
}
}
public void Clearform()
{
txtid.Clear();
nametxt.Clear();
salarytxt.Clear();
deptidtxt.Clear();
txtid.Focus();
}
private void Form1_Load(object sender, EventArgs e)
{
string command = "Select * from Empl";
dd=sql.Getrecords(command);
dataGridView1.DataSource = dd;
}
}
}In the above code, we used parameterized statements to pass the inputs instead of using an Adhoc query which may lead to a SQL injection attack.
OUTPUT:-
