반응형
/****************************************************************************************************************
-- Title : [SQL2012] Encription/Decription by encryptbypassphrase/decryptbypassphrase
-- Reference : forums.asp.net etc
-- Key word : 암호화 복호화 양방향 encryption decryption encryptbypassphrase decryptbypassphrase passphrase
암호 복호 encrypt decrypt
****************************************************************************************************************/
-- 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;
반응형