Dynamic Column Updates in EF Core Without Hand-Rolling SQ...

Sometimes you genuinely need the set of columns to update to be data, not code. An operator maps configuration fields to database columns, and you want to honor that mapping without redeploying every time it changes. The naive solution — build an UPDATE string from those column names — is also one of the easiest ways to hand-write a SQL injection vulnerability. This is how to get the flexibility without the hole.

We'll build it up in three layers: make it work, make it safe, then count the cost.

Layer 1: The dynamic update, the wrong way

The tempting version concatenates column names into SQL:

// DO NOT do this.
var sql = $"UPDATE products SET {columnName} = {value} WHERE id = {id}";

If columnName comes from configuration that an operator can edit, you've just made your schema writable by whoever controls that config. A value of name = 'x'; DROP TABLE products; -- is now your problem. Even "trusted" config is an injection surface the moment it flows into a SQL string.

Layer 2: The same feature with EF.Property

EF Core's ExecuteUpdateAsync lets you set a property by name without ever building SQL yourself. EF.Property<T> takes the property name as a string, and EF parameterizes the value and validates the property against the model:

await db.Products
    .Where(p => p.Id == id)
    .ExecuteUpdateAsync(setters => setters
        .SetProperty(p => EF.Property<float?>(p, columnName), value));

This is already a different security posture: the value is a parameter, not interpolated text, and EF will throw rather than emit SQL if columnName isn't a real mapped property. But "EF will throw" is a runtime backstop, not a policy. We want to reject bad names before they reach the database, fail closed, and control exactly which columns are writable.

Layer 3: Reflection as a whitelist

The guard is to validate every incoming column name against the entity's actual properties, using reflection, and to keep an explicit blacklist of fields that must never be touched dynamically:

private static readonly HashSet<string> Forbidden =
    new(StringComparer.Ordinal) { "Id", "CustomerId", "CreatedAt" };

private static readonly HashSet<string> Allowed =
    typeof(Product)
        .GetProperties(BindingFlags.Public | BindingFlags.Instance)
        .Select(p => p.Name)
        .Where(name => !Forbidden.Contains(name))
        .ToHashSet(StringComparer.Ordinal);

public async Task ApplyAsync(int id, IReadOnlyDictionary<string, float?> updates)
{
    foreach (var key in updates.Keys)
        if (!Allowed.Contains(key))
            throw new InvalidOperationException($"Column '{key}' is not updatable.");

    await using var tx = await db.Database.BeginTransactionAsync();
    var query = db.Products.Where(p => p.Id == id);

    foreach (var (name, value) in updates)
        await query.ExecuteUpdateAsync(s =>
            s.SetProperty(p => EF.Property<float?>(p, name), value));

    await tx.CommitAsync();
}

The important properties of this design:

Whitelist, not blacklist, as the primary control. The set of allowed names is derived from the type itself, so it can't drift out of sync with the schema. The blacklist only subtracts the sensitive few.
Fail closed. An unknown column raises before any database call, and the transaction means a bad item rolls everything back rather than half-applying.
No SQL is ever constructed from input. The column name only ever indexes into a validated set and is handed to EF as a model property reference.

The cost, stated honestly

This isn't free. You pay reflection and a dictionary lookup per field, and one ExecuteUpdateAsync per column rather than one combined statement. For a handful of configurable fields on a record, that's nothing. For a hot path updating dozens of columns across millions of rows, you'd cache the allowed set (as above, it's static) and consider batching. Measure before you optimize, but know the trade is there.

The principle to carry off

Dynamic behavior driven by config is fine. Dynamic SQL text built from config is the danger. The fix isn't to ban the feature — it's to make sure every externally-influenced identifier is validated against a server-side whitelist that the user can't expand, and to let the ORM parameterize values so you never assemble a query string by hand. Reflection over your own type is the cleanest source for that whitelist, because the schema is the source of truth.

推荐订阅源