2

I want to create queries using EF FromSqlInterpolated or FromSqlRaw that allows me to use Like clauses, but I don't know what is the right way to do it without opening the application to SqlInjection attacks. One first approach has took me to the following code 

var results = _context.Categories.FromSqlInterpolated(
$"Select * from Category where name like {"%" + partialName + "%"}");

First test worked fine, it returns results when providing expected strings, and returns nothing when i provide something like ';select * from Category Where name='Notes'--%'; Still I don't know much about SqlInjection, at least not enough to feel safe with the query shown before. Does someone know if the query is safe, or if there is a right way to do it? Thanks

J.J
  • 881
  • 16
  • 29
  • 2
    `FromSqlInterpolated` is supposed to parametrize your parameters property. From [the doc](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.relationalqueryableextensions.fromsqlinterpolated?view=efcore-3.1): Any interpolated parameter values you supply will automatically be converted to a DbParameter – ESG Feb 25 '20 at 01:24
  • @ESG so that means it's safe? I use logs to try to see the final Sql, but the result i get is `Select * from Category where name like @p0` which is not the final query – J.J Feb 25 '20 at 01:37
  • 1
    I guess the answer to my previous question is yes, once it's a parameter it's safe from https://stackoverflow.com/questions/23256298/why-does-using-parameterized-queries-or-entity-framework-prevent-sql-injection – J.J Feb 25 '20 at 01:49
  • Does this answer your question? [Use of SqlParameter in SQL LIKE clause not working](https://stackoverflow.com/questions/665129/use-of-sqlparameter-in-sql-like-clause-not-working) – Fabio Feb 25 '20 at 01:53
  • @Fabio I want to use the proposed syntax for EF Core. FromSqlRaw FromSqlInterpolated, since is the syntax that is currently suggested on the Microsoft documentation, with the only issue that the examples are based on simpler queries or stored procedures – J.J Feb 25 '20 at 01:57
  • 1
    @Juan, syntax remain same, regardless of which method you will use you need to wrap sql parameter or interpolated value with `%` characters to get it work. – Fabio Feb 25 '20 at 01:59
  • @Fabio I see your point, my bad. Still not sure it's worth deleting the question as duplicated, since more people may be filtering for similar terms as the one I proposed(since they are keywords in Microsoft documentation currently) – J.J Feb 25 '20 at 02:04

1 Answers1

6

From this document

The FromSqlInterpolated and ExecuteSqlInterpolated methods allow using string interpolation syntax in a way that protects against SQL injection attacks.

var results = _context.Categories.FromSqlInterpolated(
$"Select * from Category where name like {"%" + partialName + "%"}");

Or you can also change your query to Linq-to-Entity like this way

var results = _context.Categories.Where(p => p.name.Contains(partialName ));
Nguyễn Văn Phong
  • 13,506
  • 17
  • 39
  • 56
  • 1
    Yes, as I mentioned in the answer, **protects against SQL injection attacks** – Nguyễn Văn Phong Feb 25 '20 at 02:11
  • The second solution is not what I'm looking for, I appreciate the help, but the example I shared was a simplification of the real query/s. I want to learn the technique, not the specific query – J.J Feb 25 '20 at 02:12
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/208455/discussion-between-juan-and-phong). – J.J Feb 25 '20 at 02:27