0

We suffered some kind of invasion in our SQL Server.

I'm trying to find in every database, in every table, every column the word abortion and cheat.

I can do this with this query, but in a single database.

-- Store results in a local temp table so that.  I'm using a
-- local temp table so that I can access it in SP_EXECUTESQL.
create table #tmp 
(
    db varchar(max),
    tbl nvarchar(max),
    col nvarchar(max),
    val nvarchar(max),
);

declare @db nvarchar(max);
declare @tbl nvarchar(max);
declare @col nvarchar(max);
declare @q nvarchar(max);

declare @search nvarchar(max) = 'abortion';

-- Create a cursor on all columns in the database
declare c cursor for
    SELECT 
        DB_NAME(DB_ID()) as DBName, tbls.TABLE_NAME, cols.COLUMN_NAME  
    FROM INFORMATION_SCHEMA.TABLES AS tbls
    JOIN INFORMATION_SCHEMA.COLUMNS AS cols ON tbls.TABLE_NAME = cols.TABLE_NAME

-- For each table and column pair, see if the search value exists.
open c

fetch next from c into @db, @tbl, @col

while @@FETCH_STATUS = 0
begin
    -- Look for the search key in current table column and if found add it to the results.
    SET @q = 'INSERT INTO #tmp SELECT ''' +@db+''',''' + @tbl + ''', ''' + @col + ''', ' + @col + ' FROM ' + @tbl + ' WHERE ' + @col + ' LIKE ''%' + @search + '%'''
    EXEC SP_EXECUTESQL @q
    fetch next from c into @db, @tbl, @col
end
close c
deallocate c

-- Get results
select distinct db,tbl,col  from #tmp

-- Remove local temp table.
drop table #tmp

How can I find these strings? The result set should be:

DATABASE | TABLE | COLUMN

I don't need the result ( text field ), and I need to select distinct for tables and columns, because it will be a lot of abortion in the same table/column.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Racer SQL
  • 207
  • 1
  • 3
  • 14
  • http://stackoverflow.com/questions/9185871/how-to-search-sql-server-database-for-string or [SQL Search](http://www.red-gate.com/products/sql-development/sql-search/) or [sp_searchtable](https://gallery.technet.microsoft.com/scriptcenter/c0c57332-8624-48c0-b4c3-5b31fe641c58) – Lukasz Szozda Sep 02 '15 at 18:03
  • hello, thank you @lad2025 but none of these queries can help me. the last one that searches every database it's not working. – Racer SQL Sep 02 '15 at 18:09
  • Since this is a one off thing why can't you just run it for each database? Also, before you bother fixing the data I hope you fixed the sql injection vulnerability. Without a doubt that is what happened. – Sean Lange Sep 02 '15 at 18:23
  • Thats what i'm doing. We have 300 Databases. I'm doing it manually until i find a better solution. – Racer SQL Sep 02 '15 at 18:50
  • 1
    Did you fix the sql injection vulnerability? If you don't fix that you are going to be fighting this problem more than once. Honestly you probably have to fight this at least twice. Once as the "oh crap need to fix our website now" and once again for each time you get hacked again until you fix the problem. – Sean Lange Sep 02 '15 at 19:03

1 Answers1

0

While the use of the undocumented sp_msforeachdb is generally not encouraged, my instinct would be to send your existing code to this procedure like this:

exec sp_MSforeachdb 'USE [?]; 
-- Store results in a local temp table so that.  I'm using a
-- local temp table so that I can access it in SP_EXECUTESQL.
create table #tmp (
 db varchar(max)   ,
    tbl nvarchar(max),
    col nvarchar(max),
    val nvarchar(max),

);

declare @db nvarchar(max);
declare @tbl nvarchar(max);
declare @col nvarchar(max);
declare @q nvarchar(max);

--------------------------------------------------------------------------------------------
declare @search nvarchar(max) = ''abortion'';
--------------------------------------------------------------------------------------------


-- Create a cursor on all columns in the database
declare c cursor for
SELECT DB_NAME(DB_ID()) as DBName,tbls.TABLE_NAME, cols.COLUMN_NAME  FROM INFORMATION_SCHEMA.TABLES AS tbls
JOIN INFORMATION_SCHEMA.COLUMNS AS cols
ON tbls.TABLE_NAME = cols.TABLE_NAME

-- For each table and column pair, see if the search value exists.
open c
fetch next from c into @db, @tbl, @col
while @@FETCH_STATUS = 0
begin
    -- Look for the search key in current table column and if found add it to the results.
    SET @q = ''INSERT INTO #tmp SELECT '''''' +@db+'''''','''''' + @tbl + '''''', '''''' + @col + '''''', '' + @col + '' FROM '' + @tbl + '' WHERE '' + @col + '' LIKE ''''%'' + @search + ''%''''''
    EXEC SP_EXECUTESQL @q
    fetch next from c into @db, @tbl, @col
end
close c
deallocate c;'

The only added code here is the first line, for the rest of the code just make sure to replace ' with ''. The ? in USE [?] is a special character meaning the currently active database in the loop sp_MSforeachdb executes.

larsts
  • 451
  • 5
  • 12