CODEWITHSUNDEEP
c#sqlsql-serverlinq-to-sqlxquery
Sam
Sam

Reputation: 943

DataContext ExecuteQuery

string query = @"SELECT ColA, ColXML FROM TableT WHERE ColXML.exist('/SuperNode/Node/SubNode[.=({0})]') = 1";
string param = "''value1'',''value2'',''value3''";
string sQ = string.Format(query, param);

A: dbContext.ExecuteQuery(sQ);

B: dbContext.ExecuteQuery(query, param);

A executes and returns result but B doesn't.

Any reason for this? Also, does the param gets validated for common SQL injection patterns?

Thanks for any pointers!

Upvotes: 4

Views: 14529

Answers (2)

Jon
Jon

Reputation: 126

For anyone stumbling upon this via google as I did, ExecuteQuery does not simply pass the command and parameters to string.Format as that would create an injection vulnerability.

It replaces the {0}, {1}, ... in the command with "@p0", "@p1" etc, and then adds your parameters as parameters to the sql command. This can be confirmed by setting the .Log property on the data context to see the command actually executed.

So the OP's example doesn't work because f(x) where x = "a,b,c" is only equivalent to f(a,b,c) if we're doing a straightforward string substitution. If x is a "proper" SQL parameter then it doesn't work.

Upvotes: 2

Yván Ecarri
Yván Ecarri

Reputation: 1738

http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.executequery.aspx

You are trying to use the overloaded version of ExecuteQuery that receive parameters. Parameters must be passed as an Object array, for example:

object[] param = new Object[] { "value1", "value2", "value3" };

Anyway, your query receives only ONE parameter:

string query = @"SELECT ColA, ColXML FROM TableT WHERE ColXML.exist('/SuperNode/Node/SubNode[.=({0})]') = 1";

It seems that you want to pass a single parameter composed by three xml values. I am not an XQuery expert but you can try this:

object[] param = new Object[] { "''value1'', ''value2'', ''value3''" };
string query = @"SELECT ColA, ColXML FROM TableT WHERE ColXML.exist('/SuperNode/Node/SubNode[.=({0})]') = 1";

Upvotes: 3

Related Questions