SQL Injection, parameterized queries, ORM safety, and unsafe dynamic SQL

Overview

SQL injection occurs when untrusted input changes the structure or meaning of a database command. Instead of being handled only as data, attacker-controlled text becomes SQL syntax.

A vulnerable query might concatenate a username into SQL:

var sql =
    "SELECT Id, Email FROM Users WHERE Email = '" +
    request.Email +
    "'";

An attacker can supply input that closes the string literal and adds new SQL logic.

The primary defense is parameterization:

const string sql =
    "SELECT Id, Email FROM Users WHERE Email = @email";

await using var command = new SqlCommand(sql, connection);
command.Parameters.Add(
    new SqlParameter("@email", SqlDbType.NVarChar, 320)
    {
        Value = request.Email
    });

The SQL structure and value are transmitted separately. The database treats the value as data, even if it contains quotes or SQL keywords.

ORMs such as Entity Framework Core usually parameterize LINQ expressions, but they do not make every database operation automatically safe. Raw SQL, dynamic identifiers, interpolated-string misuse, stored procedures that build commands, and unsafe query libraries can reintroduce injection.

This topic matters in interviews because candidates should be able to:

• Explain why parameterization works.
• Distinguish values from identifiers and SQL syntax.
• Identify safe and unsafe EF Core APIs.
• Build dynamic filtering and sorting safely.
• Explain why validation, escaping, stored procedures, and ORMs are not complete defenses alone.
• Apply least privilege and testing as defense in depth.

Core Concepts

How SQL Injection Changes a Query

Suppose an application builds:

SELECT Id, Email
FROM Users
WHERE Email = '[USER INPUT]';

If input is inserted directly, an attacker may alter the predicate:

' OR 1 = 1 --

The resulting command no longer means "find this email." It may mean "return every row."

Injection can affect:

• SELECT.
• INSERT.
• UPDATE.
• DELETE.
• Stored-procedure calls.
• Dynamic DDL.
• Search expressions.
• ORM query languages.
• Administrative and reporting tools.

The impact depends on database permissions, available SQL features, exposed error behavior, and whether multiple statements are allowed.

Injection Is a Code-and-Data Boundary Failure

The root problem is not a malicious apostrophe. The problem is combining:

• Trusted command syntax.
• Untrusted data.

into one executable string.

Unsafe:

var sql = $"SELECT * FROM Products WHERE Name = '{name}'";

Safe:

const string sql =
    "SELECT * FROM Products WHERE Name = @name";

command.Parameters.AddWithValue("@name", name);

Prefer explicit parameter types and sizes over AddWithValue when database type inference could cause conversion or performance problems:

command.Parameters.Add(
    "@name",
    SqlDbType.NVarChar,
    200).Value = name;

Parameterized Queries

A parameterized query defines SQL syntax first and binds values separately:

const string sql = """
    SELECT Id, Name, Price
    FROM Products
    WHERE CategoryId = @categoryId
      AND Price <= @maximumPrice
    """;

await using var command = new SqlCommand(sql, connection);
command.Parameters.Add(
    "@categoryId",
    SqlDbType.UniqueIdentifier).Value = categoryId;
command.Parameters.Add(
    "@maximumPrice",
    SqlDbType.Decimal).Value = maximumPrice;

Benefits:

• Input cannot terminate a literal and introduce syntax.
• Types are explicit.
• Query plans can often be reused.
• Code is easier to review than custom escaping.

Parameterization must happen in the database driver or query API. Replacing characters manually is not equivalent.

What Parameters Can Represent

Parameters represent values:

WHERE CustomerId = @customerId
WHERE CreatedAt >= @from
SET DisplayName = @displayName

Parameters generally cannot represent:

• Table names.
• Column names.
• ASC or DESC.
• SQL operators.
• Keywords.
• Entire predicates.

This does not work as intended:

SELECT * FROM Orders ORDER BY @sortColumn @sortDirection

Identifiers and syntax require query redesign or an allowlist that selects trusted code fragments.

Safe Dynamic Sorting

Map external values to known expressions:

var query = dbContext.Orders
    .Where(order => order.TenantId == tenantId);

query = request.Sort switch
{
    "createdAt" => query
        .OrderBy(order => order.CreatedAt)
        .ThenBy(order => order.Id),

    "-createdAt" => query
        .OrderByDescending(order => order.CreatedAt)
        .ThenByDescending(order => order.Id),

    "total" => query
        .OrderBy(order => order.Total)
        .ThenBy(order => order.Id),

    _ => throw new InvalidSortFieldException(request.Sort)
};

