dotnet / dotnet/efcore

Make it easier to get SQL literals to use in MigrationsBuilder.Sql

Open
#28,867 5 comments 0 reactions 0 assignees View on GitHub
area-migrations customer-reported needs-design
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

`MigrationsBuilder.Sql` only accepts raw SQL, with no way to provide parameters. This means values have to be hard-coded in the query, which goes against best practice. Sure, the input isn't going to be user-provided, so there's no actual risk of SQL injection, but it's easy to write invalid SQL, especially if the values are not directly in the code, e.g. something like this:

```
var json = File.ReadAllText("somefile.json");
migrationBuild.Sql($"UPDATE MyTable SET Json = '{json}' WHERE Id = 123");
```

If the JSON contains the `'` character, the generated SQL will be invalid. Of course, it's the caller's responsibility to escape the value appropriately, but it's inconvenient.

If the `Sql` method supported parameterized queries, it could be done more cleanly, e.g.:

```
migrationBuild.Sql("UPDATE MyTable SET Json = @json WHERE Id = @id", new { id = 123, json });
```

---

Alternatively, if it's not practical to support actual parameterized queries (and from what I saw in the code, it might not be trivial), it would be possible to add an overload of `Sql` that accepts a `FormattableString` instead of a string, and takes care of escaping the values. (in fact, this would probably be a breaking change, as calls that pass an interpolated string would suddenly be calling a different overload with different behavior, so I guess it would have to be a method with a different name)

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.