Siusic Dot Com

If you are brought to this post from search engine then most likely you are looking for a way to get rid of the virus or malware infected your PC or you want to make your disk defragmenter works.

I didn’t remember since when I start feeling

my internet connection did not work as fast as it was. So I complained to my internet service provider, AT&T, and told them the internet speed I got was not what I was paying for and I even threatened to cancel the service if they didn’t raised the speed of my internet connection. However when I tested my internet speed online it was always in the range so I upgraded my internet connection up to 5M. Nothing changed and I still felt the same on connection speed. Then I started looking on my computer to see if it was the cause. I tried to defragment my hard drive. But I couldn’t. An error always said “Disk Defragmenter could not start”. I was busy on other things and put that problem on hold for a long while.

A few days ago I started to notice Google and Yahoo didn’t look quite the same as before. When I did a search on Google or Yahoo and clicked on the result links I was brought to sites different from the links and in some cases the site was totally unrelated to what I was searching for. I was scratching my head and wondering what was going on with Google and Yahoo. I right clicked on Google and Yahoo links and looked at their properties. They were either linked to go.google.com or go.yahoo.com. So I ping these two host names. Guess what the responded host was Compalusa.com at IP address 72.36.238.83. Obviously it was not a Google or Yahoo IP. All of a sudden I understood that my computer was infected with malware.

The clever thing about this malware was it was able to hide itself almost completely. I couldn’t see anything abnormal in task manager. Neither could I see anything abnormal in my start up folder and registry. Usually virus or malware would have registry entries in the following registry keys so that they could be launched on boot time. I went ahead and use a rootkit detecting tools. But nothing was detected.

HKLM\Software\Microsoft\Windows\CurrentVersion\Run
HKLM\Software\Microsoft\Windows\CurrentVersion\RunOnce
HKLM\Software\Microsoft\Windows\CurrentVersion\RunOnceEx
HKLM\Software\Microsoft\Windows\CurrentVersion\policies\Explorer\Run

Further more I found I was having trouble installing Hijackthis and Spybot. I was able to overcome this problem and installed them later on and I will tell you how. So in effect Google and Yahoo’s money generating traffic was hijacked by this guy called Compalusa. Ironically I was frequently brought to a web site called shoplocal.com which was a client of a company that I worked previously. So Shoplocal is probably paying Compalusa to get internet traffic. Don’t under estimate the money being pay by this type of advertisers like shoplocal. And Compalusa could be making over 100 thousands daily. It equals robbing Google and Yahoo which is better than robbing banks nowadays.

I deal with terrible malware like wintems and hldrrr before but this one is a lot smarter then them. It hides itself from virtually every tools that I used except Spybot. I didn’t know why Spybot was able to see it. The malware is called TDSS. It contains a driver file in %System Root%\system32\drivers\ directory, serveral dlls and a log file in the %System Root%\system32\ directory. All their file names contain “TDSS”. The command dir *TDSS*.* will give you nothing if your PC is infected with this malware. But Spybot was able to point out TDSS infected my computer. See the following screen shot. But Spybot wasn’t able to clean it.

So going back to the previous question how to install Hijackthis or Spybot in this case? By the way Hijackthis doesn’t help in anything way. To install them you will have to rename the installation file to something else. And to launch Spybot you have to download updates manually and rename the Spybot executable file to something else. The malware wouldn’t allow me to start Spybot and run updates. It seemed to know its file name and prevent it from being launched and it seemed to know from where Spybot downloads updates and blocked the download site.

How do I get rid of it? Fortunately I have a boot disk handy. The tool I use to make the boot disk was Winternal’s ERD Commander. I just found out that it is part of Microsoft now. Microsoft really has eagle’s eye finding gem out of stones. Anyway I used the boot disk to boot my computer on CD. After that I was able to exam the registry and files and deleted any suspected registry entries and files named with “TDSS”.

My internet connection returned to blazing speed.

I was looking for a way to find the first occurrence of 4 digit year in a column containing free from text. The first thing I think of was to use regular expression. I did some research and came up with the following function. Once the function is created it can be used like this,
select dbo.fn_regex (’[1-2][0,9][0-9][0-9]’, ‘free form text’),
to return the first matching substring. This function can be use on SQL 2000 and SQL 2005. However in SQL 2005 it is better to use CLR function. You can refer to the following link for further detail.

T-SQL regular expression in SQL 2000
T-SQL regular expression in SQL 2005

create FUNCTION
dbo.fn_regex(@pattern varchar(255), @matchstring varchar(8000))
RETURNS varchar(100)
AS
BEGIN
declare @obj int
declare @res int
declare @match bit
declare @objMatch int
declare @matchpart varchar(100)
set @match=0

exec @res=sp_OACreate ‘VBScript.RegExp’,@obj OUT
IF (@res <> 0) BEGIN
RETURN NULL
END

