檢視 FixSqlInjection.sql 的原始碼

==用途==
* 被SQL資料隱碼攻擊過後的，大量資料修復。
** 備註：執行前此SQL預存程序前，請記得把要修的資料庫先備份，以防萬一。

==程式碼==
===字串取代版===
====FixSqlInjection====
<source lang="tsql">
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	player
-- Create date: 
-- Description: 被SQL資料隱碼攻擊過後的，大量資料修復。
-- =============================================
CREATE PROCEDURE [dbo].[FixSqlInjection]
(
	@SqlInjection AS NVARCHAR(4000)
)
AS
BEGIN
	--TEST用
	-- drop proc  [dbo].[FixSqlInjection]
	-- exec [dbo].[FixSqlInjection] '<script src=http://cn.daxia123.cn/cn.js></script>'
 
	SET NOCOUNT ON;
 
	DECLARE @TableName AS NVARCHAR(255);
	DECLARE @ID AS INT;
	DECLARE @ColName AS NVARCHAR(255);
	DECLARE @DataType AS NVARCHAR(255);
	DECLARE @SetStr AS NVARCHAR(4000);
	DECLARE @OutSqlStr AS NVARCHAR(4000);
 
	--避免@SqlInjection 內有單引號字元
	SET @SqlInjection = REPLACE(@SqlInjection,'''',''''''); 
 
	--欄位處理回圈1 (取出本DB內所有Table的名稱
	DECLARE db_cur1 CURSOR FOR
		SELECT	TableName=name FROM sysobjects 
			WHERE type='U' AND SUBSTRING (name,1,6) NOT IN ('aspnet') 
			ORDER BY name;
 
	OPEN db_cur1;
	FETCH NEXT FROM db_cur1 INTO @TableName;
 
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @SetStr = '';
 
		--欄位處理回圈2(取出指定Table的所有欄位名稱與DataType
		DECLARE db_cur2 CURSOR FOR
			SELECT ID=a.colorder, ColName=a.name, DataType=b.name
				FROM [syscolumns] a, [systypes] b, [sysobjects] c 
			WHERE a.id=c.id 
				AND a.xtype=b.xtype 
				AND c.type='U'
				AND b.name<>'sysname'
				AND c.name=@TableName
			ORDER BY a.colid;
 
		OPEN db_cur2;
		FETCH NEXT FROM db_cur2 INTO @ID, @ColName, @DataType;
		WHILE @@FETCH_STATUS = 0
		BEGIN
 
			IF ((@DataType = 'char') or (@DataType = 'varchar'))
			BEGIN
				IF (@SetStr = '')
					SET @SetStr = @SetStr + CHAR(13) + '    ' + '['+@ColName + '] = Replace([' +@ColName +'],''' + @SqlInjection + ''','''')';
				ELSE
					SET @SetStr = @SetStr + CHAR(13) + '   ,' + '['+@ColName + '] = Replace([' +@ColName + '],''' + @SqlInjection + ''','''')';
			END
			ELSE  IF ((@DataType = 'nchar') or (@DataType = 'nvarchar'))
			BEGIN
				IF (@SetStr = '')
					SET @SetStr = @SetStr + CHAR(13) + '    ' + '['+ @ColName + '] = Replace([' +@ColName +'],N''' + @SqlInjection + ''',N'''')';
				ELSE
					SET @SetStr = @SetStr + CHAR(13) + '   ,' + '['+ @ColName + '] = Replace([' +@ColName+ '],N''' + @SqlInjection + ''',N'''')';
			END
			ELSE IF (@DataType = 'text')
			BEGIN
				IF (@SetStr = '')
					SET @SetStr = @SetStr + CHAR(13) + '    ' + '['+@ColName + '] = Replace(convert(varchar(4000),[' +@ColName +']),''' + @SqlInjection + ''','''')';
				ELSE
					SET @SetStr = @SetStr + CHAR(13) + '   ,' + '['+@ColName + '] = Replace(convert(varchar(4000),[' +@ColName + ']),''' + @SqlInjection + ''','''')';
			END
			ELSE IF (@DataType = 'ntext')
			BEGIN
				IF (@SetStr = '')
					SET @SetStr = @SetStr + CHAR(13) + '    ' + '['+@ColName + '] = Replace(convert(nvarchar(4000),[' +@ColName +']),N''' + @SqlInjection + ''',N'''')';
				ELSE
					SET @SetStr = @SetStr + CHAR(13) + '   ,' + '['+@ColName + '] = Replace(convert(nvarchar(4000),[' +@ColName + ']),N''' + @SqlInjection + ''',N'''')';
			END

 
			FETCH NEXT FROM db_cur2 INTO @ID, @ColName, @DataType
		END
		CLOSE db_cur2
		DEALLOCATE db_cur2
 
		IF (@SetStr <> '')
		BEGIN
			SET @OutSqlStr = 'UPDATE [' + @TableName + '] SET ' + @SetStr;
 
			SET @OutSqlStr = @OutSqlStr + ';' +CHAR(13);
 
			--Print輸出
			PRINT @OutSqlStr;
 
			--執行(確定要執行時, 再把下行的 -- 拿掉)
			--exec sp_executesql @OutSqlStr;
		END
 
		FETCH NEXT FROM db_cur1 INTO @TableName
	END
	CLOSE db_cur1
	DEALLOCATE db_cur1
END
GO
</source>

===字串右邊Cut掉版本===
====FixSqlInjectionByCutStr====
<source lang="tsql">
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	player
-- Create date: 
-- Description: 被SQL資料隱碼攻擊過後的，大量資料修復。
-- =============================================
CREATE PROCEDURE [dbo].[FixSqlInjectionByCutStr]
(
	@SqlInjection AS NVARCHAR(4000)
)
AS
BEGIN
	--TEST用
	-- drop proc  [dbo].[FixSqlInjectionByCutStr]
	-- exec [dbo].[FixSqlInjectionByCutStr] '<script '
 
	SET NOCOUNT ON;
 
	DECLARE @TableName AS NVARCHAR(255);
	DECLARE @ID AS INT;
	DECLARE @ColName AS NVARCHAR(255);
	DECLARE @DataType AS NVARCHAR(255);
	DECLARE @SetStr AS NVARCHAR(4000);
	DECLARE @OutSqlStr AS NVARCHAR(4000);
 
	--避免@SqlInjection 內有單引號字元
	SET @SqlInjection = REPLACE(@SqlInjection,'''',''''''); 
 
	--欄位處理回圈1 (取出本DB內所有Table的名稱
	DECLARE db_cur1 CURSOR FOR
		SELECT	TableName=name FROM sysobjects 
			WHERE type='U' AND SUBSTRING (name,1,6) NOT IN ('aspnet') 
			ORDER BY name;
 
	OPEN db_cur1;
	FETCH NEXT FROM db_cur1 INTO @TableName;
 
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @SetStr = '';
 
		--欄位處理回圈2(取出指定Table的所有欄位名稱與DataType
		DECLARE db_cur2 CURSOR FOR
			SELECT ID=a.colorder, ColName=a.name, DataType=b.name
				FROM [syscolumns] a, [systypes] b, [sysobjects] c 
			WHERE a.id=c.id 
				AND a.xtype=b.xtype 
				AND c.type='U'
				AND b.name<>'sysname'
				AND c.name=@TableName
			ORDER BY a.colid;
 
		OPEN db_cur2;
		FETCH NEXT FROM db_cur2 INTO @ID, @ColName, @DataType;
		WHILE @@FETCH_STATUS = 0
		BEGIN
 
			IF ((@DataType = 'char') or (@DataType = 'varchar'))
			BEGIN
				IF (@SetStr = '')
					SET @SetStr = @SetStr + CHAR(13) + '    ' + '['+@ColName + '] = [dbo].[GetCutStr]([' +@ColName +'],''' + @SqlInjection + ''')';
				ELSE
					SET @SetStr = @SetStr + CHAR(13) + '   ,' + '['+@ColName + '] = [dbo].[GetCutStr]([' +@ColName + '],''' + @SqlInjection + ''')';
			END
			ELSE  IF ((@DataType = 'nchar') or (@DataType = 'nvarchar'))
			BEGIN
				IF (@SetStr = '')
					SET @SetStr = @SetStr + CHAR(13) + '    ' + '['+ @ColName + '] = [dbo].[GetCutStrN]([' +@ColName +'],N''' + @SqlInjection + ''')';
				ELSE
					SET @SetStr = @SetStr + CHAR(13) + '   ,' + '['+ @ColName + '] = [dbo].[GetCutStrN]([' +@ColName+ '],N''' + @SqlInjection + ''')';
			END
			ELSE IF (@DataType = 'text')
			BEGIN
				IF (@SetStr = '')
					SET @SetStr = @SetStr + CHAR(13) + '    ' + '['+@ColName + '] = [dbo].[GetCutStr](convert(varchar(4000),[' +@ColName +']),''' + @SqlInjection + ''')';
				ELSE
					SET @SetStr = @SetStr + CHAR(13) + '   ,' + '['+@ColName + '] = [dbo].[GetCutStr](convert(varchar(4000),[' +@ColName + ']),''' + @SqlInjection + ''')';
			END
			ELSE IF (@DataType = 'ntext')
			BEGIN
				IF (@SetStr = '')
					SET @SetStr = @SetStr + CHAR(13) + '    ' + '['+@ColName + '] = [dbo].[GetCutStrN](convert(nvarchar(4000),[' +@ColName +']),N''' + @SqlInjection + ''')';
				ELSE
					SET @SetStr = @SetStr + CHAR(13) + '   ,' + '['+@ColName + '] = [dbo].[GetCutStrN](convert(nvarchar(4000),[' +@ColName + ']),N''' + @SqlInjection + ''')';
			END

 
			FETCH NEXT FROM db_cur2 INTO @ID, @ColName, @DataType
		END
		CLOSE db_cur2
		DEALLOCATE db_cur2
 
		IF (@SetStr <> '')
		BEGIN
			SET @OutSqlStr = 'UPDATE [' + @TableName + '] SET ' + @SetStr;
 
			SET @OutSqlStr = @OutSqlStr + ';' +CHAR(13);
 
			--Print輸出
			PRINT @OutSqlStr;
 
			--執行(確定要執行時, 再把下行的 -- 拿掉)
			--exec sp_executesql @OutSqlStr;
		END
 
		FETCH NEXT FROM db_cur1 INTO @TableName
	END
	CLOSE db_cur1
	DEALLOCATE db_cur1
END
GO
</source>

====GetCutStr(@StrSrc,@KeyWord)====
<source lang="tsql">
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	player
-- Create date: 
-- Description:	Cut掉右邊字串
-- =============================================
CREATE FUNCTION [dbo].[GetCutStr]
(
	@StrSrc as varchar(4000)
	,@KeyWord as varchar(4000)
)
RETURNS varchar(4000)
AS
BEGIN
	--TEST
	--drop FUNCTION [dbo].[GetCutStr]
	--select [dbo].[GetCutStr]('abc1234<script ','<script ')

	DECLARE @ResultVar as varchar(4000);
	DECLARE @index as int;

	if (@StrSrc is not null)
		begin
			select @index = charindex(@KeyWord,@StrSrc);

			if (@index <> 0 ) 
				SELECT @ResultVar = substring(@StrSrc, 0, @index);
			else
				SELECT @ResultVar = @StrSrc;
		end
	else
		SELECT @ResultVar = @StrSrc;

	RETURN @ResultVar;
END
GO
</source>

====GetCutStrN(@StrSrc,@KeyWord)====
<source lang="tsql">
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	player
-- Create date: 
-- Description:	Cut掉右邊字串
-- =============================================
CREATE FUNCTION [dbo].[GetCutStrN]
(
	@StrSrc as nvarchar(4000)
	,@KeyWord as nvarchar(4000)
)
RETURNS nvarchar(4000)
AS
BEGIN
	--TEST
	--drop FUNCTION [dbo].[GetCutStrN]
	--select [dbo].[GetCutStrN](N'abc1234<script ',N'<script ')

	DECLARE @ResultVar as nvarchar(4000);
	DECLARE @index as int;

	if (@StrSrc is not null)
		begin
			select @index = charindex(@KeyWord,@StrSrc);

			if (@index <> 0 ) 
				SELECT @ResultVar = substring(@StrSrc, 0, @index);
			else
				SELECT @ResultVar = @StrSrc;
		end
	else
		SELECT @ResultVar = @StrSrc;

	RETURN @ResultVar;
END
GO
</source>

[[Category:MS-SQL預存程序]]