1

Considering a generic stored procedure that is subject to SQL injection:

CREATE PROC usp_badproc
    @sql NVARCHAR(8000)
AS BEGIN
    EXEC(@sql)
END

and operating under the assumptions that

  1. the proc only reads data,
  2. the operating context has access to other resources and methods, including UPDATE and DELETE on sensitive tables (e.g. dbo.Users or dbo.CreditCards),
  3. the proc's signature cannot be changed
  4. the proc's executing context cannot be changed

what are the pros and cons to enhancing this procedure as:

CREATE PROC usp_badproc
    @sql NVARCHAR(8000)
AS BEGIN
    BEGIN TRANSACTION
    EXEC(@sql)
    ROLLBACK TRANSACTION
END

That is, wrapping the execution in a transaction and always rolling it back.

I understand that in essence you are just pushing the problem down a level (e.g. the @sql now has to end with COMMIT TRANSACTION), but does it add any benefit, even from a reactive/logging perspective? Can the transaction be marked in a way to never be recoverable?

Just to be clear, I would never suggest this as a fix to a SQL injection risk, I'm just trying to understand it further and the layers involved.

  • Is this question out of academic interest or something you're actually doing? Clarifying this might help since I can see a lot of frame-challenging "just don't do this" answers coming in the near future. – Tophandour Jul 10 '18 at 19:41
  • 1
    Academic! Please nobody try this! – mlhDev Jul 10 '18 at 19:42
1

Sql Server has a log of all transactions so at least you could possibly have a record of what was run (unless the attacker also erased the transaction log). You may get an error message somewhere and as long as that log isn't erased you'll have that as well. Then again, you may have Sql Server set to implicit transactions so each statement gets its own transaction and gets logged anyway.

As far as cons, I don't think that this really accomplishes anything so your issue isn't fixed. More work and obfuscating, hiding, or creating the illusion of fixing a security issue is a pretty big con. Rolling back the transaction only matters until the attacker figures out that they just have to add COMMIT TRANSACTION to the end of their injection. At least when I tried it using the most obvious approach, I got an error when it hit ROLLBACK TRANSACTION so then it will jump to whatever error handling you have and who knows what it might skip if this was just one part of a process. A better crafted injection wouldn't generate an error, though.

Overall, I'm glad that this is just academic interest because I don't think you gain anything. You don't lose terribly much either but why go through the effort if you're not fixing the issue?

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.