This code works properly because it is:
- Parameterized, and
- Not doing any Dynamic SQL
In order for SQL Injection to work, you have to build a query string (which you are not doing) and not translate single apostrophes (
') into escaped-apostrophes (
'') (those are escaped via the input parameters).
In your attempt to pass in a "compromised" value, the
'Male; DROP TABLE tblActor' string is just that, a plain-ol' string.
Now, if you were doing something along the lines of:
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'SELECT fields FROM table WHERE field23 = '
then that would be susceptible to SQL Injection because that query is not in the current, pre-parsed context; that query is just another string at the moment. So the value of
@InputParam could be
'2015-10-31'; SELECT * FROM PlainTextCreditCardInfo; and that might present a problem because that query would be rendered, and executed, as:
SELECT fields FROM table WHERE field23 = '2015-10-31'; SELECT * FROM PlainTextCreditCardInfo;
This is one (of several) major reason to use Stored Procedures: inherently more secure (well, as long as you don't circumvent that security by building queries like I showed above without validating the values of any parameters used). Though if you need to build Dynamic SQL, the preferred way is to parameterize that as well using
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'SELECT fields FROM table WHERE field23 = @SomeDate_tmp';
@SomeDate_tmp = @InputParam;
Using this approach, someone attempting to pass in
'2015-10-31'; SELECT * FROM PlainTextCreditCardInfo; for a
DATETIME input parameter would get an error when executing the Stored Procedure. Or even if the Stored Procedure accepted
NVARCHAR(100), it would have to convert to a
DATETIME in order to pass into that
sp_executesql call. And even if the parameter in the Dynamic SQL is a string type, coming into the Stored Procedure in the first place any single apostrophe would automatically get escaped to a double apostrophe.
There is a lesser known type of attack in which the attacker tries to fill up the input field with apostrophes such that a string inside of the Stored Procedure that would be used to construct the Dynamic SQL but which is declared too small can't fit everything and pushes out the ending apostrophe and somehow ends up with the correct number of apostrophes so as to no longer be "escaped" within the string. This is called SQL Truncation and was talked about in an MSDN magazine article titled "New SQL Truncation Attacks And How To Avoid Them" but the article is no longer online. It -- the November, 2006 edition of MSDN Magazine -- is only available in an archive file (in .chm format):
http://download.microsoft.com/download/3/a/7/3a7fa450-1f33-41f7-9e6d-3aa95b5a6aea/MSDNMagazineNovember2006en-us.chm I was able to download it and pull up the table of contents, which does show the article, but I can't seem to view it.
For more detailed information related to SQL Injection (covering various RDBMS's and scenarios), please see the following from the Open Web Application Security Project (OWASP):
Testing for SQL Injection
Related Stack Overflow answer on SQL Injection and SQL Truncation:
How safe is T-SQL after you replace the ' escape character?