(EFCore Best Practices) Stored Procedures and Secure Parameterization to Prevent SQL Injection attacks

Why Stored Procedures?

A stored procedure is a good tool to:

1. reduce round-trips to a database.
2. make efficient use of the database team who can write optimal queries in the SQL syntax.
3. make efficient use of the database server because too many complex joins, groups etc., might be needed in a typical real world application.
4. legacy projects often have many stored procedures already written into the database.

Execute a Stored Procedure without Parameters

So first of all let us learn how to execute a stored procedure that doesn't accept any parameters.

Suppose we have a stored procedure called GetAllItems that returns a list of items.

First of all you will have to add a model class to match the columns returned by this stored procedure. This has already been explained in detail in the previous tutorial, so I will not repeat it here.

Next you can run a raw query EXECUTE dbo.GetAllItems as you are seeing here.

var lis = context.MyRecords
                 .FromSqlRaw("EXECUTE dbo.GetAllItems")
                 .ToList();

Video Explanation (see it happen!)

Please watch the following youtube video:

Execute a Stored Procedure with Parameters

Let's next turn to stored procedures or queries that accept parameters. Firstly, the parameters have to be sanitized before they can be passed to the database engine. Secondly, the parameters must also not contain invalid characters that can cause the query to fail because of syntax conflicts.

Suppose we have to execute a stored procedure EXECUTE dbo.GetAllItems @s1. In this statement the name of the parameter is s1.

  
  // parameter is @s1 

  EXECUTE dbo.GetAllItems @s1
  
  

The wrong way of calling this stored procedure is to use string concatenation to construct the query statement. You should never do this because it is prone to sql injection attacks.

// WRONG WAY 

String value = "Shirts";

String q = "EXECUTE dbo.GetAllItems '" + value + "'" ;

var lis = context.MyRecords
                 .FromSqlRaw(q)
                 .ToList();

So how to solve this problem? There are two ways of solving this problem. The first is to use placeholders, and the second is to construct a DbParameter and pass it as the parameter value.

Using Placeholders

Now we shall examine the use of placeholders.

Suppose we have to execute a stored procedure EXECUTE dbo.GetAllItems @s1. In this statement the name of the parameter is s1.

The code for calling the stored procedure has been shown here.

String s1 = "Shirt";

var lis = context.MyRecords
                 .FromSqlRaw("EXECUTE dbo.GetAllItems {0}", s1)
                 .ToList();

// ef core converts string s1 to  
// DbParameter (SqlParameter) and substitutes at {0} 

First store the parameter value in a string variable s1. This value is passed through FromSqlRaw function by using the placeholder syntax. It looks very similar to String.Format, but it is not the same thing. The function FromSqlRaw creates a DbParameter object out of s1 and then inserts it correctly at the place where {0} has been used.

The above code could have been done with the string interpolation syntax as well. String interpolation syntax uses a dollar sign to replace the numeric placeholders by the identifiers.

In this code we have used $"EXECUTE dbo.GetAllItems {s1}". In this case also the value s1 is converted to a DbParameter and executed.

// with string interpolation syntax 

String s1 = "Shirt";

var lis = context.MyRecords
                 .FromSqlInterpolated($"EXECUTE dbo.GetAllItems {s1}")
                 .ToList();

// the function used is "FromSqlInterpolated" 
// it understands $ syntax and coverts s1 
// to DbParameter 

Using DbParameters

We can construct a DbParameter out of a value and then pass it to the FromSqlRaw function.

// using SqlParameter (DbParameter) 

var s1 = new SqlParameter ("s1", "Shirt");

var lis = context.MyRecords
                 .FromSqlRaw("EXECUTE dbo.GetAllItems @s1", s1)
                 .ToList();

In this code we have constructed a DbParameter of name s1. The value has been passed as the second argument. This syntax allows us to use various overloads of SqlParameter to specify direction of parameters, and data types etc. So we have a lot of possibility of fine-tuning the parameters.

Notice that the parameter is now passed by using sql placeholder @s1 in "EXECUTE dbo.GetAllItems @s1".

This is how we can execute stored procedures and pass parameters in a secure manner. Thanks!