SQL Injection and Prevention using ADO.NET Core

Back to: ADO.NET Core Tutorial For Beginners and Professionals

SQL Injection and Prevention using ADO.NET Core

In this article, I will discuss SQL Injection and Prevention using ADO.NET Core with Examples. Please read our previous article discussing Connected vs Disconnected Architecture in ADO.NET Core with Examples. SQL Injection (SQLi) remains one of the most common and damaging web‑application attacks. Although modern Object-Relational Mappers (ORMs) such as Entity Framework Core help mitigate the risk of SQL Injection attacks, pure ADO.NET Core code is still widely used for raw performance and therefore must be written defensively.

What is SQL Injection?

SQL Injection is a code injection technique where malicious users inject malicious SQL code into your application’s input fields, causing the application to execute unintended commands on the database. It typically happens when an application dynamically constructs SQL statements by directly including user input without proper validation or parameterization. As a result, attackers can insert malicious SQL code to access unauthorized data, modify database structures, destroy data, data corruption, or execute administrative operations on the database server.

How Does SQL Injection Work?

SQL Injection occurs when user inputs are directly concatenated into SQL queries without validation or sanitization. This enables the attacker to inject their own SQL statements into the query and manipulate the database in unintended ways. Classic examples include OR 1=1 to bypass authentication and ; DROP TABLE … to destroy data.

Example of an unsafe SQL query:  string sql = “SELECT * FROM Users WHERE Username = ‘” + username + “‘ AND Password = ‘” + password + “‘;”;

If an attacker supplies a username as admin’– and any password, the query becomes: SELECT * FROM Users WHERE Username = ‘admin’–‘ AND Password = ‘…’;

The — turns the rest of the line into a comment, bypassing password validation.

If an attacker supplies username as admin and Password = anything OR 1=1, The SQL becomes: SELECT * FROM Users WHERE Username = ‘admin’ AND Password = ‘anything’ OR 1=1;

Here, the injected OR 1=1 condition always evaluates to true. This could lead to data leaks, unauthorized access, or data modification.

Setting Up the SQL Server Database and Tables

Let’s first create a sample database and a table to understand SQL Injection. Please run the following SQL script to create a database named UsersDB and a Users table with some dummy data.

-- Create UsersDB database
CREATE DATABASE UsersDB;
GO

-- Switch to UsersDB database
USE UsersDB;
GO

-- Create the Users table
CREATE TABLE Users (
    UserId INT PRIMARY KEY IDENTITY,
    UserName NVARCHAR(50),
    Password NVARCHAR(50)
);

-- Inserting dummy data into Users table
INSERT INTO Users (UserName, Password)
VALUES 
('admin', 'password123'),
('user1', 'userpass1'),
('user2', 'userpass2');
GO

Example using ADO.NET Core with SQL Injection Vulnerability

Now, let’s see an example using ADO.NET Core in a .NET Core Console Application to understand SQL Injection. The following example builds the SQL command by concatenating user input, an invitation to attackers. The example code is self-explained, so please read the comment lines for a better understanding.

using Microsoft.Data.SqlClient;
namespace SqlInjectionDemo
{
    public class Program
    {
        static async Task Main()
        {
            // Define the connection string to your SQL Server OrdersDB database.
            string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=UsersDB;Trusted_Connection=True;TrustServerCertificate=True;";

            // Read user input
            Console.WriteLine("Enter username:");
            string? userName = Console.ReadLine();
            Console.WriteLine("Enter password:");
            string? password = Console.ReadLine();

            // SQL Injection Vulnerable Query
            string query = "SELECT UserId, Username, Password FROM Users WHERE UserName = '" + userName + "' AND Password = '" + password + "';";
            Console.WriteLine($"\nSQL Sent: {query}\n");
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    using (SqlCommand cmd = new SqlCommand(query, connection))
                    {

                        await using var rdr = await cmd.ExecuteReaderAsync(); // Injection happens *before* execution
                        if (await rdr.ReadAsync())
                        {
                            Console.ForegroundColor = ConsoleColor.Green;
                            Console.WriteLine($"Welcome, {rdr["UserName"]} (id = {rdr["UserId"]})!");
                        }
                        else
                        {
                            Console.ForegroundColor = ConsoleColor.Red;
                            Console.WriteLine("Invalid credentials");
                        }
                    }
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"Database error: {ex.Message}");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Unexpected error: {ex.Message}");
            }
        }
    }
}

Code Explanation:

In the above example, the SQL query is vulnerable to SQL Injection because user input is directly inserted into the query. Please enter username as admin’ OR 1=1; — and any password, The query becomes:

SELECT UserId, Username, Password FROM Users WHERE UserName = ‘admin’ OR 1=1; –‘ AND Password = ‘abc’;

Everything after the — is treated as a comment, so the AND clause is ignored; OR ‘1’=’1′ is always true → the attacker logs in as admin without password. For a better understanding, please look at the following output.

Why Did SQL Injection Succeed?

Because the application concatenated untrusted input into the SQL string before the server parsed it. The attacker closed the string literal (‘admin’) and injected a new Boolean expression (OR ‘1’=’1′), which the SQL parser happily accepted as part of the command structure. So, SQL Injection occurs because of the following reasons:

