bf_xptest.sql

关于SQL SERVER 的加密和解密包

-- BF_XPTEST.SQL
-- xp_blowfishencrypt/xp_blowfishdecrypt Test Script
-- This set of tests demonstrates calling the xp's directly
-- from within your code.
--
-- Michael Coles, MCDBA, 7/2005
--
DECLARE @plain_text VARCHAR(500) -- Our plain text
DECLARE @enc_text VARCHAR(500)   -- Our encrypted text
DECLARE @dec_text VARCHAR(500)   -- Our decrypted text
DECLARE @key VARCHAR(500)         -- Our encryption key

-- Test 1
SET @plain_text = 'MICHAEL JORDAN'
SET @key = 'CHICAGO BULLS'
EXEC master..xp_base64encode @key, @key OUTPUT
EXEC master..xp_blowfishencrypt @plain_text, @key, @enc_text OUTPUT
EXEC master..xp_blowfishdecrypt @enc_text, @key, @dec_text OUTPUT
PRINT 'Test 1: Encrypt/Decrypt Name'
PRINT '--Plain Text: ''' + @plain_text + ''''
PRINT '--Key: ''' + @key + ''''
PRINT '--Encrypted Text: ''' + @enc_text + ''''
PRINT '--Decrypted Text: ''' + @dec_text + ''''
IF @plain_text = @dec_text
	PRINT '--Result:  PLAIN TEXT IS EQUAL TO DECRYPTED TEXT'
ELSE
	PRINT '--Result:  PLAIN TEXT IS NOT EQUAL TO DECRYPTED TEXT'
PRINT '-----------------------------------------'
-- Test 2
SET @plain_text = ''
EXEC master..xp_generatekey 128, @key OUTPUT
EXEC master..xp_blowfishencrypt @plain_text, @key, @enc_text OUTPUT
EXEC master..xp_blowfishdecrypt @enc_text, @key, @dec_text OUTPUT
PRINT 'Test 2: Zero-Length String Handling'
PRINT '--Plain Text: ''' + @plain_text + ''''
PRINT '--Key: ''' + @key + ''''
PRINT '--Encrypted Text: ''' + @enc_text + ''''
PRINT '--Decrypted Text: ''' + @dec_text + ''''
IF @plain_text = @dec_text
	PRINT '--Result:  PLAIN TEXT IS EQUAL TO DECRYPTED TEXT'
ELSE
	PRINT '--Result:  PLAIN TEXT IS NOT EQUAL TO DECRYPTED TEXT'
PRINT '-----------------------------------------'
--Test 3
SET @plain_text = 'Now is the time for all good men to come to the aid of their countrymen.'
EXEC master..xp_generatekey 448, @key OUTPUT
EXEC master..xp_blowfishencrypt @plain_text, @key, @enc_text OUTPUT
EXEC master..xp_blowfishdecrypt @enc_text, @key, @dec_text OUTPUT
PRINT 'Test 3: Longer String/Longer Key'
PRINT '--Plain Text: ''' + @plain_text + ''''
PRINT '--Key: ''' + @key + ''''
PRINT '--Encrypted Text: ''' + @enc_text + ''''
PRINT '--Decrypted Text: ''' + @dec_text + ''''
IF @plain_text = @dec_text
	PRINT '--Result:  PLAIN TEXT IS EQUAL TO DECRYPTED TEXT'
ELSE
	PRINT '--Result:  PLAIN TEXT IS NOT EQUAL TO DECRYPTED TEXT'
PRINT '-----------------------------------------'
--Test 4
SET @plain_text = NULL
EXEC master..xp_generatekey 448, @key OUTPUT
EXEC master..xp_blowfishencrypt @plain_text, @key, @enc_text OUTPUT
EXEC master..xp_blowfishdecrypt @enc_text, @key, @dec_text OUTPUT
PRINT 'Test 4: NULL plain-text handling'
PRINT '--Plain Text: ' + COALESCE('''' + @plain_text + '''', '(NULL)')
PRINT '--Key: ''' + @key + ''''
PRINT '--Encrypted Text: ' + COALESCE('''' + @enc_text + '''', '(NULL)')
PRINT '--Decrypted Text: ' + COALESCE('''' + @dec_text + '''', '(NULL)')