EntitySQL and SQL injection

Question

I've got the following query string

"SELECT VALUE entity FROM Entities AS entity WHERE entity.Client_id
= 0 && entity.Name LIKE @searchvalue ORDER BY @sorting SKIP @skip LIMIT @limit"

with the following param replacement

query.Parameters.Add(new ObjectParameter("skip", start));
query.Parameters.Add(new ObjectParameter("limit", limit));
query.Parameters.Add(new ObjectParameter("searchvalue", searchValue + "%"));
query.Parameters.Add(new ObjectParameter("sorting", sortField + " " + sortDirection.ToUpper()));

But I always end up in the exception:

The key expression 'ORDER BY' must have at least one reference to the immediate input scope. Near ORDER BY clause item

I guess this happends cause query.Parameters.Add(...) wraps all in quotes? I also read this but for what benefit then do I need query.Parameters.Add(...) if nothing can happens? OK, the attacker may not start a new query but I guess he can manipulate the current?

Answer 1

Guess: The first thing I would try it to do something like this

SELECT VALUE entity FROM Entities AS entity WHERE entity.Client_id = 0 && entity.Name LIKE '@searchvalue' ORDER BY @sorting @sortorder SKIP @skip LIMIT @limit

query.Parameters.Add(new ObjectParameter("searchvalue", searchValue + "%"));
query.Parameters.Add(new ObjectParameter("sorting",   sortField ));
query.Parameters.Add(new ObjectParameter("sortorder", sortDirection));

In other words: move sorting order to seprated parameter.

EDIT

If this doesn't work use Query Builder to construct a query.

Look here for example.

Good luck.

Answer 2

Have tried removing the quotes @searchvalue, since you are using a parameterized query IMO, quotes are no longer required.

Instead of:

"SELECT VALUE entity FROM Entities AS entity WHERE entity.Client_id
= 0 && entity.Name LIKE '@searchvalue' ORDER BY @sorting SKIP @skip LIMIT @limit"

Try this:

"SELECT VALUE entity FROM Entities AS entity WHERE entity.Client_id
= 0 && entity.Name LIKE @searchvalue ORDER BY @sorting SKIP @skip LIMIT @limit"

Answer 3

You cant use parameters as replacement of column names.