6

I'm a big fan of using named parameters instead of string-based parameter injection. It's type-safe and safe against most forms of SQL injection. In old ADO.NET, I would create a SqlCommand object and a bunch of SqlParameters for my query.

var sSQL = "select * from Users where Name = @Name";
var cmd = new SqlCommand(conn, sSQL);
cmd.Parameters.AddWithValue("@Name", "Bob");
cmd.ExecuteReader();

Now, in Entity Framework, it appears (on this link) to have regressed to a simple String.Format statement and string injection again: (simplified for discussion)

MyRepository.Users.SqlQuery("Select * from Users where Name = {0}", "Bob");

Is there a way to use named parameters with the Entity Framework DbSqlQuery class?

Eric Falsken
  • 4,796
  • 3
  • 28
  • 46

2 Answers2

4
var param = new ObjectParameter(":p0", "Bob");
MyRepository.Users.SqlQuery("Select * from Users where Name = :p0", param);
gdoron
  • 147,333
  • 58
  • 291
  • 367
  • Where is that documented? And what name formats are allowable in the SQL Body? Can it not support the @name style? – Eric Falsken Oct 17 '12 at 16:31
  • @EricFalsken. You can see in [here in MSDN](http://msdn.microsoft.com/en-us/library/system.data.objects.objectparameter.aspx), **this \@name style are supported**. – gdoron Oct 17 '12 at 18:23
  • This totally doesn't work for me, it is not documented, and you just get `The specified parameter name ':p0' is not valid. Parameter names must begin with a letter and can only contain letters, numbers, and underscores.` – NibblyPig Jul 25 '13 at 15:19
3

Since I can't comment, I'm fixing the other answer:

var param = new ObjectParameter("p0", "Bob");
MyRepository.Users.SqlQuery("Select * from Users where Name = :p0", param);

You don't have to put a colon on the name when instantiating an ObjectParameter. That's why SLC got the error he mentioned in his comment.

Brian Hudell
  • 77
  • 1
  • 7