SQL Rollback Hack

Ever seen an application display a message like "changes will be rolled back", particularly after a SQL operation? This may be a clue that access is controlled by the SQL transaction keyword ROLLBACK. Here's how to exploit it and persist your changes!

First, a little bit of background...(tl;dr: there's a cheatsheet at the bottom)

Normally, the ROLLBACK keyword is used for error-catching when performing a series of SQL statements. The syntax goes BEGIN [statement1, statement2, etc.] COMMIT. You can put a ROLLBACK in between any of those statements to undo all of the statements leading up to it if one happens to fail. For example, let's say you're deleting records from parent and child tables. You'd want to wrap both delete statements in a BEGIN/COMMIT, so that if, say, the parent record was deleted successfully but the child record was not (maybe some foreign key was violated or some other error), all changes to both records can be undone using ROLLBACK. This safely preserves the relationship of the tables, and prevents data from "breaking". No parent or child record is left an orphan in this case.

ROLLBACK is great, but should not be used for preventing a user from making changes to the database. It's not made for access control. Here's how to bypass it, assuming it's (most likely) using a BEGIN and ROLLBACK block around all the user input (whether that may be SQL injection, or whatever way you've found to run SQL).


Inject a "commit" to perform a simple update. If it gives an error about mismatching BEGIN and COMMIT statements, that's confirmation it's vulnerable and you've succeeded in changing the data. In this case, we're updating a sysadmin's encrypted password to be the same as our own password so we can take over the account:

UPDATE User SET Password = 'f233dfgxm8913=' WHERE Name = 'sysadmin'

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0


Success! But what about more complex statements, such as CREATE or ALTER? There will be error messages like the statement "must be first statement in query batch" or it's "not allowed in multi-statement transaction." Well, for the CREATE, wrapping the statement in an EXEC and then committing works. My guess is that this is because EXEC is designed to work within the transaction first, making the command technically "first". Either way, it works. But only if the COMMIT is added after.

EXEC('CREATE PROCEDURE Hacked(@uhoh VARCHAR(1)) AS BEGIN SELECT @@VERSION END') COMMIT;

As for the "multi-statement transaction" error, we can COMMIT first, and then wrap the statement in EXEC. But, only if the COMMIT is added before.

COMMIT EXEC('ALTER DATABASE clientsdb SET TRUSTWORTHY ON')


TL;DR Cheat Sheet:

1. To complete basic DELETE, INSERT, and UPDATE statements, use a COMMIT after your statement

2. To complete statements such as CREATE PROCEDURE that contain a BEGIN statement, use EXEC('[your statement]') and then COMMIT

3. To complete ALTER/CREATE or DELETE DATABASE (uh...) use COMMIT and then EXEC('[your statement]')