Search specific string in data in all tables

Question

Post reply

Search specific string in data in all tables

joemai

Hall of Fame

Points: 3496
More actions
November 6, 2007 at 12:26 pm

#73853

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

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

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

Venkataraman-408293 SSChasing Mays Points: 650 More actions · Answer 1

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: 323518 More actions · Answer 2

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 More actions · Answer 3

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: 338 More actions · Answer 4

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

[/url]

exuberantindia78 Valued Member Points: 69 More actions · Answer 5

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