bf_udftest.sql

关于SQL SERVER 的加密和解密包

-- BF_UDFTEST.SQL
-- This test generates a table containing a list of name of varying lengths.
-- It then demonstrates how to use the dbo.fn_encrypt and dbo.fn_decrypt
-- UDF's to encrypt and decrypt the names.  Since the whole point of encryption/
-- decryption is to get back exactly what you put in, there is an additional
-- column that indicates whether the Plain Text and decrypted text are equivalent.
--
-- The second part of the test demonstrates the same thing, but using a list of
-- fake SSN's.  Note that all of the SSN's in this list have been invalidated by
-- the U.S. SSA (www.ssa.gov), including the 10 SSN's in the list that begin with
-- '987' which have been designated as SSN's that may be used by the public for
-- general purpose use in movies, TV, advertising, etc.  For more information on
-- this, visit the SSA website.
--
-- Michael Coles, MCDBA, 6/2005
--
-- First the Name Test.  Notice that Blowfish always encrypts in blocks of 8
-- bytes.  If your string is not a multiple of 8 bytes in length, it will be 
-- padded up to a multiple of 8.  The padding is removed when you decrypt your
-- string.
--
DECLARE @Blowfish_Name_Test
TABLE (Plain_Name VARCHAR(25) NOT NULL PRIMARY KEY,
	Enc_Name VARCHAR(75) NULL,
	Dec_Name VARCHAR(75) NULL,
	Success_Flag CHAR(1) NULL)

INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('JOSE')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('CESAR')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('RICHARD')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('ANTONIO')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('JENNY')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('JUSTINE')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('MAXINE')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('JOEL')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('JEFFERY')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('MARIE')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('SUSAN')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('NICK')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('B')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('BILLY')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('ROBERT')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('ISAAC')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('MOHAMED')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('ANNE')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('BRYAN')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('JOAN')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('FELIX')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('SYLVIA')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('GORDON')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('LYNNE')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('SHIRLEY')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('CHRISTOPHER')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('LORRAINE')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('JOHN-JACOB')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('LAQUEISHA')
INSERT INTO @Blowfish_Name_Test (Plain_Name) VALUES ('ADRIANNE')
DECLARE @key VARCHAR(500)

EXEC master..xp_generatekey 128, @key OUTPUT

UPDATE @Blowfish_Name_Test
SET Enc_Name = master.dbo.udf_blowfishencrypt(Plain_Name, @key)

UPDATE @Blowfish_Name_Test
SET Dec_Name = master.dbo.udf_blowfishdecrypt(Enc_Name, @key)

UPDATE @Blowfish_Name_Test
SET Success_Flag = 
	CASE WHEN Plain_Name = Dec_Name 
	THEN 'Y' 
	ELSE 'N' END

SELECT * FROM @Blowfish_Name_Test

-- Now the Fake SSN Test.  Notice that the Fake_SSN column is larger than
-- the actual data.  This demonstrates that SQL's auto-padding of CHAR 
-- columns with spaces to the right does not affect the results of the encryption
-- and decryption.
--
DECLARE @Blowfish_Fake_SSN_Test
TABLE (Fake_SSN CHAR(15) NOT NULL PRIMARY KEY,
	Enc_Fake_SSN VARCHAR(50) NULL,
	Dec_Fake_SSN VARCHAR(15) NULL,
	Success_Flag CHAR(1) NULL)
-- Create a stronger key for this test
EXEC master..xp_generatekey 448, @key OUTPUT
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('002281852')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('042103580')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('062360749')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('078051120')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('095073645')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('128036045')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('135016629')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('141186941')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('165167999')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('165187999')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('165207999')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('165227999')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('165247999')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('189092294')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('212097694')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('212099999')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('306302348')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('308125070')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('468288779')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('549241889')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('987654320')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('987654321')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('987654322')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('987654323')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('987654324')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('987654325')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('987654326')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('987654327')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('987654328')
INSERT @Blowfish_Fake_SSN_Test (Fake_SSN) VALUES ('987654329')

UPDATE @Blowfish_Fake_SSN_Test
SET Enc_Fake_SSN = master.dbo.udf_blowfishencrypt(Fake_SSN, @key)

UPDATE @Blowfish_Fake_SSN_Test
SET Dec_Fake_SSN = master.dbo.udf_blowfishdecrypt(Enc_Fake_SSN, @key)

UPDATE @Blowfish_Fake_SSN_Test
SET Success_Flag =
	CASE WHEN Fake_SSN = Dec_Fake_SSN
	THEN 'Y'
	ELSE 'N' END

SELECT * FROM  @Blowfish_Fake_SSN_Test