星期一, 4月 16, 2012

MSSQL SQL-Injection 時的資料復原

單一Table單一欄位的復原法:

UPDATE tableName SET columnName = REPLACE ( convert(varchar(8000), columnName ) , '要被取代的字串', '取代的字串' ) WHERE columnName LIKE '%要被取代的字串%' ;


改寫為 Stored Procedure (只使用單引號):
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE procedureName
AS
BEGIN
DECLARE cursor_T CURSOR FOR
(SELECT obj.name AS tableName,
clum.name AS colName
FROM dbo.sysobjects AS obj INNER JOIN
dbo.syscolumns AS clum ON obj.id = clum.id INNER JOIN
dbo.systypes AS sType ON clum.xusertype = sType.xusertype
WHERE (obj.xtype = 'U') AND (clum.xusertype IN (35, 99, 167, 231))
)
DECLARE @tableName nvarchar(255),
@col nvarchar(255)
OPEN cursor_T
FETCH NEXT FROM cursor_T INTO @tableName,@col
WHILE (@@FETCH_STATUS = 0)
BEGIN EXEC('UPDATE ' + @tableName +
' SET ' + @col + ' = REPLACE( convert(varchar(8000),' + @col + ') , ''要被取代的字串'', ''取代的字串'' )
WHERE ' + @col + ' LIKE ''%要被取代的字串%''')
FETCH NEXT FROM cursor_T INTO @tableName,@col
END
CLOSE cursor_T
DEALLOCATE cursor_T
END


Stored Procedure 執行指令:

exec procedureName