current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

I'm executing some SQL using EF with the .SqlQuery(string sql) command.

I want to make sure my sql string is completely sanitised, so the logic approach was to use an SqlCommand object with parameters to build it.

However I don't want to execute it using the SqlCommand, I just want the SqlCommand to spit out a string that I can plug into my EF .SqlQuery(...) call.

Is there a way of doing this, or another method of ensuring my .SqlQuery won't result in injection?

share|improve this question
add comment

2 Answers

up vote 8 down vote accepted

Doesn't EF support that out of the box?

You should be able to call SqlQuery with parameters, so it will take care of SQL injection etc., just like SqlCommand does:

var tests = context.Database.SqlQuery<Test>(
    @"SELECT Id, Name FROM tests where Name={0}", "TestName");

or..

var tests = context.Database.SqlQuery<Test>(
    @"SELECT Id, Name FROM tests where Name=@name", 
    new SqlParameter("@name", "TestName"));
share|improve this answer
 
Facepalm. So it does! –  SLC Jul 25 '13 at 14:43
 
I can't get this to work. I've tried both methods above and I just get the error "Must declare the table variable "@name". Infuriating there is no documentation on it –  SLC Jul 25 '13 at 15:14
 
Are you trying to pass the table name as a parameter as well? That is not going to work. See here. Do you really need the table name to be variable? –  Peter Hansen Jul 25 '13 at 15:42
1  
I like the first syntax as it is very clean and works a lot like string.Format so it's easy to understand. Plus, under the hood it uses parameters so sql injection is not an issue. –  Chris Dunaway Jul 25 '13 at 16:18
add comment
up vote 0 down vote

Get the CommandText property of the SqlCommand.

string query = cmd.CommandText;

foreach (SqlParameter p in cmd.Parameters)
{
    query = query.Replace(p.ParameterName, p.Value.ToString());
}
share|improve this answer
 
Tried that, but the command text only gives me back my original string - it doesn't put the parameters into it: eg SELECT * FROM @tableName WHERE @propertyName = @searchQuery –  SLC Jul 25 '13 at 14:24
 
Re: Edit - is that not the same as simply using a stringbuilder? - Just tried it and it is. It doesn't sanitise or anything. –  SLC Jul 25 '13 at 14:27
 
I believe(but might be wrong) that the SqlCommand object will protect you. –  jyparask Jul 25 '13 at 14:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.