17

I made the following stored procedure:

ALTER PROCEDURE usp_actorBirthdays (@nameString nvarchar(100), @actorgender nvarchar(100))
AS
SELECT ActorDOB, ActorName FROM tblActor
WHERE ActorName LIKE '%' + @nameString + '%'
AND ActorGender = @actorgender

Now, I tried doing something like this. Maybe I am doing this wrong, but I want to be sure that such a procedure can prevent any SQL Injection:

EXEC usp_actorBirthdays 'Tom', 'Male; DROP TABLE tblActor'

The image below shows the SQL above being executed in SSMS and results being displayed correctly instead of an error:

enter image description here

Btw, I added that part following the semicolon after the query was done executing. Then I executed it again, but when I checked to see if the table tblActor exists or not, it was still there. Am I doing something wrong? Or is this really injection-proof? I guess what I am trying to ask here also is that is a stored procedure like this safe? Thank you.

  • Have you tried this EXEC usp_actorBirthdays 'Tom', 'Male''; DROP TABLE tblActor' – MarmiK Apr 26 '17 at 7:09
35

This code works properly because it is:

  1. Parameterized, and
  2. 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 = '
          + @InputParam;

EXEC(@SQL);

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 sp_executesql:

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'SELECT fields FROM table WHERE field23 = @SomeDate_tmp';

EXEC sp_executesql
  @SQL,
  N'SomeDate_tmp DATETIME',
  @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 @InputParameter as 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?

  • 2
    Oh, thank you very much, this is a great answer. I understand now. I would really like to see the technique you mentioned at the end too where the attacker tries to fill up the input field with apostrophes, if you can find it. Thanks in advance. I am going to keep this open, in case you do not find it, I will choose this as the answer. – Ravi Nov 17 '15 at 3:29
  • 1
    @Ravi I found the link but it no longer gets to the article as they are all now archived. But I added some info and useful links and I am still trying to find the article within those archives. – Solomon Rutzky Nov 17 '15 at 4:03
  • 1
    Thank you srutzsky, I will read the OWASP article and the tests for injection. If I remember correctly, 'mutillidae', the vulnerable web app for security testing, has SQL injection that I had performed at college with the 'OR 1 = 1' string which in mutillidae resulted in me logging into the web app as the admin I think. That's when I was first introduced to SQL injection. – Ravi Nov 17 '15 at 14:51
  • 1
    I am not able to view the .chm file too, but thank you for this perfect answer and for all the helpful links including the one from stackoverflow and the one from OWASP. I read it today and learned a lot from this. – Ravi Nov 18 '15 at 5:16
2

The simple matter is that you're not confusing data with command at all. The values for the parameters are never treated as part of the command, and therefore are never executed.

I blogged about this at: http://blogs.lobsterpot.com.au/2015/02/10/sql-injection-the-golden-rule/

  • Thank you Rob, I have this one bookmarked for reading later in the day. – Ravi Nov 17 '15 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.