SQL Injection and Prevention in C#
Posted by Superadmin on November 15 2023 11:34:58

SQL Injection and Prevention in C#

In this article, I am going to discuss SQL Injection and Prevention in C# using ADO.NET with Examples. Please read our previous article where we discussed How to Load XML Data to a Data Table using ADO.NET with Examples.

 

SQL Injection Attack in C#:

According to MSDN, SQL Injection is an attack in which malicious code is inserted into strings that are later passed to SQL Server Database for execution. Any procedure or mechanism that constructs SQL statements should be reviewed for injection vulnerabilities. This is because SQL Server will execute all syntactically valid queries that it receives. Even parameterized data can be manipulated by a skilled and determined attacker.

 

It is the application layer attack which means a front-end attack that takes benefit of inappropriate coding of our applications that allows hackers to insert SQL commands into our application code that is SQL statements. So, as a developer or programmers, we should check and prevent SQL Injection in our application code.

  

Points to Remember:
  1. SQL Injection is a SQL Code Injection mechanism that might destroy your database.
  2. SQL Injection is one of the most common web hacking techniques.
  3. SQL Injection is the process of Injecting malicious code in SQL statements, via web page input.

Note: First, we will see an example of SQL Injection, and then we will see how we can prevent SQL Injection in C#.

How does SQL Injection work in C#?

The Primary form of SQL injection consists of the direct insertion of SQL Statements into user-input variables that are used to prepare the SQL commands dynamically and executed them on the database.

   

SQL Injection in C# usually occurs when we ask a user for input, like their username/user id, or ask to input some information, along with the name/id, or some information, or instead of name/id, or some information, the user entered an SQL statement and that SQL Statement run on your database.

The following C# code shows a simple SQL Injection Attack. The following C# code builds an SQL query by concatenating hard-coded strings together with a string value entered by the user.
string ProductName = Console.ReadLine();
var SQLQuerey = “SELECT * FROM Products WHERE NAME LIKE ‘” + ProductName + “%'”;

 

Here, we are asking the user to enter the name of the product to search in the database. If the user entered Mobile, then the SQL query generated by the C# code is as follows:
SELECT * FROM Products WHERE NAME LIKE ‘Mobile%’

 

Now, assume that the user enters the following string instead of Mobile.
Mobile’; DELETE FROM Products —

In this case, the following SQL query is going to be generated by the C# code.
SELECT * FROM Products WHERE NAME LIKE ‘Mobile’; DELETE FROM Products –%’

 

The point that you need to remember while working with SQL Server, the semicolon (;) in the SQL Queries denotes the end of one query and the start of another SQL query. The double hyphen (–) in SQL Statement in SQL Server Database indicates that the rest of the current line is a comment and should be ignored while executing the SQL Statement. If the modified code is syntactically correct, then it is going to be executed by the server.

 

In the above example, the SQL Server will execute two SQL Statements. They are as follows:

  1. 1st SQL Statement: SELECT * FROM Products WHERE NAME LIKE ‘Mobile’;
  2. 2nd SQL Statement: DELETE FROM Products

So, in this case, first, it will select all the records from the Product table where the Product Name starts with the word Mobile and then it is going to delete all the records from the Product Table. This is called SQL Injection.

  

Note: If the Injected SQL Queries are syntactically correct, they are going to be executed by the databases. Therefore, as a programmer and as a good programming practice we must validate all user input and carefully review code that prepares SQL Statements dynamically.

 

Example to Understand SQL Injection in C#:

We are going to use the following Products table to understand SQL Injection in C# using ADO.NET.

Example to Understand SQL Injection in C#

Please use the below SQL Script to create the database and populate the Products table with the required sample data.

   

CREATE DATABASE ProductDB
 GO 
 
 USE ProductDB
 GO 
 
 CREATE TABLE Products
