不灭的焱

革命尚未成功,同志仍须努力下载JDK17

作者:php-note.com  发布于:2022-08-30 18:26  分类:MySQL/NoSQL  编辑

一、存储过程加密

本方法可用于加密SQL存储过程、函数或者触发器

使用 WITH ENCRYPTION 选项:WITH ENCRYPTION 子句对用户隐藏存储过程的文本

例子:

IF OBJECT_ID(N'[Pro_Encrypt_Test]') IS NOT NULL
    DROP PROCEDURE [Pro_Encrypt_Test]
GO
CREATE PROCEDURE [Pro_Encrypt_Test]
WITH ENCRYPTION
AS
BEGIN
  SELECT * FROM T_TEST
END
GO 

建好存储过程后,用下面的语句查看存储过程

EXEC sp_helptext Pro_Encrypt_Test 

结果是:

The text for object 'Pro_Encrypt_Test' is encrypted. 

把存储过程加密后,可以不让别人在数据库中直接看到存储过程的内容。

当然网上存在不少解密的方法,所以这样的加密不是真正安全的,只是增加别人查看存储过程的难度而已。  

二、存储过程解密

IF OBJECT_ID ( N'SP_SQLJM2008', N'P' ) IS NOT NULL
	DROP PROCEDURE SP_SQLJM2008

GO 

