Before I became a SQL Server DBA, I used to be a web developer in ASP3. Since then, I sometimes maintain the websites I still have. Fix things, add things, and most importantly – clean up the mess left in the database after a nasty SQL Injection attack.

Recently one of my older websites was attacked more than 5 times in only 2 days by the most recent mass SQL Injection attack. Someone really had it in them against that site I guess. In any case, after each attack I restored the database from a backup, but for various reasons couldn’t patch up the security hole in the site itself.

Auditing the Attacks

So, being a SQL Server DBA and all, I decided to handle this problem from within the database. At first, I created an “auditing” table which had an INSTEAD OF UPDATE trigger which didn’t allow any updates to be done to that table (because all the SQL Injection attacks are updating existing tables, and I didn’t want it to be affected):

CREATE TRIGGER [dbo].[TR_SQLAudit] ON [dbo].[SQLAudit]
INSTEAD OF UPDATE
AS
BEGIN
    RAISERROR('Update is not allowed!', 16, 1)
END
GO

Then, I created this trigger on every table with a textual column:

CREATE TRIGGER [dbo].[TR_mytable] ON [dbo].[mytable]
AFTER UPDATE
AS
BEGIN
    DECLARE @Input TABLE (EventType varchar(max), Parameters int, EventInfo nvarchar(max))
 
    INSERT INTO @Input
    EXEC ('DBCC INPUTBUFFER (@@SPID) WITH NO_INFOMSGS')
 
    INSERT INTO dbo.SQLAudit (modifier, app, workstation, EventInfo)
    SELECT system_user, app_name(), host_name(), EventInfo
    FROM @Input
 
END

This trigger uses DBCC INPUTBUFFER (@@SPID), which will return the T-SQL statement executed by the current session – thus showing me what the malicious UPDATE statements look like. This could also be done using the "most_recent_sql_handle" column in the "sys.dm_exec_connections" dynamic management view.

NOTE: The auditing table could be filled with a lot of junk very quickly if your website performs a lot of UPDATEs as part of its regular logic. However if you have no idea what the malicious statements look like, you may not have a choice.

Then I waited for the next attack to occur, and not a day passed before it happened again! I opened the auditing table and found a malicious code that looked like this:

declare @s varchar(8000) 
set @s=cast(0xsomereallylongbinarynumber as varchar(8000))
exec(@s)--

The long binary number translated to a TSQL batch which opened a cursor on all table columns of a textual type (char, varchar, text etc.), and executed a dynamic SQL which added to each column a malicious script.

So now I know what the malicious attack looks like.

Blocking the Attacks

So I went back to the triggers I created earlier on every table and changed them to this:

ALTER TRIGGER [dbo].[TR_mytable] ON [dbo].[mytable]
AFTER UPDATE
AS
BEGIN
 
    DECLARE @Cancelled BIT;
    DECLARE @Input TABLE (EventType varchar(max), Parameters int, EventInfo nvarchar(max))
 
    INSERT INTO @Input
    EXEC ('DBCC INPUTBUFFER (@@SPID) WITH NO_INFOMSGS')
 
    IF EXISTS (SELECT NULL FROM @Input WHERE EventInfo LIKE '%@s varchar(8000)%' OR EventInfo LIKE '%exec(@%')
    BEGIN
        WHILE @@TRANCOUNT > 0
            ROLLBACK;
        
        SET @Cancelled = 1;
    END
    ELSE
        SET @Cancelled = 0;
        
    INSERT INTO dbo.SQLAudit (modifier, app, workstation, EventInfo,cancelled)
    SELECT system_user, app_name(), host_name(), EventInfo, @Cancelled
    FROM @Input
 
END
GO

Note how I added a “ROLLBACK” functionality in my triggers which activates whenever the current TSQL batch contains “@s varchar(8000)” or “exec(@” (which I don’t use in my regular website code).

From now on, any future attempts to execute a similar code will fail, and each such attempt will be logged in the auditing table.

If your database has e-mail sending functionality, you could also send an e-mail to yourself notifying you whenever such an attack occurred (or alternatively, a “daily report” which checks whether attacks occurred during the last day or something).

Conclusion

The SQL Injection attacks change and mutate all the time. I have no doubt that in several months from now the malicious code will look different and I will have to adjust my trigger to catch it as well.

Obviously, the best protection from SQL Injection is to use stored procedures with strongly-typed parameters. But as I’ve experienced first-hand, it’s not always possible (for whatever reason) and I suppose this could happen to other people as well.

I hope this “from within the database” approach using Triggers will help you if you’re in a pickle similar to what I’ve been in.

Good luck and as always, post comments, suggestions and questions in the comments below!

The post Protect Against SQL Injection Using Triggers appeared first on .