5

Generally I know that Linq to SQL is safe for SQL Injections because it is using SqlParameter (as explained here and also here).

But how does it look like for contains:

StreetRepository.Streets.Where(w => w.Streetname.Contains("Road"))

If I log the queries in SQL Server directly, I can see that following query is used:

SELECT [Extent1].[Id] AS [Id], [Extent1].[Streetname] AS [Streetname] 
FROM [dbo].[Streets] AS [Extent1]  
WHERE [Extent1].[Streetname] LIKE N'%Road%'

As we can see it is not using parameters for this query. If I'm using following command:

StreetRepository.Streets.Where(w => w.Streetname.Contains("Road' OR 1=1"))

I get:

SELECT [Extent1].[Id] AS [Id], [Extent1].[Streetname] AS [Streetname] 
FROM [dbo].[Streets] AS [Extent1]  
WHERE [Extent1].[Streetname] LIKE N'%Road'' OR 1=1%'

In this case it is escaped by a double ''.

But is this safe enough for all attacks? Can I use contains without worries? If not what can I use instead of contains?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Phoniex
  • 171
  • 9
  • see this link https://stackoverflow.com/questions/473173/will-using-linq-to-sql-help-prevent-sql-injection – Meysam Asadi Feb 04 '21 at 11:06
  • @meysamasadi have you read any line of my question? Your link doesn't answer any of my questions. – Phoniex Feb 04 '21 at 11:12
  • I know what you are saying. But linq reduces the risk of injection. i tested Contains("Road' OR 1=1") It was safe – Meysam Asadi Feb 04 '21 at 11:26
  • 1
    Does this answer your question? [Will using LINQ to SQL help prevent SQL injection](https://stackoverflow.com/questions/473173/will-using-linq-to-sql-help-prevent-sql-injection) – Drag and Drop Feb 04 '21 at 12:22
  • this is the same link as @meysamasadi posted... as described in all the links (posted in my question and yours) they are speaking about that parameters are used, which are safe by default. As you can see in my question the resulting SQL command doesn't have any parameters ==> because of that you link doesn't help, there is no explaination about safety when no parameters are used. Have look at accepted anwers, it explains that strings are escaped safely by LINQ. – Phoniex Feb 04 '21 at 12:35

1 Answers1

2

Parameters is not only way to protect from SQL Injection. LINQ to SQL knows how to properly escape strings. So do not worry, everything will be ok.

Anyway if you prefer parameters, just put string value into local variable:

var streetName = "Road";
StreetRepository.Streets.Where(w => w.Streetname.Contains(streetName));
Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32