🛡️ Security in .NET: Defeating SQL Injection

If XSS is an attack on your users, SQL Injection (SQLi) is an attack on your core data.

SQL Injection occurs when an attacker tricks your database into executing unintended commands by injecting SQL syntax into input fields.

In this final chapter of our security series, we ensure the data layer remains fully protected.

---

1️⃣ The Golden Rule of Database Security

The only truly reliable defense against SQL Injection is Parameterization.

Parameterized queries clearly separate:

• SQL Command Structure
• User Input Data

The database engine treats parameters strictly as values — never as executable SQL.

Even if an attacker enters malicious input such as:

DROP TABLE Users

it will be stored as text instead of executed.

---

2️⃣ EF Core: The Common "Interpolation Trap"

Many developers assume EF Core automatically prevents SQL Injection in all scenarios.

While LINQ queries are safe by default, misuse of Raw SQL methods can reintroduce vulnerabilities.

❌ Dangerous Approach: FromSqlRaw

Using string interpolation inside FromSqlRaw builds a dynamic SQL string that can be manipulated.

// DANGEROUS: vulnerable to SQL Injection
var user = context.Users
    .FromSqlRaw($"SELECT * FROM Users WHERE Email = '{userInput}'")
    .FirstOrDefault();

Here, the input becomes part of the SQL command itself.

---

✅ Secure Approach: FromSqlInterpolated

Use FromSqlInterpolated whenever dynamic values are required.

// SAFE: EF Core automatically parameterizes input
var user = context.Users
    .FromSqlInterpolated($"SELECT * FROM Users WHERE Email = {userInput}")
    .FirstOrDefault();

Although the syntax looks similar, EF Core internally converts the value into a database parameter.

This prevents malicious SQL from being executed.

---

3️⃣ Dapper & Manual SQL

Dapper is popular for performance-critical scenarios.

Because SQL strings are written manually, developers must explicitly use parameters.

✅ Correct Dapper Pattern

var sql =
    "SELECT * FROM Products WHERE CategoryId = @CatId";

var products =
    connection.Query<Product>(
        sql,
        new { CatId = userInput }
    );

Dapper ensures that @CatId is passed as a parameter value rather than concatenated into SQL.

---

4️⃣ Defense in Depth: Database Least Privilege

Even secure code should not rely on excessive database permissions.

Apply the Principle of Least Privilege:

• ❌ Avoid using sa or db_owner
• ✅ Grant only required permissions
• 🛡️ Limit potential damage from unexpected vulnerabilities

Recommended permissions for application database users:

Permission	Status
SELECT	Allowed
INSERT	Allowed
UPDATE	Allowed
DELETE	Allowed
DROP	Not allowed
ALTER	Not allowed
TRUNCATE	Not allowed

Even if an injection vulnerability exists, restricted permissions prevent attackers from destroying your schema.

---

📊 The Complete "Fort Knox" Security Stack

A secure .NET application protects every layer of the architecture:

Layer	Threat	Solution
Input	XSS (Malicious Scripts)	HtmlSanitizer + JsonConverter
Browser	Data Leakage / Script Execution	CSP Headers Middleware
Pipeline	Error Handling / Response Consistency	ResponseWrapperMiddleware
Availability	DoS / Brute Force	.NET 8 Rate Limiting
Data	SQL Injection	Parameterization + Least Privilege

---

🏁 Final Series Thoughts: Security is an Architecture

Security is not something added at the end of development.

It is an architectural decision made at the beginning of a project.

By implementing layered protections, your system becomes:

• Resilient
• Predictable
• Maintainable
• Production-ready

🛡️ Final Pro Tip Use ResponseWrapperMiddleware to catch SqlException. Never expose raw database errors such as:

Table 'Users' not found

Instead:

• Log detailed error information internally
• Return a generic message to the client

Example message:

An internal error occurred