• String Concatenation: Directly embedding user input into SQL.
• No Input Validation or Sanitization: The application trusts raw input.
• Dynamic SQL Execution: No separation between code and data.

How to Prevent SQL Injection in ADO.NET Core?

There are several ways to prevent SQL Injection:

• Use Parameterized Queries: This is the most effective method. It ensures that user inputs are treated as data, not part of the SQL query.
• Use Stored Procedures: Stored procedures can also protect against SQL Injection when used correctly.
• Input Validation and Sanitization: Always validate and sanitize user inputs.

Prevent SQL Injection using Parameterized Queries in ADO.NET Core:

Parameterized queries use placeholders for parameters instead of concatenating user input directly into the query string. This approach ensures that user input is treated as data, not executable code. Now, let’s rewrite the previous example to prevent SQL Injection using parameterized queries.

The following example demonstrates the safe Implementation with Parameterized Queries. Here we replace string concatenation with parameters, so the DB treats user input purely as data. The example code is self-explained, so please read the comment lines for a better understanding.

using Microsoft.Data.SqlClient;
namespace SqlInjectionDemo
{
    public class Program
    {
        static async Task Main()
        {
            // Define the connection string to your SQL Server OrdersDB database.
            string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=UsersDB;Trusted_Connection=True;TrustServerCertificate=True;";

            // Read user input
            Console.WriteLine("Enter username:");
            string? userName = Console.ReadLine();
            Console.WriteLine("Enter password:");
            string? password = Console.ReadLine();

            // Secure SQL Query using Parameters to avoid SQL injection
            string query = "SELECT UserId, Username, Password FROM Users WHERE UserName = @UserName AND Password = @Password;";
            Console.WriteLine($"\nSQL Sent: {query}\n");
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    using (SqlCommand cmd = new SqlCommand(query, connection))
                    {
                        // Adding parameters securely
                        cmd.Parameters.AddWithValue("@UserName", userName);
                        cmd.Parameters.AddWithValue("@Password", password);

                        await using var rdr = await cmd.ExecuteReaderAsync(); 
                        if (await rdr.ReadAsync())
                        {
                            Console.ForegroundColor = ConsoleColor.Green;
                            Console.WriteLine($"Welcome, {rdr["UserName"]} (id = {rdr["UserId"]})!");
                        }
                        else
                        {
                            Console.ForegroundColor = ConsoleColor.Red;
                            Console.WriteLine("Invalid credentials");
                        }
                    }
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"Database error: {ex.Message}");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Unexpected error: {ex.Message}");
            }
        }
    }
}

Code Explanation

The @Username placeholder ensures that whatever the user types is sent separately to SQL Server as a literal value. Even if they enter admin’ OR 1=1; —, it will look for a username equal to that exact string, and won’t break the query logic. The parameters @UserName and @Password are added using the AddWithValue method. The user input is treated as data, and the query will no longer be vulnerable to SQL Injection. For a better understanding, please look at the following output.

Prevent SQL Injection using Stored Procedure in ADO.NET Core:

Stored procedures can further protect against SQL injection by predefining SQL logic on the database server side. Let us first create a stored procedure. So, please run the following SQL script to create the stored procedure in SQL Server.

-- Stored Procedure for Safe Querying 
-- This will Validate the user credentials and return the user
CREATE PROCEDURE GetUserInfo
    @UserName NVARCHAR(50),
    @Password NVARCHAR(50)
AS
BEGIN
 SELECT UserId, UserName, Password 
 FROM Users 
 WHERE UserName =@UserName AND Password = @Password;
END

Example using Stored Procedure to Prevent SQL Injection:

Let us rewrite the previous example using a stored procedure. The following example calls the GetUserInfo stored procedure, binding @UserName and @Password parameters. The example code is self-explained, so please read the comment lines for a better understanding.

using Microsoft.Data.SqlClient;
namespace SqlInjectionDemo
{
    public class Program
    {
        static async Task Main()
        {
            // Define the connection string to your SQL Server OrdersDB database.
            string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=UsersDB;Trusted_Connection=True;TrustServerCertificate=True;";

            // Read user input
            Console.WriteLine("Enter username:");
            string? userName = Console.ReadLine();
            Console.WriteLine("Enter password:");
            string? password = Console.ReadLine();

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    using (SqlCommand cmd = new SqlCommand("GetUserInfo", connection))
                    {
                        // Specify that this is a stored procedure
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;

                        // Adding parameters securely
                        cmd.Parameters.AddWithValue("@UserName", userName);
                        cmd.Parameters.AddWithValue("@Password", password);

                        await using var rdr = await cmd.ExecuteReaderAsync(); 
                        if (await rdr.ReadAsync())
                        {
                            Console.ForegroundColor = ConsoleColor.Green;
                            Console.WriteLine($"Welcome, {rdr["UserName"]} (id = {rdr["UserId"]})!");
                        }
                        else
                        {
                            Console.ForegroundColor = ConsoleColor.Red;
                            Console.WriteLine("Invalid credentials");
                        }
                    }
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"Database error: {ex.Message}");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Unexpected error: {ex.Message}");
            }
        }
    }
}

