In-depth analysis on the encryption and decryption principles of SQL Server Stored Procedures

Start:
--------------------------------------------------------------------------------
On the network, we can see how SQL Server 2000 and SQL Server 2005 encrypt and decrypt stored procedures. Later, we analyzed the code and found that they work in the same way. Later, I wrote two stored procedures, sp_EncryptObject and sp_EncryptObject, based on the actual application environment. They can be applied to the stored procedures in SQL Server, functions, views, and triggers.
I think these two stored procedures are very interesting and I will share them with you. If you have read something similar, you can just review it.
Sp_EncryptObject ):
--------------------------------------------------------------------------------
The storage procedure (sp_EncryptObject) is encrypted by adding "with encryption" before the "As" position of the stored procedure, function, and view. If it is a trigger, add "with encryption" before "".
If the trigger is {AFTER | instead of}, modify the following code ": Copy codeThe Code is As follows: if objectproperty (object_id (@ Object), 'execisaftertrigger') = 0 set @ Replace = 'as'; else set @ Replace = '';

Stored Procedure completion code:Copy codeThe Code is as follows: Use master
Go
If object_ID ('[sp_EncryptObject]') is not null
Drop Procedure [sp_EncryptObject]
Go
Create procedure sp_EncryptObject
(
@ Object sysname = 'all'
)
As
/*
When @ Object = All, All functions, stored procedures, views, and triggers are encrypted.
Call method:
. Execute sp_EncryptObject 'all'
. Execute sp_EncryptObject 'objectname'
*/
Begin
Set nocount on
If @ Object <> 'all'
Begin
If not exists (select 1 from sys. objects a where. object_id = object_id (@ Object) And. type in ('P', 'V', 'tr', 'fn ', 'if', 'tf '))
Begin
-- SQL Server 2008
Raiserror 50001 n' the encrypted object is invalid! The encrypted object must be a function, stored procedure, view, or trigger. '
-- SQL Server 2012
-- Throw 50001, n' the encrypted object is invalid! The encrypted object must be a function, stored procedure, view, or trigger. ', 1
Return
End
If exists (select 1 from sys. SQL _modules a where a. object_id = object_id (@ Object) and a. definition is null)
Begin
-- SQL Server 2008
Raiserror 50001 n' object encrypted! '
-- SQL Server 2012
-- Throw 50001, n' object is encrypted! ', 1
Return
End
End
Declare @ SQL nvarchar (max), @ C1 nchar (1), @ C2 nchar (1), @ type nvarchar (50), @ Replace nvarchar (50)
Set @ C1 = nchar (13)
Set @ C2 = nchar (10)
Declare cur_Object
Cursor
Select object_name (a. object_id) As ObjectName, a. definition
From sys. SQL _modules
Inner join sys. objects B on B. object_id = a. object_id
And B. is_ms_shipped = 0
And not exists (select 1
From sys. extended_properties x
Where x. major_id = B. object_id
And x. minor_id = 0
And x. class = 1
And x. name = 'Microsoft _ database_tools_support'
)
Where B. type in ('P', 'V', 'tr', 'fn ', 'if', 'tf ')
And (B. name = @ Object or @ Object = 'all ')
And B. name <> 'SP _ EncryptObject'
And a. definition is not null
Order by Case
When B. type = 'V' then 1
When B. type = 'tr 'then 2
When B. type in ('fn ', 'if', 'tf') then 3
Else 4 end, B. create_date, B. object_id
Open cur_Object
Fetch next from cur_Object into @ Object, @ SQL
While @ fetch_status = 0
Begin
Begin Try
If objectproperty (object_id (@ Object), 'execisaftertrigger') = 0 set @ Replace = 'as'; else set @ Replace = 'for ';
If (patindex ('%' + @ C1 + @ C2 + @ Replace + @ C1 + @ C2 + '%', @ SQL)> 0)
Begin
Set @ SQL = Replace (@ SQL, @ C1 + @ C2 + @ Replace + @ C1 + @ C2, @ C1 + @ C2 + 'With Encryption' + @ C1 + @ C2 + @ Replace + @ C1 + @ C2)
End
Else if (patindex ('%' + @ C1 + @ Replace + @ C1 + '%', @ SQL)> 0)
Begin
Set @ SQL = Replace (@ SQL, @ C1 + @ Replace + @ C1, @ C1 + 'With Encryption' + @ C1 + @ Replace + @ C1)
End
Else if (patindex ('%' + @ C2 + @ Replace + @ C2 + '%', @ SQL)> 0)
Begin
Set @ SQL = Replace (@ SQL, @ C2 + @ Replace + @ C2, @ C2 + 'With Encryption' + @ C2 + @ Replace + @ C2)
End
Else if (patindex ('%' + @ C2 + @ Replace + @ C1 + '%', @ SQL)> 0)
Begin
Set @ SQL = Replace (@ SQL, @ C2 + @ Replace + @ C1, @ C1 + 'With Encryption' + @ C2 + @ Replace + @ C1)
End
Else if (patindex ('%' + @ C1 + @ C2 + @ Replace + '%', @ SQL)> 0)
Begin
Set @ SQL = Replace (@ SQL, @ C1 + @ C2 + @ Replace, @ C1 + @ C2 + 'With Encryption' + @ C1 + @ C2 + @ Replace)
End
Else if (patindex ('%' + @ C1 + @ Replace + '%', @ SQL)> 0)
Begin
Set @ SQL = Replace (@ SQL, @ C1 + @ Replace, @ C1 + 'With Encryption' + @ C1 + @ Replace)
End
Else if (patindex ('%' + @ C2 + @ Replace + '%', @ SQL)> 0)
Begin
Set @ SQL = Replace (@ SQL, @ C2 + @ Replace, @ C2 + 'With Encryption' + @ C2 + @ Replace)
End
Set @ type =
Case
When object_id (@ Object, 'P')> 0 then 'proc'
When object_id (@ Object, 'V')> 0 then 'view'
When object_id (@ Object, 'tr')> 0 then 'trigger'
When object_id (@ Object, 'fn ')> 0 or object_id (@ Object, 'if')> 0 or object_id (@ Object, 'tf')> 0 then 'function'
End
Set @ SQL = Replace (@ SQL, 'create' + @ type, 'alter '+ @ type)
Begin Transaction
Exec (@ SQL)
Print n' encrypted Object ('+ @ type +'): '+ @ Object
Commit Transaction
End Try
Begin Catch
Declare @ Error nvarchar (2047)
Set @ Error = 'object: '+ @ Object + @ C1 + @ C2 + 'error:' + Error_message ()
Rollback Transaction
Print @ Error
Print @ SQL
End Catch
Fetch next from cur_Object into @ Object, @ SQL
End
Close cur_Object
Deallocate cur_Object
End
Go
Exec sp_ms_marksystemobject 'SP _ EncryptObject '-- identifies as a system object
Go

