aes_xptest.sql

关于SQL SERVER 的加密和解密包

-- AES_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 = 'THIS IS 128 BITS'
EXEC master..xp_aesencrypt @plain_text, @key, @enc_text OUTPUT
EXEC master..xp_aesdecrypt @enc_text, @key, @dec_text OUTPUT
PRINT 'Test 1: Encrypt/Decrypt Name'
PRINT '--Plain Text:     ' + dbo.fn_varbintohexstr(CAST(@plain_text AS VARBINARY))
PRINT '--Key:            ' + dbo.fn_varbintohexstr(CAST(@key AS VARBINARY)) 
PRINT '--Encrypted Text: ' + dbo.fn_varbintohexstr(CAST(@enc_text AS VARBINARY))
PRINT '--Decrypted Text: ' + dbo.fn_varbintohexstr(CAST(@dec_text AS VARBINARY))
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_aesencrypt @plain_text, @key, @enc_text OUTPUT
EXEC master..xp_aesdecrypt @enc_text, @key, @dec_text OUTPUT
PRINT 'Test 2: Zero-Length String Handling'
PRINT '--Plain Text:     ''' + @plain_text + ''''
PRINT '--Key:            ' + dbo.fn_varbintohexstr(CAST(@key AS VARBINARY))
PRINT '--Encrypted Text: ' + dbo.fn_varbintohexstr(CAST(@enc_text AS VARBINARY))
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.'
SET @key = 'THIS IS 256 BITS, 256 BITS I SAY'
EXEC master..xp_aesencrypt @plain_text, @key, @enc_text OUTPUT
EXEC master..xp_aesdecrypt @enc_text, @key, @dec_text OUTPUT
PRINT 'Test 3: Longer String/Longer Key'
PRINT '--Plain Text:     ' + dbo.fn_varbintohexstr(CAST(@plain_text AS VARBINARY))
PRINT '--Key:            ' + dbo.fn_varbintohexstr(CAST(@key AS VARBINARY)) 
PRINT '--Encrypted Text: ' + dbo.fn_varbintohexstr(CAST(@enc_text AS VARBINARY))
PRINT '--Decrypted Text: ' + dbo.fn_varbintohexstr(CAST(@dec_text AS VARBINARY))
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_aesencrypt @plain_text, @key, @enc_text OUTPUT
EXEC master..xp_aesdecrypt @enc_text, @key, @dec_text OUTPUT
PRINT 'Test 4: NULL plain-text handling'
PRINT '--Plain Text:     ' + COALESCE('''' + @plain_text + '''', '(NULL)')
PRINT '--Key:            ' + dbo.fn_varbintohexstr(CAST(@key AS VARBINARY)) 
PRINT '--Encrypted Text: ' + COALESCE('''' + @enc_text + '''', '(NULL)')
PRINT '--Decrypted Text: ' + COALESCE('''' + @dec_text + '''', '(NULL)')