CODEWITHSUNDEEP
sqlasp.net-mvcentity-frameworksecurityentity-framework-5
Omtechguy
Omtechguy

Reputation: 3651

SQL Injection protect using MVC - The Stored Procedures included Dynamic SQL

One of my clients using Dynamic SQL as part of the stored procedures. they don't want to change this. I am building MVC Website that should use that risky stored procedures. therefore, using parameters with Entity Framework will not solve the problem.

When i used the old and good SQL helper, i checked all the parameters inside the ExecuteQuery function and tried to find risky keywords. but now, when i'm using the native .NET Entity Framework 5 i have no shared function i can check there for that.

Using validators for every specific field is not good for me. is there an option to make overadding function for the Entity Framework part which execute the SP or Any other ideas how to solve that problem?

Upvotes: 1

Views: 646

Answers (1)

SilverlightFox
SilverlightFox

Reputation: 33578

You do not say whether the dynamic SQL within the stored procedures uses parameters or not. Assuming not, the best solution would be to encode strings for single quotes at the point of query execution

e.g. create a methodstring EncodeSqlString(string s) { return s.replace("'", "''"); }

Then call this method

cmd.CommandText = "SP_FOO";
cmd.CommandType = CommandType.StoredProcedure;
EntityParameter param = new EntityParameter();
param.Value = EncodeSqlString(myString);
param.ParameterName = "MyParam";
cmd.Parameters.Add(param);

This would be the safest way as you are only encoding the string values as passed to the stored procedure, you are not using the values elsewhere and in contexts where the quote encoding isn't appropriate, and you will be mitigating the risk of truncation (as long as there is no truncation happening within the SPs). This will also only work correctly if the SPs only use the values for constructing SQL queries - if they do anything else with them then this probably isn't the way to go.

Only pass string values into this method. For other, non-quoted types you should ensure that they are the correct type before passing them into the parameter. e.g. for an int

  string number = Request.QueryString["Number"];
    if (int.TryParse(number, out myInt))
    {
      cmd.CommandText = "SP_BAR";
      cmd.CommandType = CommandType.StoredProcedure;
      EntityParameter param = new EntityParameter();
      param.Value = myInt;
      param.ParameterName = "MyParam";
      cmd.Parameters.Add(param);
    }
    else
    {
      // handle appropriately but do not use value
    }

Upvotes: 1

Related Questions