[SQL2012] Encryption/Decryption by encryptbypassphrase/decryptbypassphrase

/****************************************************************************************************************
-- Title : [SQL2012] Encription/Decription by encryptbypassphrase/decryptbypassphrase
-- Reference : forums.asp.net etc
-- Key word : 암호화 복호화 양방향 encryption decryption encryptbypassphrase decryptbypassphrase passphrase
                     암호 복호 encrypt decrypt
****************************************************************************************************************/

drop table ttt;

go

CREATE TABLE ttt

( seq int 

, username nvarchar(10)

, password varbinary(100)

)

go

/*

--EncryptByPassPhrase

*/

INSERT INTO  ttt VALUES(1, N'smith',  EncryptByPassPhrase(N'12',  N'XXX'));

INSERT INTO  ttt VALUES(2, N'kennal', EncryptByPassPhrase(N'12',  N'YYY'));

INSERT INTO  ttt VALUES(3, N'staurt', EncryptByPassPhrase(N'12',  N'ZZZ'));

INSERT INTO  ttt VALUES(4, N'hayden', EncryptByPassPhrase(N'1',   N'KKK'));

INSERT INTO  ttt VALUES(5, N'hayden', EncryptByPassPhrase(N'abc', N'abcde'));

go

-- Syntax: ENCRYPTBYPASSPHRASE('PASSPHRASE','text')

-- In this statement, PASSPHRASE specifies the data string to be used to derive an encryption key,

-- and 'text' data type should be VarBinary.

SELECT * FROM ttt;

/*

-- DECRYPTBYPASSPHRASE

*/

SELECT seq, username, cast(DECRYPTBYPASSPHRASE(N'12', password) as nvarchar(10)) as Password 

FROM ttt;

SELECT seq, username, convert(nvarchar(10), DECRYPTBYPASSPHRASE (N'1',password))

FROM ttt;

SELECT username, cast(DECRYPTBYPASSPHRASE(N'abc', password) as nvarchar(10)) as Password 

FROM ttt

where seq = 5;

-- Miss Match : 'N'-unicode prefix

SELECT seq, username

     , cast(DECRYPTBYPASSPHRASE(N'abc', password) as nvarchar(10)) as Password 

     , cast(DECRYPTBYPASSPHRASE('abc', password) as nvarchar(10)) as Password 

FROM ttt

where seq in (5);

-- Miss Match : data type with encryption key

INSERT INTO  ttt VALUES(6, N'hayden', EncryptByPassPhrase('abc', 'ABCDE'));

SELECT seq, username

     , cast(DECRYPTBYPASSPHRASE('abc', password) as varchar(10)) as Password 

     , cast(DECRYPTBYPASSPHRASE('abc', password) as nvarchar(10)) as Password 

FROM ttt

where seq in (6);

-- Miss match : Case Sensitive

SELECT username

     , cast(DECRYPTBYPASSPHRASE(N'abc', password) as nvarchar(10)) as Password 

     , cast(DECRYPTBYPASSPHRASE(N'ABC', password) as nvarchar(10)) as Password 

FROM ttt

where seq = 5;