CREATE PROCEDURE SP_SQLJM2008 ( @ObjectName VARCHAR(50) ) 
WITH ENCRYPTION
AS
BEGIN

	SET NOCOUNT ON
	--------------------------- CSDN:j9988 copyright:2004.01.05 
	--------------------------- V3.1
	--------------------------- 破解字节不受限制,适用于SQLSERVER2000, SQLSERVER2005存储过程,函数,视图,触发器
	--------------------------- 发现有错,请E_MAIL:CSDNj9988@tom.com
	
	
	--------------------------- 整理SQLSERVER2000、注释:郭长海
	--------------------------- 整理SQLSERVER2005、注释:郭长海
	--------------------------- 整理SQLSERVER2008、注释:郭长海
	
	
	DECLARE @Version CHAR(4),
			@ObjectID INT
	
	DECLARE @n INT, 
			@j INT, 
			@k INT, 
			@i_copy INT

	DECLARE @colid INT, 
			@encrypted INT, 
			@MaxNumber INT
	

	DECLARE @ExecSqlTxt NVARCHAR (4000), 
			@TestSqlTxt NVARCHAR (4000), 
			@CHR NVARCHAR(4000)

	DECLARE @OrigSpText1 NVARCHAR (4000), 
			@OrigSpText2 NVARCHAR (4000), 
			@OrigSpText3 NVARCHAR (4000), 
			@ResultSql NVARCHAR (4000), 
			@ResultLen INT
	
	DECLARE @i INT, 
			@status INT, 
			@type VARCHAR ( 10 ), 
			@parentid INT
	
	DECLARE @ConnectText NVARCHAR (4000), 
			@PrintText NVARCHAR (4000), 
			@Pos INT, 
			@Temp INT, 
			@Line INT

	

	
	------------------------------------------------------------------------------------------------------------ 
	
	SELECT @ObjectID = OBJECT_ID ( @ObjectName ) 

	SELECT @type = xtype, @parentid = parent_obj FROM SYSOBJECTS WHERE ID = @ObjectID
	
	SELECT @Version = CASE WHEN SUBSTRING(@@Version, 1, 30) LIKE '%2000%' THEN '2000' 
							WHEN SUBSTRING(@@Version, 1, 30) LIKE '%2005%' THEN '2005' 
							WHEN SUBSTRING(@@Version, 1, 30) LIKE '%2008%' THEN '2008' END
	
	IF @Version NOT IN ('2000', '2005', '2008')
		RETURN

	BEGIN TRAN

	SELECT id, number, colid, ctext, encrypted, status 
	INTO #TempSysComments1
	FROM SYSCOMMENTS WHERE ID = @ObjectID
	 
	SELECT @MaxNumber = MAX ( number ) FROM #TempSysComments1

	IF @Version IN ('2005', '2008')
	BEGIN
		SET @i_copy = 1
		WHILE EXISTS(SELECT colid FROM #TempSysComments1 WHERE colid = @i_copy)
		BEGIN
			UPDATE #TempSysComments1
			SET ctext = (SELECT TOP 1 SUBSTRING(imageval, (@i_copy - 1)*8000 + 1, 8000) FROM sys.sysobjvalues WHERE objid = @ObjectID AND valclass = 1 ORDER BY subobjid )
			WHERE colid = @i_copy

			SET @i_copy = @i_copy + 1
		END
	END


	------	设计同名测试明文,创建测试密文。
	SET @k = 0
	WHILE @k <= @MaxNumber 
	BEGIN
		IF EXISTS ( SELECT 1 FROM SYSCOMMENTS WHERE ID = @ObjectID AND number = @k ) 
		BEGIN
			------	设计测试 ALTER 明文
			IF @type = 'P'
				SET @TestSqlTxt = ( CASE 
						WHEN @MaxNumber > 1 THEN
							'ALTER PROCEDURE ' + @ObjectName + ';' + RTRIM ( @k ) + ' WITH ENCRYPTION AS '
						ELSE 
							'ALTER PROCEDURE ' + @ObjectName + ' WITH ENCRYPTION AS '
						END ) 
			
			IF @type = 'TR'
				SET @TestSqlTxt = 'ALTER TRIGGER ' + @ObjectName + ' ON ' + OBJECT_NAME ( @parentid ) + ' WITH ENCRYPTION FOR INSERT AS PRINT 1 '
			
			IF @type = 'FN' OR @type = 'TF' OR @type = 'IF'
				SET @TestSqlTxt = ( CASE @type 
						WHEN 'TF' THEN 
							'ALTER FUNCTION ' + @ObjectName + ' ( @a CHAR ( 1 ) ) RETURNS @b TABLE ( a VARCHAR ( 10 ) ) WITH ENCRYPTION AS BEGIN INSERT @b SELECT @a RETURN END'
						WHEN 'FN' THEN
							'ALTER FUNCTION ' + @ObjectName + ' ( @a CHAR ( 1 ) ) RETURNS CHAR ( 1 ) WITH ENCRYPTION AS BEGIN RETURN @a END'
						WHEN 'IF' THEN
							'ALTER FUNCTION ' + @ObjectName + ' ( @a CHAR ( 1 ) ) RETURNS TABLE WITH ENCRYPTION AS RETURN SELECT @a AS a'
						END ) 
			
			IF @type = 'V'
				SET @TestSqlTxt = 'ALTER VIEW ' + @ObjectName + ' WITH ENCRYPTION AS SELECT 1 AS f'
			
			--------------------------------------------------------------------------- 
			------	创建测试密文

			SET @CHR = REPLICATE(N'-', 4000)

			SET @ExecSqlTxt = N'EXEC(@SQL'

			SELECT @ExecSqlTxt = @ExecSqlTxt + N'+@' FROM #TempSysComments1 WHERE ID = @ObjectID

			SET @ExecSqlTxt = @ExecSqlTxt + N')'

			EXEC sp_executesql @ExecSqlTxt, N'@SQL NVARCHAR(4000), @ NVARCHAR(4000)', @SQL = @TestSqlTxt, @ = @CHR

			SELECT id, number, colid, ctext, encrypted, status 
			INTO #TempSysComments2
			FROM SYSCOMMENTS WHERE ID = @ObjectID

			IF @Version IN ('2005', '2008')
			BEGIN
				SET @i_copy = 1
				WHILE EXISTS(SELECT colid FROM #TempSysComments2 WHERE colid = @i_copy)
				BEGIN
					UPDATE #TempSysComments2
					SET ctext = (SELECT TOP 1 SUBSTRING(imageval, (@i_copy - 1)*8000 + 1, 8000) FROM sys.sysobjvalues WHERE objid = @ObjectID AND valclass = 1 ORDER BY subobjid )
					WHERE  ID = @ObjectID AND colid = @i_copy

					SET @i_copy = @i_copy + 1
				END
			END

			SELECT id, number, colid, ctext, encrypted, status 
			INTO #TempSysComments3
			FROM SYSCOMMENTS WHERE ID = @ObjectID

			UPDATE #TempSysComments3 SET ctext = CONVERT(VARBINARY(8000), CONVERT(NVARCHAR(4000), REPLICATE('-', 8000))) WHERE ID = @ObjectID

			UPDATE #TempSysComments3 SET ctext = CONVERT(VARBINARY(8000), CONVERT(NVARCHAR(4000), REPLACE(@TestSqlTxt,'ALTER','CREATE') + REPLICATE('-', 8000))) WHERE ID = @ObjectID AND colid = 1
	
		END
	
		SET @k = @k + 1
	
	END

	------------------------------------------------------------------------------------------------------------ 
	------	由测试明文与测试密文进行异或运算得出密匙,由密匙与原密文进行异或运算得出原明文。
	------	取测试密文

	SET @Line = 0
	SET @ConnectText = N''
	SET @k = 0
	WHILE @k <= @MaxNumber 
	BEGIN
		IF EXISTS ( SELECT 1 FROM SYSCOMMENTS WHERE ID = @ObjectID AND number = @k ) 
		BEGIN
			SELECT @colid = MAX ( colid ) FROM #TempSysComments1 WHERE ID = @ObjectID AND number = @k

			SET @n = 1
			WHILE @n <= @colid
			BEGIN
				------	取原文密文
				SELECT @OrigSpText1 = ctext, @encrypted = encrypted, @status = status FROM #TempSysComments1 WHERE ID = @ObjectID AND colid = @n AND number = @k

				IF @encrypted = 1
				BEGIN
					------	取测试密文
					SELECT @OrigSpText2 = ctext FROM #TempSysComments2 WHERE ID = @ObjectID AND colid = @n AND number = @k

					------	取测试明文
					SELECT @OrigSpText3 = ctext FROM #TempSysComments3 WHERE ID = @ObjectID AND colid = @n AND number = @k

					SET @ResultSql = N''
					SET @ResultLen = DATALENGTH ( @OrigSpText1 ) / 2
					
					SET @i = 1
					WHILE @i <= @ResultLen
					BEGIN
						SET @ResultSql = @ResultSql + NCHAR(UNICODE(SUBSTRING(@OrigSpText1, @i, 1 )) ^
															UNICODE(SUBSTRING(@OrigSpText2, @i, 1 )) ^
															UNICODE(SUBSTRING(@OrigSpText3, @i, 1 )))

						SET @i = @i + 1
					END
				END
				ELSE
				BEGIN
					SELECT @ResultSql = CONVERT ( NVARCHAR (4000), CASE WHEN @status & 2 = 2 THEN uncompress ( ctext ) ELSE ctext END ) 
					FROM #TempSysComments1 WHERE ID = @ObjectID AND colid = @n AND number = @k
				END
		
				
				------------------------------------------------------------------ 
				----	对解密后的文件进行显示格式还原

				SET @Temp = 1
				SET @Pos = 0
				
				WHILE @Temp > 0
				BEGIN
					SET @Temp = CHARINDEX(NCHAR(13)+NCHAR(10), @ResultSql)
				
					IF @Temp > 0
					BEGIN
						SET @PrintText = SUBSTRING (@ResultSql, 1, @Temp - 1)
						SET @PrintText = ISNULL (@PrintText, '')
						SET @ResultSql = SUBSTRING (@ResultSql, @Temp + 2, LEN(@ResultSql) - LEN(@PrintText) - 2)
						SET @ResultSql = ISNULL (@ResultSql, '')
						IF @Pos = 0
						BEGIN
							SET @PrintText = @ConnectText + @PrintText
							SET @ConnectText = N''
						END

						SET @Pos = CHARINDEX(NCHAR(13)+NCHAR(10), @PrintText)
						IF @Pos > 0
						BEGIN
							SET @ResultSql = SUBSTRING (@PrintText, @Pos + 2, LEN(@PrintText) - @Pos - 1) + NCHAR(13) + NCHAR(10) + @ResultSql
							SET @PrintText = SUBSTRING (@PrintText, 1, @Pos - 1)
						END
							
						IF @Version IN ('2005', '2008')
						BEGIN
							SET @Line = (@Line + 10) / 10 * 10
							SET @PrintText = REPLACE(@PrintText, N' ', N'^=*')

							WHILE LEN(@PrintText) > 0
							BEGIN
								IF LEN(@PrintText) > 100
								BEGIN
									PRINT RIGHT(N'0000000000' + CONVERT(NVARCHAR(10), @Line), 10 ) + SUBSTRING(@PrintText, 1, 100)
									SET @PrintText = SUBSTRING(@PrintText, 101, LEN(@PrintText) - 100)
									SET @Line = @Line + 1
								END
								ELSE
								BEGIN
									PRINT RIGHT(N'0000000000' + CONVERT(NVARCHAR(10), @Line), 10 ) + @PrintText
									SET @PrintText = ''
									SET @Line = @Line + 1
								END
							END
						END

						IF @Version = '2000'
							PRINT @PrintText

						SET @Pos = 1

					END
					ELSE
					BEGIN
						SET @ConnectText = @ConnectText + @ResultSql
						SET @Temp = CHARINDEX(NCHAR(13)+NCHAR(10), @ConnectText)
						IF @Temp > 0
						BEGIN
							SET @ResultSql = @ConnectText
							SET @ConnectText = N''
						END
					END
				
				END
				------------------------------------------------------------------ 

				SET @n = @n + 1
			END
		END
		SET @k = @k + 1
	END
			
	IF @Version IN ('2005', '2008')
	BEGIN
		SET @PrintText = @ConnectText
		SET @PrintText = REPLACE(@PrintText, N' ', N'^=*')

		IF LEN(@PrintText) > 0
			SET @Line = (@Line + 10) / 10 * 10

		WHILE LEN(@PrintText) > 0
		BEGIN
			IF LEN(@PrintText) > 100
			BEGIN
				PRINT RIGHT(N'0000000000' + CONVERT(NVARCHAR(10), @Line), 10 ) + SUBSTRING(@PrintText, 1, 100)
				SET @PrintText = SUBSTRING(@PrintText, 101, LEN(@PrintText) - 100)
				SET @Line = @Line + 1
			END
			ELSE
			BEGIN
				PRINT RIGHT(N'0000000000' + CONVERT(NVARCHAR(10), @Line), 10 ) + @PrintText
				SET @PrintText = ''
				SET @Line = @Line + 1
			END
		END
	END

	IF @Version = '2000'
		PRINT @ConnectText

	------------------------------------------------------------------------------------------------------------ 

	DROP TABLE #TempSysComments1
	DROP TABLE #TempSysComments2
	DROP TABLE #TempSysComments3

	ROLLBACK TRAN

END


--------------------------------------------------------------------------------------------------------------------------- 

GO 

IF OBJECT_ID ( N'SP_SQLJM', N'P' ) IS NOT NULL
	DROP PROCEDURE SP_SQLJM

GO 

CREATE PROCEDURE SP_SQLJM ( @ObjectName VARCHAR(50) ) 
WITH ENCRYPTION
AS
BEGIN

	DECLARE @dossqlcmd VARCHAR(8000)
	DECLARE @SQL_PRINT NVARCHAR(4000)
	DECLARE @SQL_SYNTAX NVARCHAR(4000)
	DECLARE @Version CHAR(4)
	DECLARE @Line INT


	SELECT @Version = CASE WHEN SUBSTRING(@@Version, 1, 30) LIKE '%2000%' THEN '2000' 
							WHEN SUBSTRING(@@Version, 1, 30) LIKE '%2005%' THEN '2005' 
							WHEN SUBSTRING(@@Version, 1, 30) LIKE '%2008%' THEN '2008' END
		
	IF @Version IN ('2005', '2008')
	BEGIN

		----	SELECT @dossqlcmd = 'sqlcmd -S '+RTRIM(ps.hostname)+' -d '+db.name+' -U '+@DBUser+' -P '+@DBPass+' -A -q "EXEC SP_JM1 ''' + @ObjectName + '''"'
		SELECT @dossqlcmd = 'sqlcmd -A -q "EXEC '+db.name+'..SP_SQLJM2008 ''' + @ObjectName + '''"'
		FROM master..sysprocesses ps, master..sysdatabases db
		WHERE ps.spid = @@spid AND ps.dbid = db.dbid
		
		IF OBJECT_ID('tempdb..#TEMP_SYNTAX') IS NOT NULL
			DROP TABLE #TEMP_SYNTAX

		CREATE TABLE  #TEMP_SYNTAX ( ctext NVARCHAR (4000) ) 

		INSERT INTO #TEMP_SYNTAX( ctext ) 
		EXEC master..xp_cmdshell @dossqlcmd

		SET @Line = 10
		SET @SQL_PRINT = N''

		-----	select * from #TEMP_SYNTAX where ISNUMERIC ( SUBSTRING(ctext, 1, 10) ) = 0 ORDER BY ctext

		DECLARE CUR_SYNTAX CURSOR FOR
				SELECT ISNULL(ctext, N'') FROM #TEMP_SYNTAX WHERE ctext IS NOT NULL ORDER BY ctext

		OPEN CUR_SYNTAX

		FETCH NEXT FROM CUR_SYNTAX INTO @SQL_SYNTAX

		WHILE @@FETCH_STATUS = 0 
		BEGIN
			IF ISNUMERIC ( SUBSTRING(@SQL_SYNTAX, 1, 10) ) = 1
			BEGIN
				IF CONVERT(INT, SUBSTRING(@SQL_SYNTAX, 1, 10)) % 10 = 0
				BEGIN
					SET @SQL_PRINT = REPLACE(@SQL_PRINT, N'^=*', N' ')
					IF LEN(@SQL_PRINT) > 0
						PRINT @SQL_PRINT
					SET @SQL_PRINT = SUBSTRING(@SQL_SYNTAX, 11, LEN(@SQL_SYNTAX) - 10)
				END
				ELSE
				BEGIN
					IF LEN(@SQL_SYNTAX) > 10
						SET @SQL_PRINT = @SQL_PRINT + SUBSTRING(@SQL_SYNTAX, 11, LEN(@SQL_SYNTAX) - 10)
				END
			END

			FETCH NEXT FROM CUR_SYNTAX INTO @SQL_SYNTAX
		END

		SET @SQL_PRINT = REPLACE(@SQL_PRINT, N'^=*', N' ')
		IF LEN(@SQL_PRINT) > 0
			PRINT @SQL_PRINT

		CLOSE CUR_SYNTAX
		DEALLOCATE CUR_SYNTAX

		IF OBJECT_ID('tempdb..#TEMP_SYNTAX') IS NOT NULL
			DROP TABLE #TEMP_SYNTAX
	END

	IF @Version = '2000'
	BEGIN
		EXEC SP_SQLJM2008 @ObjectName
	END
END

GO 
----	调用解密对象

----	如果提示“SQL Server 阻止了对组件 'xp_cmdshell' 的 过程'sys.xp_cmdshell' 的访问。”,则执行下面的代码。
/*
		EXEC sp_configure 'xp_cmdshell', 1
		RECONFIGURE
*/

----	如果提示“配置选项 'xp_cmdshell' 不存在,也可能是高级选项。”,则执行下面的代码。
/*
		EXEC sp_configure 'show advanced options', 1
		RECONFIGURE
*/



EXEC SP_SQLJM 'Up_HANDle_Stock'

 

 

摘自:

Sql server 存储过程加密

破解Sql Server存储过程加密