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
end
This is how you call it.
exec FindAuthorISBN13 @ISBN=9781254872103
exec FindAuthorISBN13 9781254872103
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.
