How to construct a raw SQL Query in EF Core with dynamic number of parameters

Question

I am trying to dynamically construct a raw SQL query that will have X number of conditions. I am working from the info on this page: https://docs.microsoft.com/en-us/ef/core/querying/raw-sql

Currently I have something similar to this:

String rawQuery = "SELECT * FROM ItemsTable WHERE ";

foreach (f in FilterList) {
  rawQuery = rawQuery + String.Format(f.condition, f.userInput);
  // f.condition is something like "Name LIKE {0}"
}

var filteredItems = context.ItemsTable
  .FromSql(rawQuery)
  .ToList();

The problem is, my parameters are not being substituted in using .FromSql(), so I am vulnerable to SQL injection attacks.

Is there a way to use .FromSql() for this task?

OR, Is there another way I can protect against SQL injection?

Answer 1

You can make the query parameterized, build a list of SqlParameters, and then pass the query and the parameters into FromSql():

var rawQuery = new StringBuilder("SELECT * FROM ItemsTable WHERE ");
var sqlParameters = new List<SqlParameter>();

foreach (var f in FilterList) {
  var parameterName = $"@p{FilterList.IndexOf(f)}";
  var parameterizedCondition = string.Format(f.condition, parameterName);
  // f.condition is something like "Name LIKE {0}"

  rawQuery.Append(parameterizedCondition);
  sqlParameters.Add(new SqlParameter(parameterName, f.userInput));
}

var filteredItems = context.ItemsTable
  .FromSql(rawQuery.ToString(), sqlParameters)
  .ToList();