How to parameterize concatenated string queries in Entity Framework Core

Question

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!

James · Accepted Answer · 2019-03-09 10:40:44Z

0

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:~~

~~https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.parameters?view=netframework-4.7.2#System_Data_SqlClient_SqlCommand_Parameters~~

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.

edited Mar 9, 2019 at 10:40

answered Mar 9, 2019 at 5:11

James

1274 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Bishal Sharma Over a year ago

Thanks. I can assure you that the code _context.Database.ExecuteSqlCommandAsync($"select * from table where id = {myid}"); is safe, maybe not best practice but its a FormattableString class not a normal string and therefore is parameterized by Entity Framework. The problem is actually with concatenation - when I concatenate, as shown in my example, I can no longer pass it as a FormattableString to the ExecuteSQLCommandAsync method. Do you have any suggestions on how I can make that happen?

James Over a year ago

Create your string with a StringBuilder (for performance) and instead of directly replacing in {i} and {i+1} replace in something like @i_0. Then create a SqlCommand from the string and run a loop that adds parameters to replace each of the values in the parameterised query.

Ivan Stoev Over a year ago

Looks like you've missed EF Core tag. The first example is perfectly valid for EF Core SQL commands because EF Core treats interpolated strings specially and binds parameter for each placeholder.

James Over a year ago

That's some very sneaky implicit behaviour. Accidentally using it in previous versions of EF seems like an easy trap to fall into.

Bishal Sharma Over a year ago

Thanks but I am not sure how I can concatenate the string while still being able to parameterize it(because FormattableString class doesn't support concatenation), unless I do some reflection... is there a way?

|

Collectives™ on Stack Overflow

How to parameterize concatenated string queries in Entity Framework Core

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related