Making raw SQL safe in Entity Framework

Question

var retval = db.TestTable.SqlQuery("SELECT * FROM dbo.TestTable WHERE " + aColumn + " = '" + passedInValue + "'");

// normally when using parameters I would do something like this:
var valueParam = SqlParameter("aValue", passedInValues);
var retval = db.TestTable.SqlQuery("SELECT * FROM dbo.TestTable WHERE Column1 = @aValue", valueParam);
// NOTE: I would not do this at all. I know to use LINQ. But for this question, I'm concentrating on the issue of passing variables to a raw sql string.

But since both the column and value are "parameters" in:

var retval = db.TestTable.SqlQuery("SELECT * FROM dbo.TestTable WHERE " + aColumn + " = '" + passedInValue + "'");

, is there to prevent sql injection for both?

Answer 1

First: whilelist aColumn: this has to be added via string concatenation but you know what columns are in your database (or you can check using schema views).

Second: In entity framework – as you show – you can use parameters for values in the query. However, rather than creating SqlParameter instances you can pass the values and use @p0, @p1, ….