How to parameterize concatenated string queries in Entity Framework Core c# entity-framework-core sql


In Entity Framework Core, I can parameterize an SQL query like so:

_context.Database.ExecuteSqlCommandAsync($"select * from table where id = {myid}");

where the SQL query string is a FormattableString.

I need to run a SQL update for around 100 rows at a go and when I do using Linq, This makes 100 calls to the database, when I could easily do this using a SQL statement something like

UPDATE entity 
SET column = CASE .....

in a single call. But I am not sure how to go about doing this for concatenated strings.

For example:

string sqlQuery = "UPDATE entity SET column = CASE " 

for(int i = 0; i < 10; i++){
    sqlQuery += "WHEN column2 = i THEN i + 1 ";

sqlQuery += "WHERE id IN (1,2,3,4,5,6,7,8,9,10)";

await _context.Database.ExecuteSqlCommandAsync(sqlQuery);

How can I sanitize or parameterize this query? Any help will be appreciated!

3/9/2019 6:54:19 AM

Popular Answer

Your first example is a bad way to parameterise your query as it opens you up to SQL injection: The interpolated strings feature in C# is not set up specifically for SQL commands; it's intended to have a variety of uses and thus does not implement SQL sanitisation.

You should always use the SqlCommand class and its Parameters property to ensure that your parameters are properly sanitised:

Update: The interpolated string is implicitly treated as a SQL command and the interpolands sanitised when using that specific constructor in EF Core.

However, in this case, you will need to build your string using @ parameter notation and then substitute the parameters using SqlCommand to ensure they are sanitised, since you can't represent the query as a single template literal.

Also, if you do ever need to concatenate a large number of strings, += is a very inefficient way of doing so, you should use StringBuilder instead for speed.

3/9/2019 10:40:44 AM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow