🛡️ Security in .NET: Preventing SQL Injection

SQL Injection occurs when user input is treated as executable SQL code.

Instead of being interpreted as simple data, malicious input alters the structure of the SQL command itself.

---

⚠️ Example of a SQL Injection Attack

Consider the following vulnerable code:

var query = "SELECT * FROM Users WHERE Id = " + userInput;

An attacker could provide input such as:

1; DROP TABLE Products;

The resulting SQL command becomes:

SELECT * FROM Users WHERE Id = 1; DROP TABLE Products;

Instead of retrieving a user record, the database executes two commands:

• Fetch user data
• Delete the entire Products table

This is SQL Injection.

---

1️⃣ The Golden Rule: Parameterization

The only reliable defense against SQL Injection is parameterization.

Parameterized queries separate:

• SQL logic (command structure)
• user input (data values)

When parameters are used, the database treats input strictly as literal values — never as executable SQL code.

---

2️⃣ Best Practices with Entity Framework Core

Entity Framework Core (EF Core) is secure by default when using LINQ queries.

However, vulnerabilities can occur when using raw SQL execution methods.

---

❌ Dangerous Approach: FromSqlRaw

Using string interpolation or concatenation inside FromSqlRaw can expose your application to SQL Injection.

// DANGEROUS: vulnerable to SQL Injection

var user = context.Users
    .FromSqlRaw($"SELECT * FROM Users WHERE Email = '{userInput}'")
    .FirstOrDefault();

The input becomes part of the SQL command string.

---

✅ Secure Approach: FromSqlInterpolated

Use FromSqlInterpolated whenever dynamic input is required.

// SAFE: EF Core automatically parameterizes input

var user = context.Users
    .FromSqlInterpolated(
        $"SELECT * FROM Users WHERE Email = {userInput}"
    )
    .FirstOrDefault();

Although the syntax looks like string interpolation, EF Core internally converts values into SQL parameters.

---

3️⃣ Best Practices with Dapper

Dapper is widely used for performance-critical applications.

Because Dapper executes raw SQL strings, developers must explicitly use parameters.

✅ Secure Dapper Example

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

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

Dapper ensures that @CatId is treated as a parameterized value.

The SQL structure remains unchanged regardless of user input.

---

4️⃣ Least Privilege: The Database Safety Net

Even well-written code should not rely on full database permissions.

Follow the Principle of Least Privilege:

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

If an attacker discovers an injection vulnerability, restricted permissions prevent catastrophic schema damage.

---

📊 Complete .NET Security Stack

Threat	Solution	Layer
XSS	HtmlSanitizer	Input Layer
XSS / Data Leakage	CSP Headers	Browser Layer
DoS / Brute Force	Rate Limiting	Availability Layer
CSRF	Antiforgery Tokens	Request Layer
SQL Injection	Parameterized Queries	Data Layer

---

🏁 Final Series Thoughts: Security is an Architecture

Security is not a single feature — it is a layered system.

By combining:

• Input sanitization
• Secure headers
• CSRF protection
• Rate limiting
• Parameterized queries

you significantly reduce the risk of common web application vulnerabilities.

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

SqlException

Return a generic message:

"An internal error occurred"

while logging full details securely in:

• Serilog
• Application Insights
• ELK Stack