SQL Server permette, tramite la clausola WITH ENCRYPTION, di mascherare i sorgenti del nostro codice, ad esempio di Stored Procedure, Viste, Trigger, ...
Sicuramente la cosa va bene per utente non smaliziati, per proteggere "abbastanza" le istruzioni e la nostra logica.
Dico "abbastanza" perchè, com'è risaputo esiste il modo di riportare in chiaro le istruzioni, un pò come avviene con la decompilazione di assembly .Net che non siano stati offuscati con programmi creati ad-hoc per questo.
Oggi, sul newsgroup italiano di SQL Server (news://microsoft.public.it.sql ) ho aiutato un collega a riportare "in vita" delle SP di un vecchio db di cui si erano persi i sorgenti.
Grazie ad una procedura discussa a suo tempo (novembre 2006) sul newsgroup SQL Server Security (news://microsoft.public.sqlserver.security ).
ATTENZIONE: prima di provare (se si vuol provare) fare un bel backup del db... ok?
/* lavoro sul tempdb per fare le mie prove */
USE tempdb
GO
/* mi costruisco una SP cifrata */
CREATE PROCEDURE dbo.up_test
WITH ENCRYPTION
AS
SELECT 1
UNION
SELECT 2
GO
/* mi costruisco la SP per decifrare */
CREATE PROCEDURE dbo.sp_SpDeObfuscation (@procedure sysname = NULL, @safety int = 1)
AS
/*
Name: sp_SpDeObfuscation
Purpose: Decrypt SP's in SQL 2005
Author: Theo Ekelmans (t...@ekelmans.com)
Based on: A script that is discussed in news://microsoft.public.sqlserver.security
Version: 1.0 - 2006-11-16
Changes: none (yet)
Input: exec sp_SpDeObfuscation '<sp_name>', 0
Output: Switch to text output!
Note: You need to use the Dedicated Administrator Connection with
SQL Server Management Studio be in DAC to be able to run this SP
*/
SET NOCOUNT ON
IF @safety = 1
BEGIN
PRINT 'CAUTION: THIS PROCEDURE DELETES AND REBUILDS THE ORIGINAL STORED PROCEDURE.'
PRINT ' '
PRINT 'MAKE A BACKUP OF YOUR DATABASE BEFORE RUNNING THIS PROCEDURE.'
PRINT ' '
PRINT 'IDEALLY, THIS PROCEDURE SHOULD BE RUN ON A NON-PRODUCTION COPY OF THE PROCEDURE.'
PRINT ' '
PRINT 'To run the procedure, change the @safety parameter to 0'
RETURN 0
END
DECLARE @intProcSpace bigint
DECLARE @t bigint
DECLARE @maxColID smallint
DECLARE @intEncrypted tinyint
DECLARE @procNameLength int
DECLARE @real_01 nvarchar(max)
DECLARE @fake_01 nvarchar(max)
DECLARE @fake_encrypt_01 nvarchar(max)
DECLARE @real_decrypt_01 nvarchar(max)
DECLARE @real_decrypt_01a nvarchar(max)
-- create this table for later use
create table #output ( [ident] [int] IDENTITY (1, 1) NOT NULL ,
[real_decrypt] NVARCHAR(MAX) )
SELECT @maxColID = max(subobjid)
FROM sys.sysobjvalues
WHERE objid = object_id(@procedure)
select @procNameLength = datalength(@procedure) + 29
select @real_decrypt_01a = ''
-- extract the encrypted imageval rows from sys.sysobjvalues
SET @real_01= ( SELECT imageval
FROM sys.sysobjvalues
WHERE objid = object_id(@procedure)
and valclass = 1
and subobjid = 1 )
-- We'll begin the transaction and roll it back later
BEGIN TRAN
-- alter the original procedure, replacing with dashes ( the cast('-' as nvarchar(max)) is to allow for SP's larger than 4K)
SET @fake_01 = 'ALTER PROCEDURE '+ @procedure +' WITH ENCRYPTION AS ' + REPLICATE(cast('-' as nvarchar(max)), 40003 - @procNameLength)
EXECUTE (@fake_01)
-- extract the encrypted fake imageval rows from sys.sysobjvalues
SET @fake_encrypt_01=( SELECT imageval
FROM sys.sysobjvalues
WHERE objid = object_id(@procedure)
and valclass = 1 and subobjid = 1)
SET @fake_01 = 'CREATE PROCEDURE '+ @procedure +' WITH ENCRYPTION AS ' + REPLICATE(cast('-' as nvarchar(max)), 40003 - @procNameLength)
--start counter
SET @intProcSpace=1
--fill temporary variable with with a filler character ( the cast(N'A' as nvarchar(max)) is to allow for SP's larger than 4K)
SET @real_decrypt_01 = replicate(cast(N'A' as nvarchar(max)), (datalength(@real_01) /2 ))
--loop through each of the variables sets of variables, building the real variable one byte at a time.
SET @intProcSpace=1
-- Go through each @real_xx variable and decrypt it, as necessary
WHILE @intProcSpace <= (datalength(@real_01)/2)
BEGIN
--xor real & fake & fake encrypted
SET @real_decrypt_01 = stuff( @real_decrypt_01,
@intProcSpace,
1,
NCHAR ( UNICODE( substring(@real_01, @intProcSpace, 1) )
^ ( UNICODE( substring(@fake_01, @intProcSpace, 1) )
^ UNICODE( substring(@fake_encrypt_01, @intProcSpace, 1) )
)))
SET @intProcSpace=@intProcSpace+1
END
-- Load the variables into #output for handling by sp_helptext logic
insert #output (real_decrypt) select @real_decrypt_01
--select real_decrypt AS '#output check' from #output -- Testing
-- -------------------------------------
-- Beginning of extract from sp_helptext
-- -------------------------------------
declare @dbname sysname
declare @BlankSpaceAdded int
declare @BasePos int
declare @CurrentPos int
declare @TextLength int
declare @LineId int
declare @AddOnLen int
declare @LFCR int --lengths of line feed carriage return
declare @DefinedLength int
declare @SyscomText nvarchar(max)
declare @Line nvarchar(255)
Select @DefinedLength = 255
SELECT @BlankSpaceAdded = 0
--Keeps track of blank spaces at end of lines. Note Len function ignores trailing blank spaces
CREATE TABLE #CommentText ( LineId int ,
Text nvarchar(255) collate database_default)
-- use #output instead of sys.sysobjvalues
DECLARE ms_crs_syscom CURSOR LOCAL FOR
SELECT real_decrypt
from #output
ORDER BY
ident
FOR READ ONLY
-- Else get the text.
SELECT @LFCR = 2
SELECT @LineId = 1
OPEN ms_crs_syscom
FETCH NEXT FROM ms_crs_syscom into @SyscomText
WHILE @@fetch_status >= 0
BEGIN
SELECT @BasePos = 1
SELECT @CurrentPos = 1
SELECT @TextLength = LEN(@SyscomText)
WHILE @CurrentPos != 0
BEGIN
--Looking for end of line followed by carriage return
SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText, @BasePos)
--If carriage return found
IF @CurrentPos != 0
BEGIN
--If new value for @Lines length will be > then the set length then insert current contents of @line and proceed.
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 --else carriage return not found
BEGIN
IF @BasePos <= @TextLength
BEGIN
--If new value for @Lines length will be > then the defined length
While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @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
-- Clean up
CLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscom
DROP TABLE #CommentText
-- -------------------------------------
-- End of extract from sp_helptext
-- -------------------------------------
ROLLBACK TRAN
DROP TABLE #output
-- Drop the procedure that was setup with dashes and rebuild it with the good stuff
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
A questo punto:
E' necessario connettersi con la DAC, Dedicated Administrator Connection (altrimenti non potrei avere acesso a tabelle di sistema come la sys.sysobjvalues).
Quindi: è necesasrio verificare tramite l'utility Surface Area Configuration che questa sia abilitata...
Per connettersi tramite DAC, da Management Studio, invece di connettersi a "nomeMacchina", bisogna connetersi a "ADMIN:nomeMacchina".
Quindi eseguire:
EXEC dbo.sp_SpDeObfuscation 'up_test', 0
Consiglio spassionato: fare un backup dei sorgenti del db, delle istruzioni di creazione degli oggetti, dovrebbe essere LA regola! ;-)