(
ID INT PRIMARY KEY,
Name VARCHAR(50),
Price INT,
QuantityAvailable INT 
)
 GO 
 
 INSERT INTO Products Values(1001, 'Mobile-Redmi', 1000, 10)
 INSERT INTO Products Values(1002, 'Mobile-Samsung', 2000, 20)
 INSERT INTO Products Values(1003, 'Mobile-Apple', 5000, 15)
 INSERT INTO Products Values(1004, 'Laptop-Sony', 10000, 20)
 INSERT INTO Products Values(1005, 'Laptop-HP', 20000, 30)
 INSERT INTO Products Values(1006, 'Laptop-Apple', 50000, 45)

Now, create a console application and then copy and paste the following code into it. In the below example, we are creating the SQL Query dynamically by concatenating the hard-coded string with the string value input by the user from the console. This is very dangerous, as it is vulnerable to SQL injection attacks.

using System; 
 using System.Data; 
 using System.Data.SqlClient; 
 
namespace DataViewClassDemo
{
class Program
{
static void Main(string[] args)
{
//Store the connection string in a variable
string ConnectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=ProductDB; integrated security=SSPI";
Console.WriteLine("Enter the Search String: Mobile or Laptop");
string SearchString = Console.ReadLine();
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
//Prepare the Dynamic SQL Query with the input taken from the user
//This is a bad way of constructing queries.
//The following line of code will open doors for SQL Injection Attack
var SQLQuerey = "SELECT * FROM Products WHERE NAME LIKE '" + SearchString + "%'";
SqlCommand command = connection.CreateCommand();
command.CommandText = SQLQuerey;
command.Connection = connection;
connection.Open();
SqlDataReader rdr = command.ExecuteReader();
while(rdr.Read())
{
Console.WriteLine($"ID: {rdr["ID"]}, Name: {rdr["Name"]}, Price: {rdr["Price"]}, Quantity: {rdr["QuantityAvailable"]}");
}
}
Console.ReadKey();
}
}
}

Now, run the above code and entered the search string as Mobile and you will get the result as expected as shown in the below image.

How does SQL Injection work in C#?

Now, again run the above application and this time entered the search string as Mobile’; DELETE FROM Products — as shown in the below image.

 

SQL Injection and Prevention in C# using ADO.NET with Examples

Now, you will not get the result. To verify why, please check the Products database table and you will see that there is no record. That the DELETE query that we input is executed in the database and deletes all the records from the Products table. This is an SQL Injection attack. As a user, we are injecting SQL Statements and that gets executed in the database.

I have seen a lot of new developers building queries dynamically by concatenating the hardcoded strings with user-entered input values. Just imagine the damage that can happen as a result of SQL Injection. However, SQL Injection can be easily avoided, by using parameterized queries or stored procedures. Let us see both approaches.

How to Prevent SQL Injection in C# using ADO.NET?

We can prevent SQL Injection using parameterized queries or by using stored procedures. To prevent SQL Injection Attacks in C#, we need to follow the below points.

  1. In C#, while preparing a SQL Statement, please use SqlParameter to define the Parameter Name, type, and value instead of making a straight SQL command as we did in our previous example.
  2. While executing the query, please specify the CommandType property value either as Text or Stored Procedure.
  3. If you are using Parameters Collection, then please mention the type and size of the parameters.
  4. Another way to avoid SQL injection attacks is to filter the user input for SQL characters. Please check if the user input contains some special characters like %,–, ; and if contains please take the necessary action.

Before proceeding further and seeing how we can prevent the SQL Injection, let us first insert records into the Products table by executing the below INSERT SQL Statements as in the previous example we have deleted all the records from the Product table using SQL Injection Attack.

 

INSERT INTO Products Values(1001, 'Mobile-Redmi', 1000, 10)
INSERT INTO Products Values(1002, 'Mobile-Samsung', 2000, 20)
INSERT INTO Products Values(1003, 'Mobile-Apple', 5000, 15)
INSERT INTO Products Values(1004, 'Laptop-Sony', 10000, 20)
INSERT INTO Products Values(1005, 'Laptop-HP', 20000, 30)
INSERT INTO Products Values(1006, 'Laptop-Apple', 50000, 45)
Preventing SQL Injection using Parameterized Queries in C#:

