CWE-89: SQL Injection - C#

Overview

SQL Injection occurs when an application incorporates untrusted data into a SQL query without proper validation or parameterization. Attackers can manipulate queries to bypass authentication, access unauthorized data, modify or delete records, or execute administrative operations.

Primary Defence: Use parameterized queries with SqlCommand.Parameters (ADO.NET), Entity Framework LINQ queries, or Dapper with parameter binding.

Common Vulnerable Patterns

String Concatenation

string userId = Request.QueryString["id"];
string query = "SELECT * FROM Users WHERE Id = " + userId;
SqlCommand cmd = new SqlCommand(query, connection);
SqlDataReader reader = cmd.ExecuteReader(); // VULNERABLE

Why this is vulnerable:

• Concatenation embeds user input directly in the SQL text.
• Attackers can inject payloads like 1 OR 1=1 or 1; DROP TABLE Users--.
• The database interprets injected text as part of the query.

String Interpolation

string username = Request.Form["username"];
string query = $"SELECT * FROM Users WHERE Username = '{username}'";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.ExecuteReader(); // VULNERABLE

Why this is vulnerable:

• Interpolation embeds user input directly into the SQL string, so quotes and operators can break out of the intended literal.
• Attackers can inject additional predicates or statements that change query behavior.

Secure Patterns

SqlCommand with Parameters (ADO.NET)

// Recommended: Explicit type specification
int userId = int.Parse(Request.QueryString["id"]);
string query = "SELECT * FROM Users WHERE Id = @UserId";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.Add("@UserId", SqlDbType.Int).Value = userId; // SAFE (type-safe)
SqlDataReader reader = cmd.ExecuteReader();

// Alternative: AddWithValue (simpler but may infer wrong type)
string userIdStr = Request.QueryString["id"];
cmd.Parameters.AddWithValue("@UserId", userIdStr); // SAFE but less robust

Why this works:

• Parameters are sent separately from the SQL text and treated strictly as data.
• Explicit SqlDbType.Int enforces type safety and avoids inference issues from AddWithValue().
• The database receives a typed parameter, so injected SQL syntax is never executed.

Entity Framework (LINQ)

string username = Request.Form["username"];
using (var context = new MyDbContext())
{
    // LINQ to Entities - automatically parameterized
    var user = context.Users
        .Where(u => u.Username == username) // SAFE
        .FirstOrDefault();
}

Why this works:

• EF translates LINQ into parameterized SQL automatically.
• User input is never concatenated into the query text.
• EF sends values as parameters (e.g., @p0), preventing injection.

Entity Framework Raw SQL

string username = Request.Form["username"];
using (var context = new MyDbContext())
{
    // Parameterized raw SQL - SAFE
    var users = context.Users
        .FromSqlRaw("SELECT * FROM Users WHERE Username = {0}", username)
        .ToList();
}

Why this works:

• Positional placeholders ({0}) are converted to parameters.
• Values are sent separately from the SQL text.
• Raw SQL is still parameterized, so injected syntax is not executed.

Framework-Specific Guidance

Entity Framework Core

// LINQ - SAFE
var user = dbContext.Users
    .Where(u => u.Email == email && u.IsActive)
    .FirstOrDefault();

// FromSqlRaw with parameters - SAFE
var users = dbContext.Users
    .FromSqlRaw("SELECT * FROM Users WHERE Email = {0}", email)
    .ToList();

// FromSqlInterpolated - SAFE (auto-parameterized)
var users = dbContext.Users
    .FromSqlInterpolated($"SELECT * FROM Users WHERE Email = {email}")
    .ToList();

Why this works:

• FromSqlInterpolated extracts parameters from the interpolated string.
• Values are converted into database parameters automatically.
• You get interpolation convenience without embedding raw input in SQL.

Dapper

string username = Request.Form["username"];
using (var connection = new SqlConnection(connectionString))
{
    // Parameterized query - SAFE
    var user = connection.QueryFirstOrDefault<User>(
        "SELECT * FROM Users WHERE Username = @Username",
        new { Username = username }
    );
}

Why this works:

• Dapper maps object properties to named parameters.
• The @Username placeholder becomes a database parameter.
• Values are sent separately from SQL, blocking injection.

NHibernate

string username = Request.Form["username"];
// HQL with named parameters - SAFE
var query = session.CreateQuery("FROM User WHERE Username = :username");
query.SetParameter("username", username);
var user = query.UniqueResult<User>();

Why this works:

• SetParameter binds values to placeholders (:username).
• Values are sent separately from SQL text.
• The database treats input as data only.

Data Type Handling

Numeric Parameters