The user selects a predefined behavior. Their text never becomes SQL syntax.

For raw SQL:

var orderBy = request.Sort switch
{
    "createdAt" => "[CreatedAt] ASC, [Id] ASC",
    "-createdAt" => "[CreatedAt] DESC, [Id] DESC",
    "total" => "[Total] ASC, [Id] ASC",
    _ => throw new InvalidSortFieldException(request.Sort)
};

var sql = $"""
    SELECT Id, CreatedAt, Total
    FROM Orders
    WHERE TenantId = @tenantId
    ORDER BY {orderBy}
    """;

Only fixed developer-owned fragments are interpolated. Values still use parameters.

Safe Dynamic Filtering

Compose typed query expressions:

IQueryable<Order> query = dbContext.Orders
    .Where(order => order.TenantId == tenantId);

if (request.Status is not null)
{
    query = query.Where(
        order => order.Status == request.Status);
}

if (request.CreatedFrom is not null)
{
    query = query.Where(
        order => order.CreatedAt >= request.CreatedFrom);
}

LINQ providers translate captured values into parameters.

Avoid accepting arbitrary SQL-like predicates:

?where=Status='Paid' OR 1=1

If a filter language is required:

• Define a grammar.
• Parse to an abstract syntax tree.
• Allowlist fields and operators.
• Type-check values.
• Generate parameterized expressions.
• Limit depth and complexity.

EF Core LINQ Safety

Normal LINQ expressions are parameterized:

var user = await dbContext.Users
    .SingleOrDefaultAsync(
        item => item.Email == request.Email,
        cancellationToken);

The email becomes a database parameter.

This protection applies to translated values, not to:

• Raw SQL strings.
• Dynamically generated expression text from unsafe libraries.
• Client-controlled table or column selection.
• Database functions invoked through unsafe SQL.
• Authorization errors in otherwise safe queries.

An ORM prevents many injection mistakes but cannot correct unsafe design.

EF Core FromSql

EF Core can parameterize interpolated values passed through safe APIs:

var minimumDate = request.CreatedFrom;

var orders = await dbContext.Orders
    .FromSql($"""
        SELECT *
        FROM Orders
        WHERE CreatedAt >= {minimumDate}
        """)
    .ToListAsync(cancellationToken);

The interpolated value becomes a database parameter rather than literal SQL.

The safety depends on passing the interpolated form directly to the parameterizing API.

EF Core FromSqlRaw

FromSqlRaw accepts a raw SQL string and must be treated as a security-sensitive sink.

Unsafe:

var sql = $"""
    SELECT *
    FROM Orders
    WHERE CustomerName = '{request.CustomerName}'
    """;

var orders = await dbContext.Orders
    .FromSqlRaw(sql)
    .ToListAsync(cancellationToken);

Safer:

var orders = await dbContext.Orders
    .FromSqlRaw(
        """
        SELECT *
        FROM Orders
        WHERE CustomerName = {0}
        """,
        request.CustomerName)
    .ToListAsync(cancellationToken);

Prefer FromSql or explicit DbParameter objects when only values vary.

Interpolated Strings Can Lose Safety

This is unsafe:

var sql =
    $"SELECT * FROM Orders WHERE CustomerName = '{request.CustomerName}'";

var orders = dbContext.Orders.FromSqlRaw(sql);

The C# interpolation is completed before EF Core receives the string. EF sees one raw SQL value and cannot recover the data boundary.

Code review must examine the API and how the string was constructed.

ExecuteSql and ExecuteSqlRaw

Use parameterizing execution methods for commands:

await dbContext.Database.ExecuteSqlAsync($"""
    UPDATE Orders
    SET Archived = 1
    WHERE CreatedAt < {cutoff}
    """);

Treat ExecuteSqlRaw like FromSqlRaw: only trusted SQL structure should be present, and values should use parameters.

Raw methods are not inherently vulnerabilities. They are dangerous when untrusted input influences their command text.

ADO.NET Commands

Safe ADO.NET:

const string sql = """
    UPDATE Users
    SET DisplayName = @displayName
    WHERE Id = @userId
    """;

await using var command = new SqlCommand(sql, connection);
command.Parameters.Add(
    "@displayName",
    SqlDbType.NVarChar,
    100).Value = request.DisplayName;