In the following example, we are Preventing SQL Injection Attacks using Parameterized Queries. Now, you can see that the query uses the parameter – @Name. The value for this query parameter is then provided by using the AddWithValue() method. The parameter is associated with the command object using the Parameters collection property.

using System;
using System.Data;
using System.Data.SqlClient;
namespace DataViewClassDemo
{
class Program
{
static void Main(string[] args)
{
//Store the connection string in a variable
string ConnectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=ProductDB; integrated security=SSPI";
Console.WriteLine("Enter the Search String: Mobile or Laptop");
string SearchString = Console.ReadLine();
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
string SQLQuerey = "SELECT * FROM Products WHERE NAME LIKE @Name";
SqlCommand command = new SqlCommand(SQLQuerey, connection);
// Provide the value for the Querey Parameter @Name
command.Parameters.AddWithValue("@Name", SearchString + "%");
command.CommandType = CommandType.Text;
connection.Open();
SqlDataReader rdr = command.ExecuteReader();
while(rdr.Read())
{
Console.WriteLine($"ID: {rdr["ID"]}, Name: {rdr["Name"]}, Price: {rdr["Price"]}, Quantity: {rdr["QuantityAvailable"]}");
}
}
Console.ReadKey();
}
}
}

Now, run the above code and entered the search string as Mobile and you will get the result as expected as shown in the below image.

Preventing SQL Injection using Parameterized Queries in C#

Now, again run the above application and this time entered the search string as Mobile’; DELETE FROM Products — as shown in the below image.

Preventing SQL Injection using Parameterized Queries in C# ADO.NET

Now, the data is not going to be deleted from the database table and the reason for this is the below SQL Statement. As you can see whatever value we have entered that is going to use as the value for the Name parameter. As this value does not match in the Products table and hence you can see it is not returning any record. You can find the below SQL statement which is generated and run by the SQL Server by using the SQL Profiler tool.

 

exec sp_executesql N’SELECT * FROM Products WHERE NAME LIKE @Name’,N’@Name nvarchar(33)’,@Name=N’Mobile”; DELETE FROM Products –%’

Preventing SQL Injection using Stored Procedure in C#:

Let us see how we can prevent SQL Injection Attacks in ADO.NET using Stored Procedures. So, let us first create a stored procedure that returns the list of products by executing the below SQL Scripts. This stored procedure takes an input parameter @Name. And based on that input parameter it is going to return the results.

CREATE PROCEDURE spGetProductsByName
@Name VARCHAR(50)
AS
BEGIN
SELECT * FROM Products
WHERE Name like @Name + '%'
END

Now, let us rewrite the previous example to use the stored procedure as follows. The following example code is self-explained, so please go through the comment lines.

using System;
using System.Data;
using System.Data.SqlClient;
namespace DataViewClassDemo
{
class Program
{
static void Main(string[] args)
{
//Store the connection string in a variable
string ConnectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=ProductDB; integrated security=SSPI";
Console.WriteLine("Enter the Search String: Mobile or Laptop");
string SearchString = Console.ReadLine();
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
// The command, that we want to execute is a stored procedure,
// so specify the name of the procedure as cmdText
SqlCommand command = new SqlCommand("spGetProductsByName", connection);
// Provide the value for the Querey Parameter @Name
command.Parameters.AddWithValue("@Name", SearchString);
//Specify that the T-SQL command is a stored procedure
command.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlDataReader rdr = command.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine($"ID: {rdr["ID"]}, Name: {rdr["Name"]}, Price: {rdr["Price"]}, Quantity: {rdr["QuantityAvailable"]}");
}
}
Console.ReadKey();
}
}
}

Now, with the above changes run the application and it should work as expected by Preventing SQL Injection Attacks. So, these are the two ways by using which you can prevent the SQL Injection Attack in ADO.NET C#.

Note: So, the conclusion is that always used parameterized queries or stored procedures when interacting with the database to perform CRUD Operations, to avoid SQL Injection Attacks.

In the next article, I am going to discuss SqlCommandBuilder in C# with Examples. Here, in this article, I try to explain SQL Injection and Prevention in C# with Examples. I hope you enjoy this SQL Injection and Prevention in ADO.NET using C# Language with Examples article.