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):
Transact-SQL
CREATE TRIGGER [dbo].[TR_SQLAudit] ON [dbo].[SQLAudit] INSTEAD OF UPDATE AS BEGIN RAISERROR('Update is not allowed!', 16, 1) END GO
1
2
3
4
5
6
7
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:
Transact-SQL
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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:
Transact-SQL
declare @s varchar(8000) set @s=cast(0xsomereallylongbinarynumber as varchar(8000)) exec(@s)--
1
2
3
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:
Transact-SQL
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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!