Now that we know how SQL injection works, we can use the same structure to check if we are infected.
If you haven't read how to, take a look at
But lets build a SQL statement, that looks for <script> injections in the database.
First we reuse the outer structure, to iterate trough the tables and fields.
The outer structure is:
DECLARE @T VARCHAR(255),@C VARCHAR(255),@S VARCHAR(4000)
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
Here we have table name in @T, and the coloumn name in @C
FETCH NEXT FROM Table_Cursor INTO @T,@C
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
Lets try to put in a PRINT, so we can see what tables/fields we are finding.
DECLARE @T VARCHAR(255),@C VARCHAR(255),@S VARCHAR(4000)
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
PRINT 'Table= ['+@T+'], coloumn= ['+@C+']' /* <<===Prints tablename an coloumn name */
FETCH NEXT FROM Table_Cursor INTO @T,@C
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
If we run this SQL against the
pubs database, we get the following in the result pane:
Table= [titleauthor], coloumn= [au_id]
Table= [titleauthor], coloumn= [title_id]
Table= [stores], coloumn= [stor_name]
Table= [stores], coloumn= [stor_address]
Table= [stores], coloumn= [city]
Table= [sales], coloumn= [ord_num]
Table= [sales], coloumn= [payterms]
Table= [sales], coloumn= [title_id]
Table= [roysched], coloumn= [title_id]
.... etc
This is the tables and files, the bot's are trying to inject <script> tags into.
Instead of printing, we now build a SQL statement for each combination which can be executed
to find out whether it contains <script> tags.
We use the @T and the @C to build a string, so instead of our PRINT, we build @S as:
@S= 'SELECT ['+@C+'] FROM ['+@T+'] WHERE ['+@C+'] LIKE ''%<script%'''
If we put it in the above code instead of print, at executes it against the
pubs database,
we get this in the result pane. Yoy may call it kind of debugging:
SELECT [au_id] FROM [titleauthor] WHERE [au_id] LIKE '%<script%'
SELECT [title_id] FROM [titleauthor] WHERE [title_id] LIKE '%<script%'
SELECT [stor_name] FROM [stores] WHERE [stor_name] LIKE '%<script%'
SELECT [stor_address] FROM [stores] WHERE [stor_address] LIKE '%<script%'
SELECT [city] FROM [stores] WHERE [city] LIKE '%<script%'
SELECT [ord_num] FROM [sales] WHERE [ord_num] LIKE '%<script%'
SELECT [payterms] FROM [sales] WHERE [payterms] LIKE '%<script%'
SELECT [title_id] FROM [sales] WHERE [title_id] LIKE '%<script%'
... etc
So for each combination of table/coloumn, we check whether it containg a '<script>
This part of the statement:
LIKE '%<script%'
is a so called wildcard search.
Is basically says 'give me the records which contains the string '<script' ANYWHERE in the field.
Note her
ANYWHERE, which means
IF you have legal '<script' occuring in your database, it wille be found as well.
Now we are ready to finish the SQL by executing the SQL instead of printing it.
So in the inner loop we use this
SELECT @S= 'SELECT ['+@C+'] FROM ['+@T+'] WHERE ['+@C+'] LIKE ''%<script%'''
Which puts the SQL inside the variable @S.
To get a better overview of the result, we put in a PRINT as well:
PRINT ('***** Checking table >'+@T+'< Coloumn >'+@C +'< *****')
It puts in a kind of header, and specially the *'s improves the readability where fields changes.
And at last, we execute the SQL (in @S) with this little piece of code:
EXEC (@S)
When it is all put together, the final SQL statement looks like this:
DECLARE @T VARCHAR(255),@C VARCHAR(255),@S VARCHAR(4000)
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
SELECT @S= 'SELECT ['+@C+'] FROM ['+@T+'] WHERE ['+@C+'] LIKE ''%<script%'''
PRINT ('***** Checking table >'+@T+'< Coloumn >'+@C +'< *****')
EXEC (@S)
FETCH NEXT FROM Table_Cursor INTO @T,@C
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
Now we are ready to run.
If you have read the
How it was done, you may notice, that we succeeded injecting into one table/field.
Let's see if we can find it.
We copy/paste the code into Query Analyzer, executes it, and sees this in the result pane:
***** Checking table >titleauthor< Coloumn >au_id< *****
au_id
-----------
***** Checking table >titleauthor< Coloumn >title_id< *****
title_id
--------
***** Checking table >stores< Coloumn >stor_name< *****
stor_name
----------------------------------------
***** Checking table >stores< Coloumn >stor_address< *****
stor_address
----------------------------------------
***** Checking table >stores< Coloumn >city< *****
city
--------------------
***** Checking table >sales< Coloumn >ord_num< *****
ord_num
--------------------
***** Checking table >sales< Coloumn >payterms< *****
payterms
------------
***** Checking table >sales< Coloumn >title_id< *****
title_id
--------
***** Checking table >roysched< Coloumn >title_id< *****
title_id
--------
***** Checking table >discounts< Coloumn >discounttype< *****
discounttype
----------------------------------------
***** Checking table >jobs< Coloumn >job_desc< *****
job_desc
--------------------------------------------------
***** Checking table >pub_info< Coloumn >pr_info< *****
pr_info
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
So how do we read it?
The basics is, that is no records is returned, this table/coloumn is 'clean'.
You can see it by having (only) an empty line between the table/field combinations.
So having this:
***** Checking table >titleauthor< Coloumn >au_id< *****
au_id
-----------
An empty line
***** Checking table >titleauthor< Coloumn >title_id< *****
Means that the table
titleauthor and field
au_id is clean.
Meaning, that no occurrences of <script was found.
But when we scrool down, we see:
***** Checking table >pub_info< Coloumn >pr_info< *****
pr_info
-------------------------------------------------------------------------------------------------------------------------------------
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
Now the generated SQL actually returns a resultset.
Notice, that depending on your settings, the resulset is only partially shown.
The strings returned is only the first (typically 255) characters within the field.
Now we have positively observe, that our
pubs database is infected.
But only the [pr_info] field in the [pub_info] table.
The best thing to do in such a case is to find a clean backup and restore it.
But if the injection have been there, undetected, for quite a while, it may be more appropriate to try to remove it instead.
You can do it manually, or you can take a look at the removal kit: