6

I have a query that looks like this:

IQueryable<Profile> profiles = from p in connection.Profiles
    where profile.Email.Contains(txtSearch)
    select p;

I know that when this is converted to SQL it uses a LIKE '%<value of txtSearch>%' but if txtSearch = "jon%gmail.com" it converts it to `LIKE '%jon~%gmail.com%'. The ~ escapes the % in the middle that is a wild card. How do I get around that? I need to be able to put wild cards into my LINQ to EF searches.

Ben Hoffman
  • 8,149
  • 8
  • 44
  • 71

1 Answers1

2

I'm not sure that this is possible directly with linq because you can call only basic string functions like Contains, StartsWith or EndsWith. It is possible with Entity SQL so you can combine these approaches. 

var query = new ObjectQuery<Profile>(
    @"SELECT VALUE p
      FROM CsdlContainerName.Profiles AS p
      WHERE p.Email LIKE '" + wildcardSearch + "'",
    context);

var result = query.AsQueryable().OrderByDescending(p => p.Name).ToList();

ESQL injection strikes back :)

Second version without injection vulnerability (I didn't try it but it should work):

var commandText =
    @"SELECT VALUE p
      FROM CsdlContainerName.Profiles AS p
      WHERE p.Email LIKE @search";

var query = new ObjectQuery<Profile>(commandText, context);
query.Parameters.Add(new ObjectParameter("search", wildcardSearch));

var result = query.AsQueryable().OrderByDescending(p => p.Name).ToList();
Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • The page I am creating is an admin page so I am not worried about a SQL injection attack but wouldn't the above method open me up to a SQL injection attack? – Ben Hoffman Mar 25 '11 at 13:27
  • I think it does but actually didn't try it (see my comment bellow the code snippet). But you can also use version with parameters - I will add it as second example. – Ladislav Mrnka Mar 25 '11 at 13:32