command.Parameters.Add(
    "@userId",
    SqlDbType.UniqueIdentifier).Value = userId;

await command.ExecuteNonQueryAsync(cancellationToken);

Use DBNull.Value for database null:

parameter.Value = value is null
    ? DBNull.Value
    : value;

Stored Procedures

Stored procedures can be safe when:

• The application uses typed parameters.
• The procedure does not concatenate those parameters into SQL.
• Execute permissions follow least privilege.

Application call:

await using var command = new SqlCommand(
    "GetOrdersByCustomer",
    connection)
{
    CommandType = CommandType.StoredProcedure
};

command.Parameters.Add(
    "@CustomerId",
    SqlDbType.UniqueIdentifier).Value = customerId;

Unsafe procedure:

CREATE PROCEDURE SearchOrders
    @WhereClause nvarchar(max)
AS
BEGIN
    EXEC(
        N'SELECT * FROM Orders WHERE ' +
        @WhereClause
    );
END

Moving concatenation into the database does not remove injection.

Dynamic SQL Inside Stored Procedures

When dynamic SQL is unavoidable inside SQL Server, use:

• Fixed trusted command structure.
• sp_executesql.
• Typed value parameters.
• Allowlisted identifiers.
• QUOTENAME for identifiers after validation.

Do not treat identifier quoting as authorization. The set of permitted identifiers must still be controlled.

Audit procedures for:

• EXEC.
• EXECUTE.
• Dynamic command variables.
• Concatenated predicates.
• Dynamic object names.

Input Validation

Validation is defense in depth:

• Parse numeric and date values into typed values.
• Enforce length and range.
• Allowlist enum-like choices.
• Reject unsupported filters and sorts.
• Normalize only when the domain requires it.

Validation does not replace parameters:

if (IsValidEmail(email))
{
    // Still unsafe if concatenated into SQL.
}

Validated data can still contain SQL-significant characters, and future validation changes can expose the query.

Escaping Is Not the Primary Defense

Manual escaping is fragile because:

• Rules differ by database and mode.
• Encodings and collations matter.
• Numeric and identifier contexts differ from strings.
• Double escaping and truncation can occur.
• Maintenance changes can bypass the helper.

Use the database driver's parameter API rather than trying to make arbitrary strings safe SQL.

Least Privilege

Parameterization prevents injection. Least privilege limits damage if another flaw remains.

Application database accounts should:

• Avoid administrator or owner roles.
• Access only required schemas and objects.
• Have read-only access where writes are unnecessary.
• Avoid DDL permissions.
• Use separate credentials for different workloads where practical.
• Execute only required stored procedures.

Do not share one database administrator credential among all applications.

Views and Separate Database Users

Database views can expose only required rows or columns. Separate users can distinguish:

• Read-only reporting.
• Transactional application writes.
• Background maintenance.
• Migrations.

Migration credentials should not be used by the normal application runtime.

Connection Secrets

Protect connection strings with:

• A secret manager.
• Workload or managed identity where supported.
• Rotation.
• Restricted access.
• No logging.

SQL injection and credential leakage are separate threats, but powerful leaked credentials increase impact.

Authorization Is Still Required

This query is parameterized but may still be insecure:

var order = await dbContext.Orders
    .SingleAsync(
        item => item.Id == requestedOrderId,
        cancellationToken);

If the caller may access only their tenant's orders:

var order = await dbContext.Orders
    .SingleOrDefaultAsync(
        item =>
            item.Id == requestedOrderId &&
            item.TenantId == currentTenantId,
        cancellationToken);

SQL injection prevention does not prevent insecure direct object reference or broken access control.

Second-Order SQL Injection

In second-order injection, unsafe text is stored safely first and later concatenated into a different query.

Example:

User input -> parameterized INSERT -> stored value
Stored value -> later string-built maintenance SQL -> injection

Treat database content as untrusted when it reaches a SQL-construction sink. Trust is based on provenance and validation, not storage location.

LIKE Queries

Parameters prevent SQL injection:

var pattern = $"%{searchTerm}%";

var products = await dbContext.Products
    .Where(product =>
        EF.Functions.Like(product.Name, pattern))
    .ToListAsync(cancellationToken);

However, % and _ remain wildcard syntax. If the product requirement is a literal search, escape wildcard characters using a defined escape character.

