Parameterization by Default: LINQ Safety, FromSql Variants, SqlQuery, and Injection Boundaries

Learning outcomes

1. The practical problem: a “small SQL escape hatch” becomes a code-execution boundary

ServiceHub mostly uses LINQ, but production data layers eventually need a query the provider expresses more clearly in SQL: a reporting projection, a database function, a stored procedure, or a carefully tuned statement. Raw SQL is not inherently unsafe. The security boundary is whether untrusted data becomes SQL syntax or remains a database parameter value.

SQL injection occurs when attacker-controlled text changes the structure or meaning of a SQL statement. A parameter is a value transmitted separately from the command text through an ADO.NET DbParameter. The database parses the SQL structure independently of the parameter value, so characters inside the value do not become new SQL tokens.

2. LINQ normally keeps values out of SQL text

When EF translates an IQueryable, captured runtime values normally become command parameters. The exact parameter names and SQL syntax are provider-specific, but the separation is observable.

var term = request.SearchText;var rows = await db.WorkOrders    .AsNoTracking()    .Where(w => w.Summary.Contains(term))    .OrderBy(w => w.Id)    .Take(50)    .Select(w => new { w.Id, w.WorkOrderNumber, w.Summary })    .ToListAsync(cancellationToken);

SELECT "w"."work_order_id", "w"."work_order_number", "w"."summary"FROM "work_orders" AS "w"WHERE "w"."tenant_id" = @__TenantId_0  AND instr("w"."summary", @__term_1) > 0ORDER BY "w"."work_order_id"LIMIT @__p_2;-- @__TenantId_0='tenant-a' is conceptually a value parameter-- @__term_1 is a value parameter-- @__p_2=50 is a value parameter

The generated command proves the values are parameterized and that the Chapter 22 tenant filter participates in the SQL. It does not prove the caller is authorized to choose tenant-a; that identity-to-tenant decision remains an application/security concern.

3. FromSql and interpolated SQL preserve the value/syntax separation

EF Core’s interpolated FromSql API accepts a FormattableString. Interpolated values are converted into parameters rather than pasted into the SQL text. Use it when SQL structure is fixed and only values vary.

var minimumPriority = request.MinimumPriority;var rows = await db.WorkOrders    .FromSql($"""        SELECT *        FROM work_orders        WHERE priority >= {minimumPriority}        """)    .Where(w => w.TenantId == db.TenantId)    .OrderBy(w => w.Id)    .Take(50)    .AsNoTracking()    .ToListAsync(cancellationToken);

The interpolation hole becomes a DbParameter. Composition after FromSql is still translated by EF when the SQL is composable. Keep tenant policy explicit in the query or rely on the correctly configured global filter; do not assume raw SQL text automatically includes application predicates.

4. Database.SqlQuery gives a parameterized raw-SQL path for non-entity results

EF Core can also materialize scalar or unmapped result shapes through Database.SqlQuery<T>. The same rule applies: interpolated values are parameters; SQL structure remains developer-authored.

var tenantId = db.TenantId;var numbers = await db.Database    .SqlQuery<string>($"""        SELECT work_order_number AS Value        FROM work_orders        WHERE tenant_id = {tenantId}        ORDER BY work_order_id        LIMIT 20        """)    .ToListAsync(cancellationToken);

For scalar composition, EF documentation uses the result-column alias Value where required. For richer unmapped types, define a result shape that matches the selected columns. This is still SQL code: review selected columns, tenant predicates, cardinality, and provider syntax.

5. Deliberate vulnerability: FromSqlRaw plus string interpolation turns data into syntax

The dangerous API is not “raw SQL” by itself; the dangerous pattern is building command text from untrusted data. The following code interpolates search into the statement before EF sees it.

var search = request.SearchText;var sql = $"SELECT * FROM work_orders WHERE summary LIKE '%{search}%'";var rows = await db.WorkOrders    .FromSqlRaw(sql)    .ToListAsync(cancellationToken);

%' OR 1=1 --

That value can terminate the intended string literal and alter the predicate. Depending on provider/driver configuration, other attack forms can disclose data, perform unauthorized writes, or trigger expensive queries. The repair is to keep the SQL structure fixed and parameterize the value.

var pattern = $"%{request.SearchText}%";var rows = await db.WorkOrders    .FromSql($"""        SELECT *        FROM work_orders        WHERE summary LIKE {pattern}        """)    .Where(w => w.TenantId == db.TenantId)    .Take(50)    .AsNoTracking()    .ToListAsync(cancellationToken);

6. Inspect DbParameter metadata without dumping secret values

For security testing, observe command text, parameter names, database types, sizes, nullability, command duration, and error class. Avoid logging full parameter values in production. A command interceptor can verify parameterization without enabling EnableSensitiveDataLogging().

public sealed record ParameterShape(    string Name, DbType Type, int Size, bool IsNullable);public sealed class ParameterShapeInterceptor : DbCommandInterceptor{    public List<(string Sql, ParameterShape[] Parameters)> Records { get; } = [];    public override InterceptionResult<DbDataReader> ReaderExecuting(        DbCommand command,        CommandEventData eventData,        InterceptionResult<DbDataReader> result)    {        var shapes = command.Parameters.Cast<DbParameter>()            .Select(p => new ParameterShape(                p.ParameterName, p.DbType, p.Size, p.IsNullable))            .ToArray();        Records.Add((command.CommandText, shapes)); // deliberately no values        return result;    }}

This evidence can prove that untrusted values stayed outside the SQL text. It cannot prove the query is authorized, bounded, or performant; those are separate tests.

7. Parameters cannot stand in for identifiers or SQL grammar

Databases generally do not allow a parameter to replace a column name, table name, sort direction, operator, keyword, or arbitrary SQL fragment. A parameter represents a value, not syntax. This is why a query such as ORDER BY @column does not mean “sort by the column whose name is in the parameter.”

8. Mandatory lab: prove safe values, exploit the broken version, then repair it

1. Use the disposable SQLite ServiceHub database and seed work orders in at least two tenants.
2. Run the LINQ search with a normal term and capture ToQueryString() plus metadata-only command interception.
3. Run the deliberately vulnerable FromSqlRaw example only against the disposable lab database using the supplied attack string; record how the SQL text changes.
4. Replace it with interpolated FromSql; verify the attack string is now a parameter value and returns only literal matches.
5. Verify tenant isolation independently: an authorized tenant query must never rely on injection protection as its authorization mechanism.
6. Keep sensitive-data logging disabled and delete/reset the disposable database.

9. Production judgment and bridge

Prefer LINQ when it expresses the query clearly; use interpolated FromSql/SqlQuery when SQL is the right tool; reserve raw variants for cases where the SQL structure itself must be dynamic and every inserted fragment is trusted or strictly allow-listed. Parameterization is foundational, but it is only one control. Lesson 2 treats dynamic sorting/filtering and query shape as a broader injection, data-exposure, and denial-of-service surface.

Authoritative references

• SQL queries in EF Core — official FromSql/FromSqlRaw/SqlQuery guidance, parameterization, dynamic identifiers and warnings
• EF Core simple logging — logging options and sensitive-data implications
• EF Core interceptors — command interception for observing SQL and parameter metadata
• Global query filters — tenant and soft-delete filters, bypass and limitations
• OWASP SQL Injection Prevention Cheat Sheet — defense-in-depth guidance for parameters, allow-lists and least privilege
• SQLite provider limitations — provider-specific behavior for the mandatory free local lab