Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Search specific string in data in all tables Expand / Collapse
Author
Message
Posted Tuesday, November 6, 2007 12:26 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 11, 2014 4:28 PM
Points: 242, Visits: 224
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



Post #419243
Posted Tuesday, November 6, 2007 12:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 7, 2014 5:10 AM
Points: 137, Visits: 124
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
:)
Post #419254
Posted Tuesday, November 6, 2007 1:35 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:28 AM
Points: 12,910, Visits: 32,013
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #419285
Posted Monday, June 13, 2011 10:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 14, 2011 5:05 PM
Points: 1, Visits: 2
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.

Post #1124732
Posted Monday, December 10, 2012 10:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 21, 2013 11:39 AM
Points: 7, Visits: 85
I got something similar which is just a plain query, not a tool or a sProc.
[url=http://fullparam.wordpress.com/2012/09/07/fck-it-i-am-going-to-search-all-tables-all-collumns/][/url]
Post #1394713
Posted Thursday, August 29, 2013 6:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 29, 2013 6:03 AM
Points: 1, Visits: 0
Agree with ugly search, but it works well most of the time. Here is the code too...http://exuberantindia.com/?p=177
Post #1489661
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse