共計 5499 個字符，預(yù)計需要花費 14 分鐘才能閱讀完成。
SQLSERVER 中怎么解密存儲過程，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面丸趣 TV 小編將為大家詳細講解，有這方面需求的人可以來學(xué)習(xí)下，希望你能有所收獲。
加密測試的存儲過程
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE TYPE= P AND NAME= P_TEST)
DROP PROCEDURE P_TEST
GO
CREATE PROCEDURE P_TEST(@USERNAME VARCHAR(20),@MSG VARCHAR(20) OUTPUT)
WITH ENCRYPTION
AS
BEGIN
IF(SELECT COUNT(1) FROM Custs WHERE NAME=@USERNAME) 0
SET @MSG= 此用戶名存在
ELSE
SET @MSG= 此用戶名不存在
END
解密的存儲過程
Create PROCEDURE Decryption(@procedure sysname = NULL)
AS
SET NOCOUNT ON
DECLARE @intProcSpace bigint, @t bigint, @maxColID smallint,@procNameLength int
select @maxColID = max(subobjid) FROM
sys.sysobjvalues WHERE objid = object_id(@procedure)
–select @maxColID as Rows in sys.sysobjvalues
select @procNameLength = datalength(@procedure) + 29
DECLARE @real_01 nvarchar(max)
DECLARE @fake_01 nvarchar(max)
DECLARE @fake_encrypt_01 nvarchar(max)
DECLARE @real_decrypt_01 nvarchar(max),@real_decrypt_01a nvarchar(max)
declare @objtype varchar(2),@ParentName nvarchar(max)
select @real_decrypt_01a =
– 提取對象的類型如是存儲過程還是函數(shù)，如果是觸發(fā)器，還要得到其父對象的名稱
select @objtype=type,@parentname=object_name(parent_object_id)
from sys.objects where [object_id]=object_id(@procedure)
— 從 sys.sysobjvalues 里提出加密的 imageval 記錄
SET @real_01=(SELECT top 1 imageval FROM sys.sysobjvalues WHERE objid =
object_id(@procedure) and valclass = 1 order by subobjid)
– 創(chuàng)建一個臨時表
create table #output ([ident] [int] IDENTITY (1, 1) NOT NULL ,
[real_decrypt] NVARCHAR(MAX) )
– 開始一個事務(wù)，稍后回滾
BEGIN TRAN
– 更改原始的存儲過程，用短橫線替換
if @objtype= P
SET @fake_01= ALTER PROCEDURE + @procedure + WITH ENCRYPTION AS select 1
/**//* +REPLICATE(cast( * as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+ */
else if @objtype= FN
SET @fake_01= ALTER FUNCTION + @procedure + () RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1
/**//* +REPLICATE(cast( * as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+ */ END
else if @objtype= V
SET @fake_01= ALTER view + @procedure + WITH ENCRYPTION AS select 1 as col
/**//* +REPLICATE(cast( * as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+ */
else if @objtype= TR
SET @fake_01= ALTER trigger + @procedure + ON +@parentname+ WITH ENCRYPTION AFTER INSERT AS RAISERROR (N ,16,10)
/**//* +REPLICATE(cast( * as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+ */
EXECUTE (@fake_01)
– 從 sys.sysobjvalues 里提出加密的假的
SET @fake_encrypt_01=(SELECT top 1 imageval FROM sys.sysobjvalues WHERE objid =
object_id(@procedure) and valclass = 1 order by subobjid )
if @objtype= P
SET @fake_01= Create PROCEDURE + @procedure + WITH ENCRYPTION AS select 1
/**//* +REPLICATE(cast( * as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+ */
else if @objtype= FN
SET @fake_01= CREATE FUNCTION + @procedure + () RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1
/**//* +REPLICATE(cast( * as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+ */ END
else if @objtype= V
SET @fake_01= Create view + @procedure + WITH ENCRYPTION AS select 1 as col
/**//* +REPLICATE(cast( * as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+ */
else if @objtype= TR
SET @fake_01= Create trigger + @procedure + ON +@parentname+ WITH ENCRYPTION AFTER INSERT AS RAISERROR (N ,16,10)
/**//* +REPLICATE(cast( * as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+ */
– 開始計數(shù)
SET @intProcSpace=1
– 使用字符填充臨時變量
SET @real_decrypt_01 = replicate(cast( A as nvarchar(max)), (datalength(@real_01) /2 ))
– 循環(huán)設(shè)置每一個變量，創(chuàng)建真正的變量
– 每次一個字節(jié)
SET @intProcSpace=1
– 如有必要，遍歷每個 @real_xx 變量并解密
WHILE @intProcSpace =(datalength(@real_01) begin= set= real_decrypt_01= intprocspace= @intProcSpace+1 end= insert= output= select= –select= real_decrypt= as= from= –= ————————————-= declare= dbname= sysname= blankspaceadded= 0 int= basepos= currentpos= textlength= lineid= 1 addonlen= lfcr= 2 definedlength= 255 syscomtext= line= create= table= commenttext= text= collate= sys.sysobjvalues= ms_crs_syscom= cursor= local= for= order= by= ident= read= only= open= fetch= next= into= while= fetch_status= = 0
BEGIN
SELECT @BasePos = 1
SELECT @CurrentPos = 1
SELECT @TextLength = LEN(@SyscomText)
WHILE @CurrentPos != 0
BEGIN
– 通過回車查找行的結(jié)束
SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText,
@BasePos)
– 如果找到回車
IF @CurrentPos != 0
BEGIN
– 如果 @Lines 的長度的新值比設(shè)置的大就插入 @Lines 目前的內(nèi)容并繼續(xù)
While (isnull(LEN(@Line),0) + @BlankSpaceAdded +
@CurrentPos-@BasePos + @LFCR) @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) +
@BlankSpaceAdded)
INSERT #CommentText VALUES
(@LineId,
isnull(@Line, N) + isnull(SUBSTRING(@SyscomText,
@BasePos, @AddOnLen), N ))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SELECT @Line = isnull(@Line, N) +
isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N )
SELECT @BasePos = @CurrentPos+2
INSERT #CommentText VALUES(@LineId, @Line)
SELECT @LineId = @LineId + 1
SELECT @Line = NULL
END
ELSE
– 如果回車沒找到
BEGIN
IF @BasePos = 1= textlength= begin= while= blankspaceadded= @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength –
(isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
(@LineId,
isnull(@Line, N) + isnull(SUBSTRING(@SyscomText,
@BasePos, @AddOnLen), N ))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded =
0
END
SELECT @Line = isnull(@Line, N) +
isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1), N )
if LEN(@Line) @DefinedLength and charindex(,
@SyscomText, @TextLength+1 ) 0
BEGIN
SELECT @Line = @Line + , @BlankSpaceAdded = 1
END
END
END
END
FETCH NEXT FROM ms_crs_syscom into @SyscomText
END
IF @Line is NOT NULL
INSERT #CommentText VALUES(@LineId, @Line)
select Text from #CommentText order by LineId
CLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscom
DROP TABLE #CommentText
— ————————————-
– 結(jié)束從 sp_helptext 提取
— ————————————-
– 刪除用短橫線創(chuàng)建的存儲過程并重建原始的存儲過程
ROLLBACK TRAN
DROP TABLE #output
GO
啟用 DAC
SP_CONFIGURE remote admin connections
GO
—-0：僅允許本地連接使用 DAC，1：允許遠程連接使用 DAC
SP_CONFIGURE remote admin connections , 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
文件—— 新建—— 數(shù)據(jù)庫引擎查詢
服務(wù)器名稱：admin:. 或者 admin: 服務(wù)器名稱
登錄進去執(zhí)行解密操作
USE TEST
EXEC Decryption P_TEST
GO
看完上述內(nèi)容是否對您有幫助呢？如果還想對相關(guān)知識有進一步的了解或閱讀更多相關(guān)文章，請關(guān)注丸趣 TV 行業(yè)資訊頻道，感謝您對丸趣 TV 的支持。
