In one of the web applications we have at my work, there is a sql injection problem. Unfortunately the web application is very old, programmed with classic asp, the company does not support it anymore. We have the source code for the web application as it is classic asp however; the code is not documented, and the hit points to the database is all over, so I can’t just simply go to one location in the source, and fix it.
The first thing I have to do after we are hit, is to stop the attacks, so after reporting the Ip address of the attacker to the related departments, I knew I couldn’t fix the problem in the code; so I added a trigger to the database, that will check the inserted data; and if found a <script command simply rollback. Here is a sample trigger that does this:
1: CREATE TRIGGER [dbo].[checkNewsForScriptandIFrameAttach]
2: ON [dbo].[news]
3: AFTER INSERT, UPDATE
4: AS
5: IF @@ROWCOUNT=0
6: begin
7: RETURN
8: END
9: declare @headline varchar(350)
10:
11: set @headline = (select headline from inserted)
12:
13: IF(CHARINDEX('<iframe',@headline))>0
14: BEGIN
15: ROLLBACK TRANSACTION
16: END
17:
18: IF(CHARINDEX('<script',@headline))>0
19: BEGIN
20: ROLLBACK TRANSACTION
21: END
This is a trigger that will run after the insert and update sql commands on the database. What it does is, to get the column value from the inserted table (this is a special table that sql server uses, before it actually writes the value to the actual table), and checks if the inserted column has <iframe or <script tags. If there is any, then rollback the transaction and don’t write the new values to the actual table but wipe them out.
So the next step is to sanitize the table that is already hit, I could use TSQL for this, but I decided to use LINQ as I can log the operations and do some other things while I am sanitizing the table. I wrote this small console application:
1: NewsDBDataContext db = new NewsDBDataContext();
2:
3: var scriptCode = "<script src=http://www.somefckrcode></script>";
4:
5: var news = db.News;
6: foreach (var news in news
7: {
8: news.headline = news.headline.Replace(scriptCode,String.Empty);
9: db.SubmitChanges();
10: //some log operations here
11: }
All this code does is to replace the script tag with empty string. Of cource I can query the database, and bring in only the records that has the scriptCode value injected; however in my case all the rows has it, so I simply get all the records, and iterate through all of them.
I ran this application, and got the error from sql “Value will be truncated….”; it wasn’t a good error message; as it wasn’t really telling me anything. But quickly I figured out that the problem was the trigger, the trigger was on, and my code was checking the script and updating it, however trigger was throwing an error; I disabled my trigger, then ran the application, everything worked perfectly fine, and enabled the trigger back again.
One more note on this, the number of rows that I am updating is around 9000; the update took around 5 minutes; when I replace the headline.Replace with a regular expression replace, the code took less than a minute :)