exec @res=sp_OASetProperty @obj, ‘Pattern’, @pattern
IF (@res <> 0) BEGIN
RETURN NULL
END

exec @res=sp_OASetProperty @obj, ‘IgnoreCase’, 1
IF (@res <> 0) BEGIN
RETURN NULL
END

–exec @res=sp_OAMethod @obj, ‘Test’,@match OUT, @matchstring
–IF (@res <> 0) BEGIN
— RETURN NULL
–END

exec @res= sp_OAMethod @obj, ‘execute’, @objMatch OUT, @matchstring
IF (@res <> 0)
RETURN NULL
ELSE
EXEC @res= sp_OAGetProperty @objmatch, ‘item(0).value’, @matchpart OUT

exec @res=sp_OADestroy @obj

–return @match
return @matchpart
END

A query to display all user tables in a database and sort them by decending order according to row counts is very useful when it comes to research and discover where data are stored in a complex database. I found such query very helpful from time to time. I have collected a few of them here.

For SQL server 2000:

SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = ‘U’
AND
si.id = OBJECT_ID(user_name(uid)+’.'+so.name)
GROUP BY
so.name
ORDER BY
2 DESC

For SQL Server 2005

–SQL 2005
SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sys.tables so,
sys.sysindexes si
WHERE
so.type = ‘U’
AND
si.id = OBJECT_ID(schema_name(schema_id)+’.'+so.name)
GROUP BY
so.name
ORDER BY
2 DESC

SELECT OBJECT_NAME(st.OBJECT_ID) TableName, st.row_count
FROM sys.dm_db_partition_stats st
join sys.objects tb on st.object_id=tb.object_id
and objectproperty(tb.object_id, ‘isUserTable’)=1
WHERE st.index_id < 2
and st.row_count>0
ORDER BY st.row_count DESC

After writing previous post about using window batch command to rename all files in a folder I found using VB Script to do the same thing is much easier.

Let’s say you have a few thousand files named in the same pattern like transaction_xxxxx.txt in a folder called test. You want to rename all these files so that they are named like trans_xxxxx.txt. Here is the VB Script code to do that.

Dim fso,f

Set fso=CreateObject(”Scripting.FileSystemObject”)
Set f = fso.GetFolder(”d:/test”)
For Each file In f.Files
fso.MoveFile file.Name, Replace(file.Name,”Transaction”,”Tran”)
Next

Set f = Nothing
Set fso = Nothing

Put the above code into a text file, rename the text file to rename.vbs and put it into the test folder. Double click on the vbs file to execute it. And that is it.

I was trying to write a batch file to rename all files in a folder on windows. And the following is what I came up with. It worked very well. I found one of the great usage of my blog was to archive whatever script I came up with. It could help someone to quickly find solution for the same problem I experienced and at the same time I could easily find my archived script using the search box on my blog.

rem: Some testing batch command in Windows XP

setlocal ENABLEDELAYEDEXPANSION
@echo off

rem ***************** Test 1 **********************
rem about variables

set var1= This is a variable VAR1
set var2=’This is a variable VAR2′
set var3=”This is a variable VAR3″
echo %var1%
echo ‘%var2%’
echo “%var3%”

rem ***********************************************
rem Conclusion from test 1 is that assigning text
rem value to variable doesn’t need to be quoted
rem ***********************************************

rem **************** Test 2 **********************
rem about for loop

set count=0
for %%a in (1 2 3) do (
echo !count!
set /A count=!count!+1
echo %%a
)

for %%A in (1 2) do for %%B in (A B) DO ECHO %%A%%B

rem **********************************************
rem Conclusion from test 2 is that variable in
rem the for loop has to be single character like
rem %%a. %%var will not work
rem you don’t have to have
rem setlocal ENABLEDELAYEDEXPANSION for the count
rem to work but you do have to use !count! instead
rem of %count%
rem **********************************************

rem **************** Test 3 **********************
rem use for loop to list all .txt file names

for /f %%a in (’dir /b transaction*.txt’) do (
echo %%a
)

rem **********************************************
rem I don’t know what the switch /f in the for
rem statement and the /A switch in the set
rem statement mean but it works.
rem Can someone explain the switch? =)
rem **********************************************

rem **************** Test 4 **********************
rem rename all .txt file so that all of them
rem starts with “trans” instead of “transaction”

for /f “tokens=1,2 delims=_” %%a in (’dir /b transaction*.txt’) do (
if “%%a”==”transaction” echo yes
echo %%a
echo %%b
ren %%a_%%b tran_%%b
)

rem **********************************************
rem The above script works fine. It renames all
rem files named in partern transaction_XXXX.txt
rem to trans_XXXXX.txt
rem “tokens=1,2 delims=_” tells it to separate
rem whatever returned in dir command in two parts
rem using the first “_” found and put the first
rem token in %%a, the second in %%b.
rem It is very powerful and interesting!
rem **********************************************

goto :eof