Prevent SQL Injection Attacks Using SQL Server Stored Procedures

SQL injection is a very serious topic and there are numerous libraries and best practices to help you secure your connection between your app and your database. To reduce the ability of bad actors to take over your SQL requests you can use Stored Procedures (think of it as server-side code) vs client-side code. They are equivalent to function calls within other programming languages.

Anatomy of a Stored Procedure

Similar to function calls a stored procedure has a name and 0 or more parameters. The parameters are actually placeholder variables, so they are required to begin with an ampersand (@). Like variables they are also required to have their datatype defined. Let's build a very simple stored procedure that takes an ISBN and returns the author of the book.

create procedure FindAuthorISBN13
(
@ISBN bigint
)
as
begin
--fill in with code
end

This is how you call it.

--call by parameter. This is the preferred method
exec FindAuthorISBN13 @ISBN=9781254872103
--call by position
exec FindAuthorISBN13 9781254872103
--implicit conversion can be called by position or parameter
exec FindAuthorISBN13 '9781254872103'

Now if you tried to call it with something other than a valid big integer you will get an error. That is the first line of defense.

Now for the guts of the procedure:

 create procedure FindAuthorISBN13
(
@ISBN bigint
)
with execute as owner
as
begin
 select AuthorName as [Author Name]
   from TitleMetadata
  where ISBN13=@ISBN
end

As you can see you are using the variable to test for a value against a column. You aren't passing in a SQL statement to be run.

Now from C# this is how you would make the call:

....
var cmd = new SQLCommand(Proc, conn)
cmd.Parameters.AddWithValue("@ISBN",_isbn);
cmd.CommandType = CommandType.StoredProcedure;
using var reader = cmd.ExecuteReader();
reader.Read();
result=reader.GetFieldValue<string>(0);
......

There is no way a malicious actor can gain access to anything other than the result programmed in the procedure.