This is a search-semantics issue, not SQL injection, because the value remains parameterized.

IN Clauses

LINQ:

var orders = await dbContext.Orders
    .Where(order => requestedIds.Contains(order.Id))
    .ToListAsync(cancellationToken);

For large lists, consider:

• Table-valued parameters.
• Temporary tables.
• Bulk-loading a typed input.
• Database-specific array parameters.

Do not join a list into SQL text.

Multi-Tenant Queries

Tenant filters should be applied consistently:

modelBuilder.Entity<Order>()
    .HasQueryFilter(
        order => order.TenantId == currentTenant.Id);

Global filters can help but require care for:

• Background jobs.
• Administrative access.
• Context lifetime.
• Raw SQL that bypasses expected filters.

Database row-level security can add defense in depth.

Error Handling

Do not expose:

• SQL text.
• Table names.
• Connection details.
• Stack traces.
• Database error numbers without a consumer need.

Return a stable problem response and log detailed diagnostics securely with a correlation ID.

Verbose errors can help attackers discover query structure, but hiding errors does not fix injection.

Logging

Log:

• Operation and route.
• Safe query identifier.
• Duration.
• Row count where appropriate.
• Correlation ID.
• Database failure category.

Avoid logging:

• Full raw SQL containing secrets or personal data.
• Parameter values by default.
• Connection strings.
• Authentication tokens.

Development logging that includes sensitive data must not be enabled in production.

Code Review

Search for security-sensitive APIs and patterns:

FromSqlRaw
ExecuteSqlRaw
SqlCommand
DbCommand
EXEC(
sp_executesql
ORDER BY "
WHERE " +
string interpolation near SQL

For each occurrence, trace whether untrusted input can influence:

• Command text.
• Identifiers.
• Operators.
• Sort direction.
• Stored dynamic fragments.

Do not classify an API as safe solely by name.

Static and Dynamic Testing

Use:

• Static analysis for concatenated SQL.
• Dependency and code review.
• Integration tests with hostile input.
• Database audit logs.
• Dynamic application security testing.
• Penetration testing for critical systems.

Tests should verify:

• Quotes remain data.
• Sort and filter fields are allowlisted.
• Raw-SQL parameters are bound.
• Unauthorized rows remain inaccessible.
• Error responses do not leak details.

Do not run destructive attack payloads against production data.

Performance and Parameterization

Parameterized queries often improve plan reuse, but query plans can be suboptimal for skewed data. Solve performance issues with:

• Appropriate indexes.
• Query redesign.
• Database-supported plan controls.
• Purpose-specific query variants.
• Measured recompilation strategies.

Do not switch to string-concatenated literals as a casual performance fix.

Threat Modeling Dynamic Reporting

Reporting systems often need flexible columns, filters, grouping, and sorting.

Safer design:

External query model
  -> parser and authorization
  -> allowlisted report fields
  -> typed query expression
  -> parameterized execution

Enforce:

• Per-field authorization.
• Maximum query complexity.
• Row and time limits.
• Export limits.
• Read-only credentials.
• Auditing.

Common Mistakes

• Concatenating or interpolating user input into SQL.
• Assuming validation makes concatenation safe.
• Assuming every ORM operation is safe.
• Passing prebuilt interpolated strings to raw SQL APIs.
• Letting users supply column or table names directly.
• Treating stored procedures as inherently safe.
• Building dynamic SQL inside procedures without parameters.
• Escaping quotes as the primary defense.
• Using administrator database credentials.
• Logging sensitive SQL parameters.
• Forgetting tenant and resource authorization.
• Joining IN values into text.
• Treating stored database values as trusted.
• Ignoring wildcard semantics in LIKE.
• Disabling parameterization to solve performance problems.

Best Practices

• Keep SQL structure fixed and bind all values as parameters.
• Prefer typed LINQ for dynamic filtering and sorting.
• Treat raw SQL APIs as security-sensitive sinks.
• Pass interpolated values directly only to APIs that parameterize them.
• Map client sort and field names to trusted expressions.
• Use typed parameters with appropriate size and precision.
• Audit dynamic SQL inside stored procedures.
• Apply validation as defense in depth, not as the primary barrier.
• Run the application with least database privilege.
• Separate migration and runtime credentials.
• Enforce tenant and object authorization in the query.
• Test raw SQL, dynamic reports, and hostile inputs.
• Avoid exposing database details in errors and logs.