Review raw SQL built from composed strings

Rule ND3128 Security

Matches methods that call an Entity Framework Core Raw entry point (FromSqlRaw(), ExecuteSqlRaw(), SqlQueryRaw()) or a Dapper Query / Execute member, while also building strings in the same body (String.Concat(), Format(), Join(), interpolation, StringBuilder). These members hand the SQL text to the database verbatim, so a composed fragment coming from a request, a file name or a configuration value is executed as SQL: SQL Injection https://en.wikipedia.org/wiki/SQL_injection

The rules Review commands vulnerable to SQL injection and Review data adapters vulnerable to SQL injection only cover ADO.NET commands, which modern code rarely uses directly. This rule covers the ORM path.

False positives are possible when every composed fragment is a constant or an internally generated identifier; review matches and suppress issues when needed.

This is CWE-89 SQL Injection (OWASP Top 10 A03 Injection).

Flagged Code
public List<Blog> FindBlogs(BloggingContext db, string name) {
   var sql = $"SELECT * FROM Blogs WHERE Name = '{name}'";  // SQL composed in the method
   return db.Blogs.FromSqlRaw(sql).ToList();  // flagged: the composed text runs verbatim
}
Fixed Code
public List<Blog> FindBlogs(BloggingContext db, string name) {
   // FromSql takes a FormattableString: each hole becomes a DbParameter
   return db.Blogs.FromSql($"SELECT * FROM Blogs WHERE Name = {name}").ToList();
}

How to fix

With Entity Framework Core use the interpolated siblings, which turn every hole into a DbParameter: FromSql($"... {value}"), ExecuteSql($"... {value}"), FromSqlInterpolated(...). With Dapper pass an anonymous parameter object: connection.Query<T>("... WHERE Id = @id", new { id }).

A table or column name cannot be parameterized: validate it against a whitelist of known names before interpolating it.

CQLinq source

This rule is written in CQLinq, C# LINQ over the NDepend code model. It ships with every new NDepend project: you can read it, adapt it, or use it as a starting point for your own custom rules.

// <Name>Review raw SQL built from composed strings</Name>
// <Id>ND3128:ReviewRawSQLBuiltFromComposedStrings</Id>

warnif count > 0

// Third-party members are part of the code model only when the application uses them.
// Only the *Raw* / Dapper entry points take a SQL string the caller assembled.
// The interpolated EF Core siblings (FromSql, FromSqlInterpolated, ExecuteSqlInterpolated)
// turn every hole into a DbParameter, so they are deliberately absent from this list.
let rawSqlMembers = ThirdParty.Methods.Where(m =>
   m.SimpleNameEqualsAny(
     "FromSqlRaw", "ExecuteSqlRaw", "ExecuteSqlRawAsync", "SqlQueryRaw",
     "ExecuteSqlCommand", "ExecuteSqlCommandAsync", "SqlQuery") ||

   // Dapper: Query/Execute overloads all take the SQL as a plain string
   (m.ParentType.FullName.StartsWith("Dapper.") && m.SimpleName.StartsWithAny("Query", "Execute")))
   .ToHashSetEx()

where rawSqlMembers.Any()

// Concatenation, formatting, interpolation or a builder: the SQL is assembled in the method.
let stringBuildingMembers = ThirdParty.Methods.Where(m =>
   (m.ParentType.FullName == "System.String" && m.SimpleNameEqualsAny("Concat", "Format", "Join", "Replace", "Insert")) ||
    m.ParentType.FullNameEqualsAny("System.Text.StringBuilder", "System.Runtime.CompilerServices.DefaultInterpolatedStringHandler"))
   .ToHashSetEx()

from m in Application.Methods.UsingAny(rawSqlMembers)
where m.MethodsCalled.Intersect(stringBuildingMembers).Any()

select new {
  m,
  rawSqlMembersUsed = m.MethodsCalled.Intersect(rawSqlMembers).ToArray(),
  Debt = 15.ToMinutes().ToDebt(),
  Severity = Severity.High
}

//<Expl>
// The *{0}* assembles a SQL string and passes it to *{1}*.
// If any fragment derives from user input, malicious SQL is executed:
// use parameters or the interpolated overloads instead.
//</Expl>

// <Desc>
// Matches methods that call an Entity Framework Core *Raw* entry point
// (*FromSqlRaw()*, *ExecuteSqlRaw()*, *SqlQueryRaw()*) or a Dapper
// *Query* / *Execute* member, while also building strings in the same body
// (*String.Concat()*, *Format()*, *Join()*, interpolation, *StringBuilder*).
// These members hand the SQL text to the database verbatim, so a composed
// fragment coming from a request, a file name or a configuration value is
// executed as SQL: **SQL Injection**
// https://en.wikipedia.org/wiki/SQL_injection
//
// The rules *Review commands vulnerable to SQL injection* and *Review data
// adapters vulnerable to SQL injection* only cover ADO.NET commands, which
// modern code rarely uses directly. This rule covers the ORM path.
//
// False positives are possible when every composed fragment is a constant
// or an internally generated identifier; review matches and suppress
// issues when needed.
//
// This is CWE-89 *SQL Injection* (OWASP Top 10 A03 Injection).
//
// **Flagged Code**
// ```csharp
// public List<Blog> FindBlogs(BloggingContext db, string name) {
//    var sql = $"SELECT * FROM Blogs WHERE Name = '{name}'";  // SQL composed in the method
//    return db.Blogs.FromSqlRaw(sql).ToList();  // flagged: the composed text runs verbatim
// }
// ```
//
// **Fixed Code**
// ```csharp
// public List<Blog> FindBlogs(BloggingContext db, string name) {
//    // FromSql takes a FormattableString: each hole becomes a DbParameter
//    return db.Blogs.FromSql($"SELECT * FROM Blogs WHERE Name = {name}").ToList();
// }
// ```
// </Desc>

// <HowToFix>
// With Entity Framework Core use the interpolated siblings, which turn
// every hole into a *DbParameter*: *FromSql($"... {value}")*,
// *ExecuteSql($"... {value}")*, *FromSqlInterpolated(...)*. With Dapper
// pass an anonymous parameter object:
// **connection.Query<T>("... WHERE Id = @id", new { id })**.
//
// A table or column name cannot be parameterized: validate it against a
// whitelist of known names before interpolating it.
// </HowToFix>

Open in the interactive Rules Explorer · All default rules

Security: all 39 items