How to recover objects that are missing as a result of a SQL injection attack

All of a sudden, you realize that something’s wrong with your database objects. Some stored procedures are missing, functions are there but they don’t work as expected since their code seems to be altered, and some triggers are created and fired which enhances the chaos you’re in.
After the initial shock, you start investigating. Don’t rule out a SQL injection attack, as it’s one of the most common web application security issues.

To find out more about SQL injection attacks, see: Checklist for suspicious activity on the SQL Server

How to recover deleted and damaged objects?

ApexSQL Log is an auditing and recovery tool for SQL Server databases which reads transaction logs, transaction log backups, detached transaction logs and database backups, and audits, reverts or replays data and object changes that have affected the database, including the ones that have occurred before the product was installed.

To recover objects that were deleted in a SQL injection attack, use ApexSQL Log

1. Start ApexSQL Log

2. Connect to the database

   

3. If you have any transaction log backups and/or detached transaction logs made before the SQL injection occurred.

   • To add transaction log backups or detached transaction logs click “Add” button
   • Select the needed transaction log backups or detached transaction logs and click “Next”

   

4. Use the “Filter” tab, “Time range” filter to narrow down the recovery to the time when the SQL injection occurred

   

5. On the “Operations” filter tab, unselect all Data operations (DML) to skip auditing the data changes – inserts, updates and deletes. If you can tell which object types have been tampered with, check only the specific Schema operations (DDL). If not, check all schema operations

   

6. If applicable, use other available filters to narrow down the results
7. To create the Undo script:

   1. Check the transactions you want to create the Undo script for, and click “Undo” on the “Home” tab

      

8. Execute the script against the database

After the Undo script is executed, the object changes will be rolled back and the original objects will be back in the database. All objects added by the SQL injection attack will be removed

Even if it looks like the worst-case scenario, it doesn’t have to be. ApexSQL Log can help you recover your objects to their original state