• Madeira Team

Protect Against SQL Injection Using Triggers

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