Prevent SQL Injection in SQL Server: Best Practices in .NET

Spread the love

SQL Injection is one of the most common and dangerous security flaws in software development today. In this blog post, we will explore what SQL Injection is in SQL Server, why it is so dangerous, and most importantly, how we can prevent SQL Injection in our web application. We will be using best practices like parameterized queries, stored procedures, and Entity Framework techniques to prevent SQL Injection. We’ll also walk through a real-world coding example showing just how easy it is for hackers to exploit vulnerable code.

What Is SQL Injection?

SQL Injection is a type of cyberattack where a malicious user “injects” harmful SQL code into an application’s database query. When user input is improperly handled, an attacker can modify or bypass the query to:

Retrieve sensitive data (like passwords or credit cards)
Delete or update records
Execute administrative operations
In extreme cases, take control of the database server

Why SQL Injection Is So Dangerous

It’s easy to exploit – Anyone with basic knowledge of SQL can try it.
It exposes critical data – Personal info, user credentials, or even entire tables.
It affects trust – A data breach harms your brand and user confidence.
It’s preventable – Yet many developers still leave apps vulnerable.

Real-World SQL Injection Example in C# with SQL Server

Let’s look at a typical mistake junior developers make when building login forms.

❌ Vulnerable Code (C# and ADO.NET)

string username = txtUsername.Text;
string password = txtPassword.Text;

string query = $"SELECT * FROM Users WHERE Username = '{username}' AND Password = '{password}'";

SqlCommand cmd = new SqlCommand(query, sqlConnection);
SqlDataReader reader = cmd.ExecuteReader();

Looks normal, right? But now watch what happens if a malicious user enters:

Username: admin’–
Password: (anything)

The generated SQL query becomes:

SELECT * FROM Users WHERE Username = 'admin'--' AND Password = 'anything'

The — starts a comment in SQL, so everything after it is ignored. The query is now:

SELECT * FROM Users WHERE Username = 'admin'

Result: The attacker logs in as admin without knowing the password!

How to Prevent SQL Injection in SQL Server

✅ 1. Use Parameterized Queries

This is the most effective and straightforward way to write secure SQL queries in .NET.

string query = "SELECT * FROM Users WHERE Username = @username AND Password = @password";

SqlCommand cmd = new SqlCommand(query, sqlConnection);
cmd.Parameters.AddWithValue("@username", txtUsername.Text);
cmd.Parameters.AddWithValue("@password", txtPassword.Text);

SqlDataReader reader = cmd.ExecuteReader();

Why it works: Parameterized queries treat user input as data, not code. SQL Server knows how to handle it safely, even if it includes special characters.

✅ 2. Use Stored Procedures

Stored procedures help by encapsulating SQL logic on the server side, separating it from user input.

CREATE PROCEDURE GetUser
    @Username NVARCHAR(100),
    @Password NVARCHAR(100)
AS
BEGIN
    SELECT * FROM Users WHERE Username = @Username AND Password = @Password
END

C# code:

SqlCommand cmd = new SqlCommand("AuthenticateUser", sqlConnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Username", txtUsername.Text);
cmd.Parameters.AddWithValue("@Password", txtPassword.Text);

Tip: Stored procedures still need parameters. If you build dynamic SQL inside the procedure, you could still be vulnerable.

✅ 3. Follow ORM Best Practices (Entity Framework)

If you’re using an ORM like Entity Framework.

var user = dbContext.Users
    .FirstOrDefault(u => u.Username == username && u.Password == password);

If you are using raw SQL, always use parameters

var user = dbContext.Users
    .FromSqlRaw("SELECT * FROM Users WHERE Username = @username AND Password = @password",
    new SqlParameter("@username", @username),
    new SqlParameter("@password", @password))
    .FirstOrDefault();

Reminder: LINQ queries in EF Core automatically parameterize input to prevent injection.

✅ 4. Input Validation and Sanitization

Never trust user input. Always validate it using strong rules:

Use Regex to allow only safe characters.
Set max length on input fields.
Reject special characters if not needed (like quotes, semicolons).
Use built-in .NET validation libraries whenever possible.

if (username.Length > 50 || !Regex.IsMatch(username, @"^[a-zA-Z0-9_]+$"))
{
    throw new ArgumentException("Invalid username.");
}

Bonus Tip: Turn Off Detailed Error Messages in Production

Never expose raw SQL errors to users; they might reveal table names, query structure, or even credentials. Always display a user-friendly error message or error page.

SQL Injection is not a theoretical threat—it’s real, and it happens every day.

🔑 Key Takeaways:

Always use parameterized queries to separate code and data.
Prefer stored procedures or ORMs like Entity Framework with LINQ.
Validate and sanitize every piece of user input.
Think like an attacker: if someone wanted to break your code, how would they do it?

By following these practices, you’ll write more secure SQL queries in .NET and protect your application from one of the most critical web vulnerabilities.

Also Read,

Spread the love