数据库名:db其中包含两张表 ,还有其他表..表tb1
person_name person_birthday person_info张三 1981-6-1 健康李四 1976-9-12 有病史王五 1972-7-14 暂无
表tb2person_id person_address person_info1 无锡 暂无2 苏州 无病史3 上海 暂无<>
问题(1):假设现在只知道数据库名.不知道表名,如何知道数据中所有表包含 "病史" 关键字的表?问题(2):如何将数据库中所有表中"暂无"关键字都替换为""?问题(3):假设表tb2的"person_address"字段被人注入了脚本,该如何恢复?或许别
表中也被注入类似的脚本.如何进行批量处理此类问题?person_address <script>www.aje.com</script>无锡苏州<script>www.ajfie.com</script><script>www.ae.com</script>上海<script>je.com</script>
person_name person_birthday person_info张三 1981-6-1 健康李四 1976-9-12 有病史王五 1972-7-14 暂无
表tb2person_id person_address person_info1 无锡 暂无2 苏州 无病史3 上海 暂无<>
问题(1):假设现在只知道数据库名.不知道表名,如何知道数据中所有表包含 "病史" 关键字的表?问题(2):如何将数据库中所有表中"暂无"关键字都替换为""?问题(3):假设表tb2的"person_address"字段被人注入了脚本,该如何恢复?或许别
表中也被注入类似的脚本.如何进行批量处理此类问题?person_address <script>www.aje.com</script>无锡苏州<script>www.ajfie.com</script><script>www.ae.com</script>上海<script>je.com</script>
@value varchar(200)
AS
--求test库中包含值为@value的表和列名--存储表名和列名
IF object_id('tabss') IS NOT NULL
exec('drop table tabss')
CREATE TABLE Tabss(id int identity(1,1),tabname varchar(100),colName varchar(100))--查询某表某列是否包含某个值
IF object_id('ysgs') IS NOT NULL
exec('drop proc ysgs')
exec('create PROC ysgs(@tab varchar(100),@col varchar(100))
AS
exec(''select 1 from ''+@tab+'' where ''+@col+'' like ''''%'+@value+'%'''''')')--将结果存入tabss表中
EXEC master.dbo.xp_execresultset 'SELECT ''exec ysgs ''''''+object_name(id)+'''''',''''''+name+'''''';if @@rowcount>0 insert tabss (colname,tabname)values(''''''+name+'''''',''''''+object_name(id)+'''''')'' FROM syscolumns s WHERE xtype in(SELECT xtype FROM systypes s2 WHERE name in(''char'',''varchar'',''nchar'',''nvarchar''))
AND id in(SELECT id FROM sysobjects s2 WHERE xtype=''u'')',N'test'
GO
/*调用
exec xb_GetTableNameAndColNameForValue 'aa_1'
SELECT * FROM tabss
*/
@value sql_variant, --要搜索的数据
@precision bit=1 --1=仅根据sql_variant中的数据类型查找对应类型的数据列.<>1,查询兼容的所有列,字符数据使用like匹配
AS
SET NOCOUNT ON
IF @value IS NULL RETURN--数据类型处理
SELECT xtype INTO #t FROM systypes
WHERE name=SQL_VARIANT_PROPERTY(@value,N'BaseType')--扩展数据类型及查询处理语句
DECLARE @sql nvarchar(4000),@sql1 nvarchar(4000)
IF @precision=1
SET @sql=CASE SQL_VARIANT_PROPERTY(@value,N'BaseType')
WHEN N'text' THEN N' LIKE N''%''+CAST(@value as varchar(8000))+''%'''
WHEN N'ntext' THEN N' LIKE ''%''+CAST(@value as nvarchar(4000))+''%'''
ELSE N'=@value' END
ELSE
BEGIN
SET @sql=CAST(SQL_VARIANT_PROPERTY(@value,N'BaseType') as sysname)
IF @sql LIKE N'%char' or @sql LIKE N'%text'
BEGIN
INSERT #t SELECT xtype FROM systypes
WHERE name LIKE N'%char' or name LIKE N'%text'
SELECT @sql=N' LIKE N''%''+CAST(@value as '
+CASE
WHEN LEFT(@sql,1)=N'n' THEN ' nvarchar(4000)'
ELSE 'varchar(8000)' END
+N')+N''%'''
END
ELSE IF @sql LIKE N'%datetime'
BEGIN
INSERT #t SELECT xtype FROM systypes
WHERE name LIKE N'%datetime'
SET @sql=N'=@value'
END
ELSE IF @sql LIKE N'%int'
OR @sql LIKE N'%money'
OR @sql IN(N'real',N'float',N'decimal',N'numeric')
BEGIN
INSERT #t SELECT xtype FROM systypes
WHERE name LIKE N'%int'
OR name LIKE N'%money'
OR name IN(N'real',N'float',N'decimal')
SET @sql=N'=@value'
END
ELSE
SET @sql=N'=@value'
END
--保存结果的临时表
CREATE TABLE #(TableName sysname,FieldName sysname,Type sysname,SQL nvarchar(4000))DECLARE tb CURSOR LOCAL
FOR
SELECT N'SELECT * FROM '
+QUOTENAME(USER_NAME(o.uid))
+N'.'+QUOTENAME(o.name)
+N' WHERE '+QUOTENAME(c.name)
+@sql,
N'INSERT # VALUES(N'+QUOTENAME(o.name,N'''')
+N',N'+QUOTENAME(c.name,N'''')
+N',N'+QUOTENAME(QUOTENAME(t.name)+CASE
WHEN t.name IN (N'decimal',N'numeric')
THEN N'('+CAST(c.prec as varchar)+N','+CAST(c.scale as varchar)+N')'
WHEN t.name=N'float'
OR t.name like N'%char'
OR t.name like N'%binary'
THEN N'('+CAST(c.prec as varchar)+N')'
ELSE N'' END,N'''')
+N',@sql)'
FROM sysobjects o,syscolumns c,systypes t,#t tt
WHERE o.id=c.id
AND c.xusertype=t.xusertype
AND t.xtype=tt.xtype
AND OBJECTPROPERTY(o.id,N'IsUserTable')=1OPEN tb
FETCH tb INTO @sql,@sql1
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql1=N'IF EXISTS('+@sql+N') '+@sql1
EXEC sp_executesql @sql1,N'@value sql_variant,@sql nvarchar(4000)',@value,@sql
FETCH tb INTO @sql,@sql1
END
CLOSE tb
DEALLOCATE tb
SELECT * FROM #
AS
BEGIN
DECLARE @name VARCHAR(40)
DECLARE @S NVARCHAR(1000),@C INT
SET @f=''
EXEC('declare cur cursor for select name from syscolumns where id=object_id('''+@t+''') and xtype in(SELECT xtype FROM systypes s2 WHERE name in(''char'',''varchar'',''nchar'',''nvarchar''))')
OPEN cur
FETCH NEXT FROM cur INTO @name
WHILE @@FETCH_STATUS=0
BEGIN
SET @S='select @s=count(1) from '+@t+' where ['+@name +'] like ''%'+@v+'%'''
EXEC sp_executesql @S,N'@s int out',@C OUT
IF @C>0
SET @f=@f+@name+','
FETCH NEXT FROM cur INTO @name
END
CLOSE cur
DEALLOCATE cur
RETURN
END go
EXEC sp_msforeachtable
@command1 = N'declare @l varchar(1000); exec getFieldAll ''?'',''aa_1'',@l out;if @l<>'''' select ''?'' 表名,@l 列名
'
CREATE proc getFieldAll(@t VARCHAR(100),@v VARCHAR(20))
AS
BEGIN
DECLARE @name VARCHAR(40)
DECLARE @S NVARCHAR(1000),@C INT
EXEC('declare cur cursor for select name from syscolumns where id=object_id('''+@t+''') and xtype in(SELECT xtype FROM systypes s2 WHERE name in(''char'',''varchar'',''nchar'',''nvarchar''))')
OPEN cur
FETCH NEXT FROM cur INTO @name
WHILE @@FETCH_STATUS=0
BEGIN
SET @S='select @s=count(1) from '+@t+' where ['+@name +'] like ''%'+@v+'%'''
EXEC sp_executesql @S,N'@s int out',@C OUT
IF @C>0
exec('update '+@t +' set '+@name +'=replace('+@name+','''+@v+''',''?'')')
FETCH NEXT FROM cur INTO @name
END
CLOSE cur
DEALLOCATE cur
RETURN
END
go
EXEC sp_msforeachtable @command1 = N'exec getFieldAll ''?'',''暂无'''
declare @tableName nvarchar(256), @sql nvarchar(4000)
set @tableName=parsename(''?'',1)
set @sql=N''''select @sql=@sql+N'' union all select ''''''+@tableName+'''''' as tableName from ''+@tableName+'' where charindex(N''''病史'''',''+name+'')>0''
from syscolumns
where id=object_id(@tableName) and type_name(xtype) in (''varchar'',''nvarchar'')set @sql=stuff(@sql,1,10,'''')
--print @sql
exec(@sql)
'
exec sp_msforeachtable N'
declare @tableName nvarchar(256), @sql nvarchar(4000),@value nvarchar(100)
set @tableName=parsename(''?'',1)
set @sql=N''''
set @value=N''暂无''select @sql=@sql+N'' update ''+@tableName+'' set ''+name+''=replace(''+name+'',N''''''+@value+'''''','''''''') where charindex(N''''''+@value+'''''',''+name+'')>0''
from syscolumns
where id=object_id(@tableName) and type_name(xtype) in (''varchar'',''nvarchar'',''char'',''nchar'')--print @sql
exec(@sql)
'
1、遍历所有的表
2、遍历1中表的所有字段
3、用动态SQL拼出语句,如果该表的该字段LIKE 关键字,就把表名、字段名插入到一个结果表里这样1、2两个问题就可以解决了。伪代码如下:DECLARE @SQL nvarchar(1000)CREATE TABLE temp_result(table_name sysname,col_name sysname) FOR each @table_name in (SELECT name FROM sys.objects where type = 'U' AND is_ms_shipped = 0) FOR each @col_name in (SELECT name FROM sys.columns WHERE object_id = OBJECT_ID(@table_name))
SELECT @SQL = 'IF EXISTS(SELECT 1 FROM @table_name WHERE @col_name = '病史') INSERT INTO temp_result VALUES(@table_name,@col_name)'
END FOR
END FOR