Search specific string in data in all tables

  • joemai

    Hall of Fame

    Points: 3496

    Hi Experts,

    Is there a way to find specific string in data in all the user tables, not column names or stored procedures? Can some one please help? Also, one of our websites was hacked by someone. I believe that they use SQL injection to do so. Is there anyway/tool to check for SQL vulnerbilities?

    Any inputs will be very appreciated.

    Thanks,

    Minh

  • Venkataraman-408293

    Mr or Mrs. 500

    Points: 540

    This one is useful link to search and replace SQL Server data in all columns of all tables, in a given database?

    http://vyaskn.tripod.com/sql_server_search_and_replace.htm

    🙂

  • Lowell

    SSC Guru

    Points: 323309

    here’s something i use, and i’m sure i’m stating the obvious and what you already did tog et back up and running, but you’d be best off doing a back up, restoring a previous database, and then compare corrupted vs production.

    there’s no built in tool to test for sql injection, you;l need to go thru your code.

    CREATE PROCEDURE UGLYSEARCH

    — EXEC UGLYSEARCH ‘TEST’

    @SEARCHSTRING VARCHAR(50)

    AS

    SET NOCOUNT ON

    DECLARE @SQL VARCHAR(500),

    @TABLENAME VARCHAR(60),

    @COLUMNNAME VARCHAR(60)

    CREATE TABLE #RESULTS(TBLNAME VARCHAR(60),COLNAME VARCHAR(60),SQL VARCHAR(600))

    SELECT

    SYSOBJECTS.NAME AS TBLNAME,

    SYSCOLUMNS.NAME AS COLNAME,

    TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE

    INTO #FKFINDER

    FROM SYSOBJECTS

    INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID

    WHERE SYSOBJECTS.XTYPE=’U’

    AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN (‘VARCHAR’,’NVARCHAR’,’CHAR’,’NCHAR’)

    ORDER BY TBLNAME,COLNAME

    DECLARE C1 CURSOR FOR

    SELECT TBLNAME,COLNAME FROM #FKFINDER ORDER BY TBLNAME,COLNAME

    OPEN C1

    FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME

    WHILE @@FETCH_STATUS <> -1

    BEGIN

    –SET @SQL = ‘SELECT ”’ + @TABLENAME + ”’ AS TABLENAME,”’ + @COLUMNNAME + ”’ AS COLUMNNAME,* FROM ‘ + @TABLENAME + ‘ WHERE ‘ + @COLUMNNAME + ‘ LIKE ”%’ + @SEARCHSTRING + ‘%”’

    SET @SQL = ‘IF EXISTS(SELECT * FROM ‘ + @TABLENAME + ‘ WHERE ‘ + @COLUMNNAME + ‘ LIKE ”%’ + @SEARCHSTRING + ‘%”) INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(”’ + @TABLENAME + ”’,”’ + @COLUMNNAME + ”’,” SELECT * FROM ‘ + @TABLENAME + ‘ WHERE ‘ + @COLUMNNAME + ‘ LIKE ””’ + @SEARCHSTRING + ””’ ”) ;’

    PRINT @SQL

    EXEC (@SQL)

    FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME

    END

    CLOSE C1

    DEALLOCATE C1

    SELECT * FROM #RESULTS

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • edylee

    SSC Journeyman

    Points: 95

    Here is a tool you can try (Free).

    http://www.sqlmgmt.com/ProductDetail.aspx?Id=101

    This tool helps you to search all columns of all tables in a database for a string keyword. In order to increase performance, it examines string type columns only in all tables to search for a given keyword.

  • regeter

    Old Hand

    Points: 328

    I got something similar which is just a plain query, not a tool or a sProc.

    [/url]

  • exuberantindia78

    Valued Member

    Points: 69

    Agree with ugly search, but it works well most of the time. Here is the code too…http://exuberantindia.com/?p=177

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

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