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))