search all fields at once

  • As I noted in another thread, my DB got hit with SQL injection through our company website. Obviously the long-term solution is to put injection-prevention code in the site. But for now, I need to find those places in the database where some of the malicious code got placed. As it turns out, they appended our existing data with HTML script tags with a reference to a javascript.

    I found several fields where this occurred and cleaned it out, but I would like to search the entire database for this particular string. I can't do a SELECT * FROM * WHERE * = "whatever" So how do you do a DB-wide search for a given string?

  • Here is an article with some examples that should help.

    http://www.mssqltips.com/tip.asp?tip=1525

    You will specifically want to look at the last code sample.

  • Without writing a bunch of code myself I can present a framework of an idea..

    You could use the information schema views to get the table names and column names for each table in the database. You would filter out all the numeric fields for search, since the code can't be stored ther. You could then use whats left to build a dynamic SQL statement that searches each field in each table for some snippet of the offending code and outputs some indicator. I would probably not use the whole string, but the beginning of it or a unique string in it. The reason I recommend that is that because of your data types it may have gotten truncated and I think you are trying to find all traces of it.

    Depending on the size of your database this can be quite intensive, but the script should be able to do most of the detection work..

    CEWII

  • Ken Simmons (7/22/2009)


    Here is an article with some examples that should help.

    http://www.mssqltips.com/tip.asp?tip=1525

    You will specifically want to look at the last code sample.

    Aha.. Same idea, but with code!

    CEWII

  • thanks, guys!

  • I think i will go with http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

  • Here is the code to search an entire database for keywords. This should do the trick.

    I know... stupid variable names, I was bored one morning. šŸ˜›

    Michelle

    SET NOCOUNT ON

    DECLARE @Search varchar(100)

    SET @Search = 'enter search criteria here'

    --Iā€™m searching for cable in the above line

    SELECT Sharpie = TABLE_NAME

    INTO Marker

    FROM INFORMATION_SCHEMA.TABLES

    WHERE TABLE_TYPE = 'BASE TABLE'

    SELECT Sharpie, COLUMN_NAME AS Highlighter

    INTO Tomo

    FROM Marker JOIN INFORMATION_SCHEMA.COLUMNS

    ON TABLE_NAME = Sharpie

    AND DATA_TYPE LIKE '%char'

    CREATE TABLE Yellow (Red varchar(1000))

    DECLARE @Coffee varchar(1000)

    SET @Coffee =

    'INSERT INTO Yellow SELECT TOP 1 Red=''Found ' +

    REPLACE(@Search,'''','''''') + ' in SHARPIE.HIGHLIGHTER'' FROM SHARPIE WHERE ' +

    'HIGHLIGHTER LIKE ' + QUOTENAME('%'+@Search+'%','''')

    --'HIGHLIGHTER = ' + QUOTENAME(@Search,'''')

    /*if you want to find the string as a substring of a column value, change the above line to

    'HIGHLIGHTER LIKE ' + QUOTENAME('%'+@Search+'%','''') */

    DECLARE @Work varchar(1000)

    DECLARE @Sharpie sysname

    DECLARE @Highlighter sysname

    DECLARE Project CURSOR FOR

    SELECT Highlighter, Sharpie FROM Tomo

    WHERE Sharpie 'Marker'

    OPEN Project

    DECLARE @Region int

    FETCH NEXT FROM Project INTO @Highlighter,@Sharpie

    WHILE @@FETCH_STATUS = 0 BEGIN

    SET @Work =

    REPLACE(REPLACE(@Coffee,'HIGHLIGHTER',quotename(@Highlighter)),

    'SHARPIE',quotename(@Sharpie))

    exec (@Work)

    SELECT @Region = COUNT(Red) FROM Yellow

    FETCH NEXT FROM Project INTO @Highlighter,@Sharpie

    END

    IF @Region IS NULL

    PRINT '['+@Search+'] not found'

    ELSE

    SELECT Red FROM Yellow

    DEALLOCATE Project

    go

    DROP TABLE Marker

    DROP TABLE Tomo

    DROP TABLE Yellow

Viewing 7 posts - 1 through 6 (of 6 total)

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