Recently I wrote a post about executing raw SQL scripts in Entity Framework Core 5: Executing raw SQL with Entity Framework Core 5. One of the readers noticed that I did a big mistake when passing parameters. Let’s take a closed look.

I had code like this:

    [HttpPost("UpdateProfiles")]
    public async Task<IActionResult> UpdateProfiles([FromBody] int minimalProfileId = 0)
    {
        await primeDbContext.Database.ExecuteSqlRawAsync(
            $"UPDATE Profiles SET Country = 'Poland' WHERE LEFT(TelNo, 2) = '48' AND Id > {minimalProfileId}");

        return Ok();
    }

This method updates profiles Country to Poland, where the phone number starts with 48, and an Id is higher than provided. Notice that I used ExecuteSqlRawAsync and provided an interpolated string, where I pass minimalProfileId. So where’s the catch?

When passing parameters to SQL you have to be super cautious. Especially when you’re passing a user-provided data, you are exposed to SQL injection attack. In order to avoid that, you should use FromSqlInterpolated or ExecuteSqlInterpolated methods. Changes to the code are minimal:

    [HttpPost("UpdateProfiles")]
    public async Task<IActionResult> UpdateProfiles([FromBody] int minimalProfileId = 0)
    {
        await primeDbContext.Database.ExecuteSqlInterpolatedAsync(
            $"UPDATE Profiles SET Country = 'Poland' WHERE LEFT(TelNo, 2) = '48' AND Id > {minimalProfileId}");

        return Ok();
    }

Nice huh? I can still pass an interpolated string, but with ExecuteSqlInterpolatedAsync I’m safe. Using this method allows parameters to be passed separately, causing .Net Core to check them for invalid characters or expressions. You can read more about it on the Microsoft website.

Hope you liked this post, stay safe 🙂

All code posted here can be found on my GitHub account. Enjoy!