I have some old databases i was handed that use SQL Server 2000 and they are getting SQL Injected with javascript script tags at the end of certain database fields. I need a trigger to strip out the injected on update until I have time to fix the front end that is allowing this.
I am a SQL Server novice - please help!
something like:
UPDATE table
SET field = REPLACE(field, '</script>', REPLACE(field, '<script>',''))
WHERE table.pk IN (SELECT pk FROM inserted WHERE field LIKE '%script>')
?
Is there any regex like functionality in SQL Server 2000? The content of the script tags constantly changes.
There's a large scale attack that's been going on since way back in April, and if that's what getting you then you'd have to add a trigger for every table in the database.
This script modifies the original attack code to clean up everything in one swoop, assuming <script isn't valid text anywhere in the db:
DECLARE @T varchar(255),@C varchar(255)
DECLARE Table_Cursor CURSOR FOR select a.name,b.name from sysobjects a,syscolumns b where a.id=b.id and a.xtype='u' and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167)
OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C
WHILE(@@FETCH_STATUS=0) BEGIN
exec('update ['+@T+'] set ['+@C+']=LEFT(['+@C+'], CHARINDEX(''<script'', ['+@C+'])-1)
WHERE CHARINDEX(''<script'', ['+@C+']) >0')
FETCH NEXT FROM Table_Cursor INTO @T,@C
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
Additionally, I've heard you may have luck stopping this attack by removing SELECT permissions for the application user on syscolumns or sysobjects, if that's an option for you. You still need to fix your vulnerabilities in preparation for the next attack.
once your data is fixed you will need to find and fix the way the injections are getting into your datbase. I presume you are probably using dynamic SQl. This article will help you fix it so that injections won't be a problem http://www.sommarskog.se/dynamic_sql.html