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
- the proc only reads data,
- the operating context has access to other resources and methods, including
DELETEon sensitive tables (e.g.
- the proc's signature cannot be changed
- 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.