June 3, 2016 at 6:34 am
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.
June 3, 2016 at 11:18 am
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