Entity framework SqlQuery execute query with repeated parameter

Question

I'm having troubles trying to execute a SQL query with repeated parameters using entity framework.

The query is a keyword search, that looks in different tables, therefore using the same parameter many times. I'm using LIKE statements (yes, I know I should be using FULLTEXTSEARCH, but I don't have time for that right now).

I've tried all the syntax explained here: How to use DbContext.Database.SqlQuery<TElement>(sql, params) with stored procedure? EF Code First CTP5 and none of them make the query work (I get zero returned rows).

I even tried building a string array in runtime, with length equal to the number of times the parameter repeats in the query, and then populating all the elements of the array with the keyword search term. Then I passed that as the object[] parameters. Didn't work either.

The only thing that works is to make a search&replace that is obviously a bad idea because the parameter comes from a text input, and I'll be vulnerable to SQL injection attacks.

The working code (vulnerable to SQL injection attacks, but the query returns rows):

//not the real query, but just for you to have an idea
string query =
    "SELECT Field1, " +
    "       Field2 " +
    "FROM   Table1 " +
    "WHERE  UPPER(Field1) LIKE '%{0}%' " +
    "OR     UPPER(Field2) LIKE '%{0}%'";

//keywordSearchTerms is NOT sanitized
query = query.Replace("{0}", keywordSearchTerms.ToUpper());

List<ProjectViewModel> list = null;
using (var context = new MyContext())
{
    list = context.Database.SqlQuery<ProjectViewModel>(query, new object[] { }).ToList();
}
return list;

I'm using ASP.NET MVC 4, .NET 4.5, SQL Server 2008 and Entity Framework 5.

Any thoughts on how to make the SQLQuery<> method populate all the occurrences of the parameter in the query string? Thank you very much for your time.

Heavy JS · Accepted Answer · 2023-06-15 12:14:50Z

6

Try this:

string query =
    @"SELECT Field1,
          Field2 FROM Table1
      WHERE UPPER(Field1) LIKE '%' + @searchTerm + '%'
      OR UPPER(Field2) LIKE '%' + @searchTerm + '%'";
 
context
    .SqlQuery<ProjectViewModel>(query, new SqlParameter("@searchTerm", searchTerm))
    .ToList();

edited Jun 15, 2023 at 12:14

Heavy JS

751 silver badge8 bronze badges

answered Jul 29, 2013 at 11:07

serefbilge

1,7345 gold badges30 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jurica Smircic · Accepted Answer · 2013-04-30 09:35:19Z

1

You can use parameters in your query. Something like this

string query =
 "SELECT Field1, " +
 "       Field2 " +
 "FROM   Table1 " +
 "WHERE  UPPER(Field1) LIKE @searchTerm" +
 "OR     UPPER(Field2) LIKE @searchTerm";

        string search= string.Format("%{0}%", keywordSearchTerms);

        context.SqlQuery<ProjectViewModel>(query, new SqlParameter("@searchTerm", search)).ToList();

answered Apr 30, 2013 at 9:35

Jurica Smircic

6,5152 gold badges25 silver badges29 bronze badges

1 Comment

Juan Paredes Over a year ago

I tried this. It throws a SqlException: Must declare the scalar variable "@searchTerm". Thanks for the suggestion.

Cloudsan Chen · Accepted Answer · 2013-04-30 09:35:17Z

-2

how about try this

string query =
string.Format("SELECT Field1, Field2 FROM   Table1 WHERE  UPPER(Field1) LIKE '%{0}%' 
OR     UPPER(Field2) LIKE '%{0}%'",keywordSearchTerms.ToUpper());

context. Database.SqlQuery< ProjectViewModel >(query)

answered Apr 30, 2013 at 9:35

Cloudsan Chen

3291 silver badge10 bronze badges

5 Comments

Juan Paredes Over a year ago

I also tried this. In fact, I think it was one of my first attemps. It doesn't throw an exception, but the query returns zero results. Thanks.

Cloudsan Chen Over a year ago

Did you try to use the query in SQL Management Studio?

Cloudsan Chen Over a year ago

what's your keywordSearchTerms? is it a single word? or multiple words?

Juan Paredes Over a year ago

The query works. When I use search&replace on the query string, the method gives back results for certain searches I have prepared for testing. It also works on SQL Management studio. So is not the query that is wrong. What I think the problem is that the SqlQuery<T>(query, params) method does not substitute the parameter placeholders in the query string with the sanitized value. Finally I don't think is relevant the value of the keywordSearchTerms. The LIKE operator works with strings, regardless of single or multiple words, as far as I know. Thanks for your time.

Azhdeen Over a year ago

Since you're using string.Format to do the parameter replacement instead of SqlParameter, isn't this method susceptible to SQL injection?

Collectives™ on Stack Overflow

Entity framework SqlQuery execute query with repeated parameter

3 Answers 3

Comments

1 Comment

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related