string idParam = Request.QueryString["id"];
if (int.TryParse(idParam, out int userId))
{
    string query = "SELECT * FROM Users WHERE Id = @UserId";
    SqlCommand cmd = new SqlCommand(query, connection);
    cmd.Parameters.Add("@UserId", SqlDbType.Int).Value = userId; // Type-safe
    // ...
}
else
{
    // Handle invalid input
}

DateTime Parameters

string query = "SELECT * FROM Orders WHERE OrderDate > @StartDate";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = startDate; // Type-safe

IN Clauses (Multiple Values)

// Vulnerable - don't concatenate
string ids = Request.QueryString["ids"]; // "1,2,3"
string query = $"SELECT * FROM Users WHERE Id IN ({ids})"; // VULNERABLE

Recommended: Entity Framework LINQ (Simplest)

var userIds = new List<int> { 1, 2, 3, 4, 5 };

// EF Core automatically translates Contains() to parameterized IN clause - SAFE
var users = context.Users
    .Where(u => userIds.Contains(u.Id))
    .ToList();

// Generated SQL: SELECT * FROM Users WHERE Id IN (@p0, @p1, @p2, @p3, @p4)

Why this works:

• EF Core expands Contains() into a parameterized IN clause.
• Each value becomes a separate parameter (@p0, @p1, ...).
• No user input is concatenated into SQL.

Recommended: Dapper (Simple)

var userIds = new List<int> { 1, 2, 3, 4, 5 };

// Dapper automatically expands arrays/lists to parameters - SAFE
var users = connection.Query<User>(
    "SELECT * FROM Users WHERE Id IN @UserIds",
    new { UserIds = userIds }
).ToList();

// Generated SQL: SELECT * FROM Users WHERE Id IN (@UserIds1, @UserIds2, @UserIds3, @UserIds4, @UserIds5)

ADO.NET with Table-Valued Parameters (SQL Server)

// Table-valued parameters (requires predefined table type in SQL Server)
// Use this approach if not using Entity Framework or Dapper

// First, create the table type in SQL Server:
// CREATE TYPE dbo.IntListTableType AS TABLE (Id INT)

var userIds = new List<int> { 1, 2, 3, 4, 5 };
DataTable idTable = new DataTable();
idTable.Columns.Add("Id", typeof(int));
foreach (int id in userIds)
{
    idTable.Rows.Add(id);
}

SqlCommand cmd = new SqlCommand("SELECT * FROM Users WHERE Id IN (SELECT Id FROM @UserIds)", connection);
SqlParameter param = cmd.Parameters.AddWithValue("@UserIds", idTable);
param.SqlDbType = SqlDbType.Structured;
param.TypeName = "dbo.IntListTableType"; // Predefined table type

Migration Strategy

1. Identify all SQL construction: Search for SqlCommand, string concatenation with SQL keywords
2. Replace concatenation: Change to parameterized queries
3. Use Parameters.Add(): Prefer explicit SqlDbType for type safety; use AddWithValue() only when appropriate
4. Convert to LINQ: Where possible, use Entity Framework LINQ for type safety
5. Test thoroughly: Verify functionality and try injection payloads

SQL Server Permission Hardening (Defense in Depth)

Even with parameterized queries, restrict database permissions to limit SQL injection impact:

SQL Server Permission Examples

Create restricted application user:

-- Create login and user

CREATE LOGIN app_user WITH PASSWORD = 'Strong_P@ssw0rd';
CREATE USER app_user FOR LOGIN app_user;

-- DO NOT add to these roles:
-- db_datareader, db_datawriter (grant table-level permissions instead)
-- db_owner, db_ddladmin, db_securityadmin, db_accessadmin

-- Grant specific table permissions only

GRANT SELECT, INSERT, UPDATE ON dbo.Users TO app_user;
GRANT SELECT, INSERT, UPDATE ON dbo.Orders TO app_user;
GRANT SELECT ON dbo.Products TO app_user;  -- Read-only

What this prevents in SQLi attacks:

• '; DROP TABLE Users; -- - No DROP permission
• ' UNION SELECT password FROM AdminUsers -- - No access to admin tables
• '; EXEC xp_cmdshell 'del /F /S /Q C:\*' -- - No access to xp_cmdshell
• '; ALTER TABLE Users ADD IsAdmin BIT -- - No schema modification permissions

Still possible (use parameterized queries!):

• Data exfiltration from permitted tables
• Unauthorized data modification within granted permissions

Key principle: Least privilege reduces blast radius but does NOT eliminate SQL injection risk. Always use parameterized queries.

C# connection string with restricted user:

string connectionString = "Server=myserver;Database=myapp;User Id=app_user;Password=" + 
    Environment.GetEnvironmentVariable("DB_PASSWORD");

// Or use Integrated Security with limited service account
string connectionString = "Server=myserver;Database=myapp;Integrated Security=true;";

FromSqlRaw String Interpolation

