Least privilege limits a database identity to the permissions required by the application—no more. If an attacker exploits SQL injection, restricted credentials can limit damage such as dropping tables, reading schemas, or extracting data. section 9.4.1
Apply the principle alongside query safety:
- Give each application connection string only the database and operations it needs, such as read-only access to one schema. section 9.4.1
- Use SQL parameters so attacker-controlled titles remain values rather than executable SQL. section 9.4.1
- Inspect generated SQL, because EF Core queries can differ by version and query shape. section 9.4.1
Limiting an injected query's blast radius
A title containing `atitle'); DELETE FROM Books; --` can alter an unsafe query. Parameterized queries prevent the input from becoming SQL, while a least-privileged connection can additionally prevent destructive operations if an injection reaches the database. section 9.4.1
Listing 9.6 LogSqlInterceptor logging SQL generated by EF Core
using System.Data.Common; #1
using Microsoft.EntityFrameworkCore.Diagnostics; #2
using Xunit.Abstractions; #3
namespace ManningBooksApi.Tests;
public class LogSqlInterceptor : DbCommandInterceptor
{
private readonly ITestOutputHelper _testOutput; #4
public LogSqlInterceptor(ITestOutputHelper testOutput)
=> _testOutput = testOutput;
public override
ValueTask<InterceptionResult<DbDataReader>> #5
ReaderExecutingAsync( #6
DbCommand command,
CommandEventData eventData,
InterceptionResult<DbDataReader> result,
CancellationToken cancelToken)
{
_testOutput.WriteLine(command.CommandText.Trim()); #7
return base.ReaderExecutingAsync( #8
command, eventData, result, cancelToken);
}
}
Use the SQL interceptor listing to inspect commands produced by EF Core and verify whether application queries use parameters. section 9.4.1
