Sql/Dapper: How do perform LIKE in WHERE clause for array of values?

Question

I have a table with a lot of employees in it, every person has a Name column with their full name.

I then want to do a query similar to this when searching for people:

SELECT * FROM Employees WHERE Name LIKE '%' + @value1 + '%' AND Name LIKE '%' + @value2 +'%' AND so forth...

for an arbitrary array of values.

My Dapper code would look something like this:

public IEnumerable<Employee> Search(string[] words)
{
  using var connection = CreateConnection();
  connection.Query<Employee>("SELECT * etc.", words);
}

Is there ANY way to do this with SQL without resorting to string concatenation, and the risk of SQL Injection attacks that follows?

Caveat: I don't know how Dapper actually passes an array to the query, which limits my creative ideas for working around this :-D

And also: Changing the Table structure is, unfortunately, out of the question. And I'd rather avoid fetching every single person into .Net memory and doing the filtering there.

Answer 1

Is there ANY way to do this with SQL without resorting to string concatenation, and the risk of SQL Injection attacks that follows?

Because the set of where conditions is not fixed you will need to build the query dynamically. But that does not mean you cannot parameterise the query, you just build the parameter list alongside building the query. Each time a word from the list add to the condition and add a parameter.

As Dapper doesn't directly include anything that takes a collection of DbParameter, consider using ADO.NET to get an IDataReader and then Dappter's

IEnumerable<T> Parse<T>(this IDataReader reader)

for the mapping.

Such a builder would be very roughly

var n = 0;
for (criterion in cirteria) {
  var cond = $"{crition.column} like @p{n}";
  var p = new SqlPatameter($"@p{n}", $"%{crition.value}%";
  conditions.Add(cond);
  cmd.Parameters.Add(p);
}

var sql = "select whetever from table where " + String.Join(" and ", conditions);
cmd.CommandText = sql;
var reader  = await cmd.ExecuteReaderAsync();
var res = reader.Parse<TResult>();