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?

Not familiar with entity-sql, but my guess is yes, it thinks that, e.g. `fieldname asc` is the name of the field you want. Can you try with `... ORDER BY @sortfield @sortdir ...` and using two parms? — Mr Lister, Jan 31 '12 at 07:59

Tigran · Accepted Answer · 2012-01-31T08:40:11.200

3

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.

edited Jan 31 '12 at 08:40

answered Jan 31 '12 at 08:01

Tigran

61,654
8
86
123

that was my initial one... So no, that result in the same error – sra Jan 31 '12 at 08:05
@sra: ok, so probably it's a worst case, when you can not do this. But it should be possible to do with ObjectQuery builder. Look [here](http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/6b95ad32-6642-4ec7-8051-e0bdfe022eff/) – Tigran Jan 31 '12 at 08:11
that is the answer! You should copy that at the bottom of your answer. – sra Jan 31 '12 at 08:32

score 0 · Answer 2 · answered Jan 31 '12 at 08:15

0

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"

answered Jan 31 '12 at 08:15

jerjer

8,694
30
36

I've forgot these, you're absolutely right. But nonetheless that's not my error... I've fixed this in my post. But does parameterized query automatic quote? – sra Jan 31 '12 at 08:23
It will automatically apply the quotes if necessary (e.g. when working with strings). There's no need to wrap a parameter in quotes. – CadentOrange Jan 31 '12 at 08:43

score 0 · Answer 3 · answered Jan 31 '12 at 09:00

0

You cant use parameters as replacement of column names.

answered Jan 31 '12 at 09:00

Vitaliy Kalinin

1,791
12
20

EntitySQL and SQL injection

3 Answers3