Brute Force data search tool

  • Hi

    I have made it work with varchar and nvarchar max, and also XML.

    Also, I have compacted the nested if statements so that we have one insert.

    Also, I have added a bit of logic to properly compare the length of fields for Nvarchar and Nchar fields since nvarchar(10) is being reported as having length of 20.

    So to calculate the comparison with the search string length, we have to divide by 2 like so

    isnull(nullif(cast(c.max_length as int),-1),50000)/ (case when st.name like 'n%char' THEN 2 else 1 END) >= LEN(@searchvalue))

    I have changed the fuzzy = 0 to do an equal to when comparing string instead of doing a like with no %.

    Not picking it apart for any reason other than I found it very very helpful and am optimising it and reducing code so that I can maintain it easily if I need to.

    alter PROCEDURE [dbo].[DBBruteForce]

    @searchvalue varchar(4000),

    @type varchar(200),

    @fuzzy BIT,

    @fuzzyoperator VARCHAR(200)

    AS

    BEGIN

    --set nocount on as there could be thousands of queries here

    SET NOCOUNT ON;

    --build up helper table for types used later to limit rows we will query

    DECLARE @typelist TABLE(rowid INT IDENTITY, TYPE VARCHAR(255), typename varchar(255) PRIMARY KEY(type,typename));

    --feel free to add more in here if you need currently this only supports number, string and date

    INSERT INTO @typelist(type,typename)

    VALUES

    ('number','decimal'),

    ('number','numeric'),

    ('string','char'),

    ('number','smallint'),

    ('string','varchar'),

    ('date','datetime'),

    ('string','nvarchar'),

    ('string','text'),

    ('string','ntext'),

    ('number','int'),

    ('number','bigint'),

    ('date','smalldatetime'),

    ('number','float'),

    ('number','money'),

    ('string','xml')

    ;

    --now remove the temp tbles if they already exists

    IF OBJECT_ID('tempdb..#TempCols') IS NOT NULL

    DROP TABLE #TempCols;

    IF OBJECT_ID('tempdb..#TempBruteForce') IS NOT NULL

    DROP TABLE #TempBruteForce;

    --create the temp table needed for the search

    CREATE TABLE #TempCols(id INT IDENTITY PRIMARY KEY, tbl VARCHAR(255) NOT null, col VARCHAR(255) NOT null, TYPE varchar(255));

    CREATE TABLE #TempBruteForce(id INT IDENTITY PRIMARY KEY, tbl VARCHAR(255) NOT null, tblqry Nvarchar(max), cnt INT DEFAULT (0),processed BIT DEFAULT(0),sqltxt Nvarchar(max),errortxt NVARCHAR(max));

    INSERT INTO #TempCols(tbl,col,type)

    SELECT DISTINCT '[' + ss.name + '].[' + t.name + ']' AS tblname, c.name AS colname, st.name AS coltype

    FROM sys.tables t

    INNER JOIN sys.columns c ON c.object_id = t.object_id

    INNER JOIN sys.types st ON st.system_type_id = c.system_type_id AND st.name != 'sysname'

    INNER JOIN sys.schemas ss ON ss.schema_id = t.schema_id and ss.name <>'CDC'

    WHERE EXISTS (SELECT 1 FROM @typelist WHERE type = @type AND typename = st.name) and @type in('number','date') OR (EXISTS (SELECT 1 FROM @typelist WHERE type = @type AND typename = st.name)

    AND ((st.collation_name is not null and isnull(nullif(cast(c.max_length as int),-1),50000)/ (case when st.name like 'n%char' THEN 2 else 1 END) >= LEN(@searchvalue)) or st.name = 'XML')) and @type ='string'

    --in this case we cast datetimes as daes to do a simple comparison, mainly to avoid a 2 millisec difference causing a non match.

    --Its better to error on the side of more rows idenified then less.

    if not exists (select null from #TempCols)

    BEGIN

    --if the type cant be determined just error the process out

    RAISERROR ('Unknown type encountered! string, number, and date are the only accepted types', 0, 20) WITH NOWAIT;

    END

    INSERT INTO #TempBruteForce(tbl,tblqry,cnt,processed,sqltxt,errortxt)

    SELECT t.tbl,'select count(1) from ' + tbl + ' where 1 = 1 and ('+REPLACE(x.csvvalue,'|',' or ')+')',0,0,NULL,null

    FROM #TempCols t

    OUTER APPLY ( SELECT STUFF(( SELECT '|' + (

    case WHEN @type = 'string' and tt.TYPE <>'XML' and @fuzzy = 1 AND @fuzzyoperator = 'beginswith' THEN '[' + tt.col + '] LIKE '''+@searchvalue+'%'''

    WHEN @type = 'string' and tt.TYPE <>'XML' and @fuzzy = 1 AND @fuzzyoperator = 'endswith' THEN '[' + tt.col + '] LIKE ''%'+@searchvalue+''''

    WHEN @type = 'string' and tt.TYPE <>'XML' and @fuzzy = 1 AND @fuzzyoperator = 'Contains' THEN '[' + tt.col + '] LIKE ''%'+@searchvalue+'%'''

    WHEN @type = 'string' and tt.TYPE <>'XML' and @fuzzy = 0 THEN '[' + tt.col + '] = '''+@searchvalue+''''

    WHEN @type = 'string' and tt.TYPE ='XML' and @fuzzy = 1 AND @fuzzyoperator = 'beginswith' THEN 'cast([' + tt.col + '] as nvarchar(max)) LIKE '''+@searchvalue+'%'''

    WHEN @type = 'string' and tt.TYPE ='XML' and @fuzzy = 1 AND @fuzzyoperator = 'endswith' THEN 'cast([' + tt.col + '] as nvarchar(max)) LIKE ''%'+@searchvalue+''''

    WHEN @type = 'string' and tt.TYPE ='XML' and @fuzzy = 1 AND @fuzzyoperator = 'Contains' THEN 'cast([' + tt.col + '] as nvarchar(max)) LIKE ''%'+@searchvalue+'%'''

    WHEN @type = 'string' and tt.TYPE ='XML' and @fuzzy = 0 THEN 'cast([' + tt.col + '] as nvarchar(max)) = '''+@searchvalue+''''

    WHEN @type = 'number' THEN '[' + tt.col + '] = '+@searchvalue

    WHEN @type = 'date' THEN'cast([' + tt.col + '] as date) = cast('''+@searchvalue+''' as date)'

    END)

    FROM #TempCols tt

    WHERE tt.tbl = t.tbl

    FOR XML PATH(''), TYPE ).value('.[1]', 'nvarchar(max)'

    ), 1, 1, '') AS csvvalue

    ) x

    GROUP BY tbl,x.csvvalue

    --declarations

    DECLARE @rowcnt INT ,

    @operator VARCHAR(200),

    @where varchar(200),

    @sqltxt NVARCHAR(max),

    @SQL NVARCHAR(max),

    @SQL_cnt NVARCHAR(max),

    @ParmDefinition nVARCHAR(500),

    @I INT,

    @id INT,

    @tbl varchar(255),

    @col VARCHAR(255),

    @fldtype VARCHAR(255),

    @counter INT;

    DECLARE dbnames CURSOR LOCAL FORWARD_ONLY FAST_FORWARD FOR

    SELECT id,tbl,tblqry FROM #TempBruteForce

    OPEN dbnames

    FETCH NEXT FROM dbnames INTO @id,@tbl,@sqltxt

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SELECT @SQL_cnt = 'SELECT @IOUT = ('+@sqltxt+');';

    SELECT @SQL = @sqltxt;

    SET @ParmDefinition = N'@IOUT INT OUTPUT';

    BEGIN TRY

    EXEC sp_executesql

    @SQL_cnt,

    @ParmDefinition,

    @IOUT=@I OUTPUT;

    --get count results and update the temptable, mark as processed

    UPDATE #TempBruteForce SET cnt = @I, sqltxt = @sql, processed = 1 WHERE id = @id;

    --if @i is greater than 0 than lets print the query to the messages, allows a preview of data found before it finishes,

    --it can take an hour on large databases with large tables and lots of rows. so sometimes i dont like to wait.

    IF(@I > 0)

    BEGIN

    RAISERROR (@SQL, 0, 1) WITH NOWAIT;

    END

    ELSE

    BEGIN

    --i like to at least print the table out for ones nothing was found in, mainly to tell where its at in the process

    DECLARE @temptxt VARCHAR(255);

    SET @temptxt = @tbl + ': [no data found]'

    RAISERROR (@temptxt, 0, 1) WITH NOWAIT;

    end

    END TRY

    BEGIN CATCH

    --in this case i am interested in what errors so lets just record that one back in the temp table as an error

    PRINT('Error occurred in table: '+@tbl+'');

    UPDATE t SET processed = 1,errortxt = @sqltxt

    FROM #TempBruteForce t

    where t.id = @id;

    END CATCH

    FETCH NEXT FROM dbnames INTO @id,@tbl,@sqltxt

    END

    CLOSE dbnames

    DEALLOCATE dbnames

    SELECT 'Search Results';

    SELECT * FROM #TempBruteForce WHERE cnt > 0 ORDER BY cnt DESC;

    SELECT 'Errors Encountered';

    SELECT * FROM #TempBruteForce WHERE LEN(errortxt) > 0;

    SET NOCOUNT OFF;

    END

    GO

    Editted since making it work with XML made it ignore other blobs.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • Nice, I dont get a lot of data in xml columns so never actually added that. I will warn you however that switching the like with no percents to an equals works for most but will error on text and ntext columns, so that now would also need to be cased out or dealt with separately. that was why i decided to use the like without the percents. Typically the databases i am working with are not well designed and are typically older, so text and ntext columns are common in the tables i am searching through. Instead of having more case statements and making the script more complicated i went with a simple solution using the likes. If you dont usually have to deal with text or ntext columns then maybe its fine as it is.

    Good catch on the column length stuff, totally was in a hurry to add that in and totally forgot to account for nvarchar lengths. Dont know how many times that has messed with me over the years, you think i would have learned by now:(

    The point of posting this script was for feedback and new ideas, so i totally appreciate what you've done here and adjusted my scripts some because of it. Thanks a bunch, and glad it was useful for you.

    John

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply