6

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?

NibblyPig
  • 51,118
  • 72
  • 200
  • 356

2 Answers2

12

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"));
Peter Hansen
  • 8,807
  • 1
  • 36
  • 44
  • Facepalm. So it does! – NibblyPig 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 – NibblyPig 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.](http://stackoverflow.com/questions/14003241/must-declare-the-table-variable-table) 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
0

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());
}
Giannis Paraskevopoulos
  • 18,261
  • 1
  • 49
  • 69
  • 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` – NibblyPig 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. – NibblyPig Jul 25 '13 at 14:27
  • I believe(but might be wrong) that the SqlCommand object will protect you. – Giannis Paraskevopoulos Jul 25 '13 at 14:33