Sanitize sql input parameters using Entity Framework

Question

I am using Entity Framework EDMX which automatically generates the needed functions i need for stored procedure executions as part of an auto generated class based on DBContext class.

for example, this is an auto generated function to execute stored procedure:

public virtual ObjectResult<Company> GetCompanies(string companyName)
    {
        var companyNameParameter = companyName != null ?
            new ObjectParameter("CompanyName", companyName) :
            new ObjectParameter("CompanyName", typeof(string));

        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Company>("GetCompanies", companyNameParameter);
    }

Since i am using dynamic SQL inside some of the stored procedure, i would like to override ExecuteFunction and use "Custom ExecuteFunction" function to add some sanitize functions there which will loop over each parameter and sanitize it. i am wondering, maybe i can create a custom ObjectContext class and use it somehow?

How can i do it in a way that i will still be able to use the EDMX Entity framework auto generates functions?

i know that there is some solutions to sanitize the parameters inside the SP but i still want to learn how can i use custom ExecuteFunction function or custom ObjectContext together with EDMX.

you shouldn't have to sanitize it ... if the problem is that you're concatenating the values and using EXEC (@sql) - then: don't do that - you should be using EXEC sp_executesql @sql, N'{args definition}', @arg0, @arg1, ... - which allows you to fully parameterize dynamic SQL; more context: learn.microsoft.com/en-us/sql/relational-databases/… — Marc Gravell
– Marc Gravell, Commented Nov 23, 2017 at 13:15
@MarcGravell i know that but i still want to learn how can i use custom ExecuteFunction function or custom ObjectContext together with EDMX. — Omtechguy
– Omtechguy, Commented Nov 23, 2017 at 13:19

David Browne - Microsoft · Accepted Answer · 2017-11-23 14:07:23Z

1

How can i do it in a way that i will still be able to use the EDMX Entity framework auto generates functions?

Since the generated functions are virtual, you can override them in a subclass.

eg

public class MyDbContext2 : MyDbContext
{
    public override ObjectResult<Company> GetCompanies(string companyName)
    {
       Sanatize(companyname);
       return base.GetCompanies(companyName);
    }
//. . .
}

answered Nov 23, 2017 at 14:07

David Browne - Microsoft

90.8k7 gold badges53 silver badges87 bronze badges

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

2 Comments

Omtechguy Over a year ago

In that way, i need to do this to every function that needs to be sanitized in MyDBContext. Is there an option to override somehow the ExecuteFunction function by inherit from ObjectContext?

David Browne - Microsoft Over a year ago

AFAIK no. And you only need to do this for the procedures where you are using dynamic SQL and not sanitizing the input parameters in the stored procedure itself.

Collectives™ on Stack Overflow

Sanitize sql input parameters using Entity Framework

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related