Code Explanation:

Using a stored procedure allows for better control over the query and ensures that inputs are treated as parameters. Stored procedures do not allow for dynamic SQL execution unless explicitly instructed, making them safe from SQL Injection.

The procedure’s query text is pre‑defined on the server. Parameters are passed in binary form. Even if an attacker manipulates @UserName, the payload is stored in a variable; it never changes the procedure’s execution plan. For a better understanding, please look at the following output. Enter username as admin’ OR 1=1; — and any password.

Note: A stored procedure can still be vulnerable if it constructs dynamic SQL internally with concatenation. Always parameterise inside the proc too.

Prevent SQL Injection with Input Validation in ADO.NET Core

Let’s add input validation to the previous example to further strengthen protection against SQL injection. Input validation ensures that the user inputs conform to expected formats before they are used in any SQL query or operation. This helps prevent malicious inputs from entering the system. We will validate the user inputs to ensure they are safe to use in the SQL query. For example, we could:

• Ensure the username and password contain only alphanumeric characters (no special characters that could be used in SQL Injection).
• Limit the length of the inputs to avoid overly long inputs that might contain malicious code.

Let us rewrite the previous example with Input Validation. The example code is self-explained, so please read the comment lines for a better understanding.

using Microsoft.Data.SqlClient;
using System.Text.RegularExpressions;

namespace SqlInjectionDemo
{
    public class Program
    {
        static async Task Main()
        {
            // Define the connection string to your SQL Server OrdersDB database.
            string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=UsersDB;Trusted_Connection=True;TrustServerCertificate=True;";

            // Read user input
            Console.WriteLine("Enter username:");
            string? userName = Console.ReadLine();
            Console.WriteLine("Enter password:");
            string? password = Console.ReadLine();

            if (userName == null || password == null) 
            {
                Console.WriteLine("username or password cannot be null");
                return;
            }

            // Input validation: Ensure the input contains only alphanumeric characters and has a length of at most 50
            if (!IsValidInput(userName) || !IsValidInput(password))
            {
                Console.WriteLine("Invalid input! Only alphanumeric characters are allowed, and length must be less than 50 characters.");
                return;
            }

            // SQL Query using Parameterized Queries to prevent SQL Injection
            string query = "SELECT * FROM Users WHERE UserName = @UserName AND Password = @Password";

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    using (SqlCommand cmd = new SqlCommand(query, connection))
                    {
                        // Adding parameters securely
                        cmd.Parameters.AddWithValue("@UserName", userName);
                        cmd.Parameters.AddWithValue("@Password", password);

                        await using var rdr = await cmd.ExecuteReaderAsync(); 
                        if (await rdr.ReadAsync())
                        {
                            Console.ForegroundColor = ConsoleColor.Green;
                            Console.WriteLine($"Welcome, {rdr["UserName"]} (id = {rdr["UserId"]})!");
                        }
                        else
                        {
                            Console.ForegroundColor = ConsoleColor.Red;
                            Console.WriteLine("Invalid credentials");
                        }
                    }
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"Database error: {ex.Message}");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Unexpected error: {ex.Message}");
            }
        }

        // Function to validate the user input
        static bool IsValidInput(string input)
        {
            // Only allow alphanumeric characters and length of up to 50 characters
            return Regex.IsMatch(input, @"^[a-zA-Z0-9]{1,50}$");
        }
    }
}

Code Explanation:

By limiting input to a safe subset (alphanumeric characters), we reduce the chances of harmful SQL syntax or scripts being entered. Special characters such as ‘, –, ;, etc., commonly used in SQL Injection attacks, are not allowed. Even though parameterized queries provide strong protection, input validation adds an additional layer of defense.

Regular Expression:

The IsValidInput function uses a regular expression (^[a-zA-Z0-9]{1,50}$) to validate that the input:

• Contains only alphanumeric characters (a-zA-Z0-9).
• Has a length between 1 and 50 characters. This limits overly long inputs, which could potentially be used for more complex injection attacks.

Validation Logic:

• The username and password inputs are validated before they are used in the SQL query.
• If the input does not match the expected pattern (alphanumeric and within the specified length limits), the program will print an error message and return without executing the SQL query.

Output: Enter username as admin’ OR 1=1; — and any password.

SQL Injection is a critical security issue, and proper precautions must be taken. The best practices to prevent SQL Injection include parameterized queries, stored procedures, and input validation. Always ensure user inputs are sanitized and validated before interacting with a database to prevent malicious attacks.

SQL Injection is a critical vulnerability that can compromise your database and application security. By using parameterized queries and stored procedures with ADO.NET Core, you can safeguard your applications against these attacks. Always prioritize secure coding practices, validate user inputs, and never trust data from external sources without proper checks.

In the next article, I will discuss how to implement Dynamic SQL using ADO.NET Core with Examples. In this article, I explain SQL Injection and Prevention using ADO.NET Core with examples. I hope you enjoy this article on SQL Injection and Prevention using ADO.NET Core with Examples.