// This is VULNERABLE - bypasses parameterization
var users = context.Users
    .FromSqlRaw($"SELECT * FROM Users WHERE Email = '{email}'") // VULNERABLE
    .ToList();

Use instead:

// Use FromSqlInterpolated (auto-parameterizes)
var users = context.Users
    .FromSqlInterpolated($"SELECT * FROM Users WHERE Email = {email}") // SAFE
    .ToList();

// Or FromSqlRaw with explicit parameters
var users = context.Users
    .FromSqlRaw("SELECT * FROM Users WHERE Email = {0}", email) // SAFE
    .ToList();

AddWithValue Type Inference Issues

// CAUTION - AddWithValue may infer wrong type
cmd.Parameters.AddWithValue("@Name", username); // May infer as NVarChar(length)

// BETTER - Specify type explicitly
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 100).Value = username;

Stored Procedures Without Parameters

// WRONG - Concatenating stored procedure call
string procCall = $"EXEC GetUserByEmail '{email}'";
cmd.ExecuteNonQuery();

// CORRECT - Use parameters with stored procedures
SqlCommand cmd = new SqlCommand("GetUserByEmail", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Email", email);

Not Using CommandType Properly

// WRONG - Treating stored procedure as text
string query = $"EXEC GetUser {userId}";
cmd.CommandText = query;
cmd.CommandType = CommandType.Text; // Still vulnerable

// CORRECT - Use CommandType.StoredProcedure
cmd.CommandText = "GetUser";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserId", userId);

Remediation Steps

Locate the Finding

Review the data flow in the security scan report:

• Source: Where user input enters (Request.QueryString, Request.Form, Request.Cookies, HttpContext.Request, route parameters, headers)
• Sink: The SQL execution call (ExecuteReader(), ExecuteNonQuery(), ExecuteScalar(), FromSqlRaw(), Database.ExecuteSqlRaw())
• Note any frames between source and sink

Understand the Data Flow

Trace how data moves from source to sink:

• Identify if any validation or sanitization exists
• Look for string concatenation or interpolation in SQL queries
• Check if parameterized queries are used
• Verify if Entity Framework LINQ is used (automatically parameterized)

Identify the Pattern

Match the code to vulnerable patterns:

• String concatenation → Pattern: "SELECT * FROM Users WHERE Id = " + userId
• String interpolation → Pattern: $"SELECT * FROM Users WHERE Name = '{name}'"
• FromSqlRaw with interpolation → Pattern: FromSqlRaw($"...'{value}'...")
• No parameterization → Pattern: Direct query execution without parameters

Apply the Fix

Choose the appropriate remediation approach based on your architecture:

Option 1: Use Entity Framework LINQ queries (Recommended - automatic parameterization)

• Use LINQ query methods like Where(), FirstOrDefault(), Select()
• Entity Framework automatically converts LINQ expressions to parameterized SQL
• Provides type safety and prevents concatenation-based injection
• Avoid raw SQL methods like FromSqlRaw() with string interpolation

Option 2: Use ADO.NET parameterized queries (Best for direct database access)

• Use SqlCommand.Parameters.Add() with explicit SqlDbType for type safety
• Replace string concatenation with ? or @param placeholders
• Use Parameters.Add() instead of AddWithValue() when possible
• Avoid string concatenation or interpolation in SQL queries

Option 3: Use Dapper or other micro-ORMs with parameters (Simple and performant)

• Pass parameters as anonymous objects or dynamic parameters
• Dapper automatically maps object properties to named parameters
• Use @ParameterName syntax in SQL with parameter object
• Avoid concatenating user input into raw SQL strings

Option 4: For dynamic table/column names, use allowlist validation (Special cases)

• Parameterized queries cannot be used for identifiers (table/column names)
• Create allowlist of valid table/column names
• Validate user input against allowlist before using in query
• Consider redesigning to avoid user-controlled identifiers

See the Secure Patterns section for detailed implementation examples of each approach.

Verify the Fix

Test and confirm:

• Test with malicious inputs: ' OR '1'='1, admin'--, 1'; DROP TABLE Users--
• Run unit tests to verify parameterization
• Enable SQL logging to inspect generated queries
• Rescan with the security scanner to confirm finding is resolved
• Verify business functionality still works

Check for Similar Issues

Search for related vulnerabilities:

• Search codebase for: SqlCommand, FromSqlRaw, ExecuteSqlRaw, string concatenation with SQL keywords
• Review other controllers/services that accept user input and execute SQL
• Check for similar patterns in the same module or related functionality

Additional Resources

• CWE-89: SQL Injection
• Dapper Documentation
• Entity Framework Core Documentation
• OWASP SQL Injection Prevention Cheat Sheet
• Parameterized Queries in .NET
• SqlCommand.Parameters Property