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?

Richard · Accepted Answer · 2016-04-29 15:48:21Z

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, ….

answered Apr 29, 2016 at 15:48

Richard

110k21 gold badges214 silver badges279 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

On The Net Again Over a year ago

Can you expand on the first part of your answer? What do you mean by 'whilelist aColumn?

Richard Over a year ago

@DaBest Whitelist column: fail if aColumn is not an exact match in your list of known columns: if (!safeColumList.Contains(aColumn)) throw new SecurityException("Hacker!!");.

Ilya Chumakov · Accepted Answer · 2016-04-29 15:48:37Z

0

Right way to prevent SQL injection is to use SqlParameter and SqlQuery<T>:

var parameter = new SqlParameter("@title", value);
var result = context.Database.SqlQuery<Book>("SELECT * FROM Books WHERE Title LIKE @title", parameter);

http://ignoringthevoices.blogspot.ru/2013/07/sql-injection-with-entity-framework-5.html

answered Apr 29, 2016 at 15:48

Ilya Chumakov

25.4k10 gold badges99 silver badges124 bronze badges

Collectives™ on Stack Overflow

Making raw SQL safe in Entity Framework

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related