If SQL Server 2012 is used, modify the code in the following two locations. In SQL Server 2012, we recommend that you use throw instead of raiserror.

Decryption Method:
In the decryption process, the most important method is exclusive or:
[Character 1] is encrypted by the fn_x (x) function to [encrypted character 1]. If we know [encrypted character 1] and Query [Character 1] In turn, we can do this:
[Character 1] = [character 2] ^ fn_x ([character 2]) ^ [encrypted character 1]
Here is a simple example:Copy codeThe Code is as follows: -- create an encryption function (fn_x)
If object_id ('fn _ x') is not null drop function fn_x
Go
Create function fn_x
(
@ X nchar (1)
) Returns nchar (1)
As
Begin
Return (nchar (65535-unicode (@ x ))))
End
Go
Declare @ nchar_1_encrypt nchar (1), @ nchar_2 nchar (1)
-- Encrypt the character 'a' and store the variable @ nchar_policencrypt
Set @ nchar_1_encrypt = dbo. fn_x (N 'A ')
-- The answer character @ nchar_2
Set @ nchar_2 = 'X'
-- Calculate the characters before @ nchar_1_encrypt Encryption
Select nchar (unicode (@ nchar_2) ^ unicode (dbo. fn_x (@ nchar_2) ^ unicode (@ nchar_1_encrypt) as [@ nchar_1]
/*
@ Nchar_1
--------------------
A
*/

[Note]: You can decrypt data from SQL Server 2000 to SQL Server 2012 using different or different methods.
The stored procedure for decryption (sp_DecryptObject ):Copy codeThe Code is as follows: Use master
Go
If object_ID ('[sp_DecryptObject]') is not null
Drop Procedure [sp_DecryptObject]
Go
Create procedure sp_DecryptObject
(
@ Object sysname, -- Name of the Object to be decrypted: function, stored procedure, view or trigger
@ MaxLength int = 4000 -- length of the evaluation content
)
As
Set nocount on
/* 1. decrypt */
If not exists (select 1 from sys. objects a where. object_id = object_id (@ Object) And. type in ('P', 'V', 'tr', 'fn ', 'if', 'tf '))
Begin
-- SQL Server 2008
Raiserror 50001 n' Invalid object! The object to be decrypted must be a function, stored procedure, view, or trigger. '
-- SQL Server 2012
-- Throw 50001, n'invalid object! The object to be decrypted must be a function, stored procedure, view, or trigger. ', 1
Return
End
If exists (select 1 from sys. SQL _modules a where a. object_id = object_id (@ Object) and a. definition is not null)
Begin
-- SQL Server 2008
Raiserror 50001 n' object not encrypted! '
-- SQL Server 2012
-- Throw 50001, n'invalid object! The object to be decrypted must be a function, stored procedure, view, or trigger. ', 1
Return
End
Declare @ SQL nvarchar (max) -- decrypted SQL statement
, @ Imageval nvarchar (max) -- encrypted string
, @ TmpStr nvarchar (max) -- temporary SQL statement
, @ TmpStr_imageval nvarchar (max) -- temporary SQL statement (encrypted)
, @ Type char (2) -- object type ('P', 'V', 'tr', 'fn ', 'if', 'tf ')
, @ ObjectID int -- Object ID
, @ I int -- While loop usage
, @ Oject1 nvarchar (1000)
Set @ objectID = object_id (@ Object)
Set @ type = (select a. type from sys. objects a where a. object_id = @ objectID)
Declare @ Space4000 nchar (4000)
Set @ Space4000 = replicate ('-', 4000)
/*
@ TmpStr construct the following SQL statement
-------------------------------------------------------------------------------
Alter trigger Tr_Name on Table_Name with encryption for update as return /**/
Alter proc Proc_Name with encryption as select 1 as col /**/
Alter view View_Name with encryption as select 1 as col /**/
Alter function Fn_Name () returns int with encryption as begin return (0) end /**/
*/
Set @ Oject1 = quotename (object_schema_name (@ objectID) + '.' + quotename (@ Object)
Set @ tmpStr =
Case
When @ type = 'P' then n' Alter Procedure '+ @ Oject1 +' with encryption as select 1 as column1'
When @ type = 'V' then N 'alter view' + @ Oject1 + 'with encryption as select 1 as column1'
When @ type = 'fn 'then n' Alter function' + @ Oject1 + '() returns int with encryption as begin return (0) end'
When @ type = 'if 'then n' Alter function' + @ Oject1 + '() returns table with encryption as return (Select a. name from sys. types )'
When @ type = 'tf' then N 'alter function' + @ Oject1 + '() returns @ t table (name nvarchar (50) with encryption as begin return end'
Else 'alter trigger' + @ Oject1 + 'on' + quotename (object_schema_name (@ objectID) + '. '+ (select Top (1) quotename (object_name (parent_id) from sys. triggers a where. object_id = @ objectID) + 'with encryption for update as return'
End
Set @ tmpStr = @ tmpStr + '/*' + @ Space4000
Set @ I = 0
While @ I <(ceiling (@ MaxLength * 1.0/4000)-1)
Begin
Set @ tmpStr = @ tmpStr + @ Space4000
Set @ I = @ I + 1
End
Set @ tmpStr = @ tmpStr + '*/'
------------
Set @ imageval = (select top (1) a. imageval from sys. sysobjvalues a where a. objid = @ objectID and a. valclass = 1)
Begin tran
Exec (@ tmpStr)
Set @ tmpStr_imageval = (select top (1) a. imageval from sys. sysobjvalues a where a. objid = @ objectID and a. valclass = 1)
Rollback tran
-------------
Set @ tmpStr = stuff (@ tmpStr, 1, 5, 'create ')
Set @ SQL =''
Set @ I = 1
While @ I <= (datalength (@ imageval)/2)
Begin
Set @ SQL = @ SQL + isnull (nchar (unicode (substring (@ tmpStr, @ I, 1) ^ unicode (substring (@ tmpStr_imageval, @ I, 1 )) ^ unicode (substring (@ imageval, @ I, 1 ))),'')
Set @ I + = 1
End
/* 2. Print */
Declare @ patindex int
While @ SQL>''
Begin
Set @ patindex = patindex ('%' + char (13) + char (10) + '%', @ SQL)
If @ patindex> 0
Begin
Print substring (@ SQL, 1, @ patindex-1)
Set @ SQL = stuff (@ SQL, 1, @ patindex + 1 ,'')
End
Else
Begin
Set @ patindex = patindex ('%' + char (13) + '%', @ SQL)
If @ patindex> 0
Begin
Print substring (@ SQL, 1, @ patindex-1)
Set @ SQL = stuff (@ SQL, 1, @ patindex ,'')
End
Else
Begin
Set @ patindex = patindex ('%' + char (10) + '%', @ SQL)
If @ patindex> 0
Begin
Print substring (@ SQL, 1, @ patindex-1)
Set @ SQL = stuff (@ SQL, 1, @ patindex ,'')
End
Else
Begin
Print @ SQL
Set @ SQL =''
End
End
End
End
Go
Exec sp_ms_marksystemobject 'SP _ DecryptObject '-- identifies as a system object
Go

If SQL Server 2012 is used, modify the code in the following two locations. The method is similar to the previous encryption process:

Build a test environment:
--------------------------------------------------------------------------------
In a Test environment (DB: Test), execute the preceding encryption Stored Procedure (sp_EncryptObject) and decryption Stored Procedure (sp_EncryptObject), and create two tables: TableA & TableBCopy codeThe Code is as follows: use test
Go
-- Create a table: TableA & TableB
If object_id ('mytablea') is not null drop table myTableA
If object_id ('mytableb') is not null drop table myTableB
Go
Create table myTableA (ID int identity, data nvarchar (50), constraint PK_myTableA primary key (ID ))
Create table myTableB (ID int, data nvarchar (50), constraint PK_myTableB primary key (ID ))
Go

Next, we will create 6 unencrypted objects (object types include 'P', 'V', 'tr', 'fn ', 'if', 'tf '):
1. View (myView ):Copy codeThe Code is as follows: if object_id ('myview') is not null drop view myView
Go
Create view myView
As
Select * from TableA;
Go

2. Trigger (MyTrigger ):Copy codeThe Code is as follows: if object_id ('mytrigger') is not null drop Trigger MyTrigger
Go
Create trigger MyTrigger
On TableA
For update
As
Insert into TableB (ID, data) select a. ID, a. Data From Inserted
Go

3. Stored Procedure (MyProc ):Copy codeThe Code is as follows: if object_id ('myproc') is not null drop proc MyProc
Go
Create proc MyProc
(
@ Data nvarchar (50)
)
As
Insert into TableA (data) values (@ data)
Go

4. User-Defined Table value function (TF) (MyFunction_TF ):Copy codeThe Code is as follows: if object_id ('myfunction _ tf') is not null drop function MyFunction_TF
Go
Create function MyFunction_TF
(
)
Returns @ t table
(
Id int,
Data nvarchar (50)
)
As
Begin
Insert @ t (id, data) select id, data from TableA
Return
End
Go

5. inline Table value function (IF) (MyFunction_IF ):Copy codeThe Code is as follows: if object_id ('myfunction _ if') is not null drop function MyFunction_IF
Go
Create function MyFunction_IF
(
)
Returns table
As
Return (select top (3) id, data from TableA order by id desc)
Go

6. scalar function (FN) (MyFunction_FN ):Copy codeThe Code is as follows: if object_id ('myfunction _ FN ') is not null drop function MyFunction_FN
Go
Create function MyFunction_FN
(
)
Returns nvarchar (50)
As
Begin
Return (select top (1) data from TableA order by id desc)
End
Go

After executing the script in step 1-6 above, we can query the System View sys. SQL _modules to see the pre-encryption definition information:Copy codeThe Code is as follows: select B. name as object, B. type, a. definition
From sys. SQL _modules
Inner join sys. objects B on B. object_id = a. object_id
Where B. create_date> = convert (date, getdate ())
Order by B. object_id

Encryption Test:
--------------------------------------------------------------------------------
Next I will encrypt them at one time by calling the encryption Stored Procedure (sp_EncryptObject:Copy codeThe Code is as follows: use test
Go
Exec sp_EncryptObject 'all'
Go

When we check the System View sys. SQL _modules again, we will find that the return value of the definition column is null, indicating that the definition has been encrypted:

Decryption Test:
--------------------------------------------------------------------------------
The decryption process must be connected to SQL Server in the DAC. The example here is to start the DAC from the SSMS (SQL Server Management Studio) query editor,

Sp_DecryptObject decrypts a stored procedure, function, view, or trigger at a time:Copy codeThe Code is as follows: use test
Go
Exec sp_DecryptObject MyTrigger
Go

When the definition content length exceeds 4000, we can specify the value of @ MaxLength, such:Copy codeThe Code is as follows: exec sp_DecryptObject fn_My, 20000
Go

Here (fn_My) is a function with more than 8000 defined content:

......

Summary:
--------------------------------------------------------------------------------
Although the above script has been tested on SQL Server 2008 R2 and SQL Server 2012, some unknown errors cannot be avoided. If you are testing the script yourself, do not use it in the production environment. If you have any questions or have any comments or suggestions during the application process, please email me or follow up. Thank you very much!