Clever Geek Handbook

How can I create a trigger to replace sql injection <script> tags in SQL Server 2000?

I have several old databases passed to me that are using 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 cut the injected on the update until I have time to fix the front end that allows it.

I am getting started with SQL Server - please help!

+1


source to share


5 answers


I think the limitation would be better. Anything that compromised the content would be better off rejected.

Set up a constraint on the margin, something like



CHARINDEX ('<script & gt', [fieldname]) = 0
+2


source


something like:

UPDATE table
Field SET = REPLACE (field, '</script>', REPLACE (field, '<script>', ''))
WHERE table.pk IN (SELECT pk FROM inserted WHERE field LIKE '% script>')



?

0


source


Are there any regular expressions in SQL Server 2000? The content of script tags is constantly changing.

0


source


There has been a massive attack that has been going on since April last year, and if you need it, you'll have to add a trigger for every table in the database. This script modifies the attack source to clear everything in one fell swoop, assuming <script

invalid 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

Also, I heard that you will be able to stop this attack by removing the permissions SELECT

for the app user on syscolumns

or sysobjects

if that is an option for you. You should still patch your vulnerabilities in preparation for the next attack.

0


source


Once your data is corrected, you will need to find and fix how the injections are getting into your database. I assume you are probably using dynamic SQl. This article will help you fix it so injections are not a problem http://www.sommarskog.se/dynamic_sql.html

0


source






More articles:


All Articles