SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Search specific string in data in all tables


Search specific string in data in all tables

Author
Message
joemai
joemai
Right there with Babe
Right there with Babe (772 reputation)Right there with Babe (772 reputation)Right there with Babe (772 reputation)Right there with Babe (772 reputation)Right there with Babe (772 reputation)Right there with Babe (772 reputation)Right there with Babe (772 reputation)Right there with Babe (772 reputation)

Group: General Forum Members
Points: 772 Visits: 303
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
Venkataraman-408293
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 126
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
Smile
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73225 Visits: 40960
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
edylee
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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.
regeter
regeter
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 92
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]
exuberantindia78
exuberantindia78
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 0
Agree with ugly search, but it works well most of the time. Here is the code too...http://exuberantindia.com/?p=177
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search