This Article explains you how to Encrypt and Decrypt a text
Introduction
This Article explains you how to Encrypt and Decrypt a text.you can encrypt a password and can store a password as VarBinary in a column by using EncryptByPassPhrase function. Encrypted column can be decrypted by using DECRYPTBYPASSPHRASE function.
Explanation
EncryptByPassPhrase:
EncryptByPassPhrase uses the Triple DES algorithm to encrypt the text passed in.
Syntax: ENCRYPTBYPASSPHRASE('PASSPHRASE',‘text’)
In this statement, PASSPHRASE specifies the data string to be used to derive an encryption key,and 'text' data type shoulb be VarBinary.
Creating a 'login_details' table:-
create table login_details(uid integer,username varchar(10),password varbinary(100))
insert into login_details(uid,username,password) values(1,'smith',EncryptByPassPhrase('12',’XXX’))insert into login_details(uid,username,password) values(2,'kennal',EncryptByPassPhrase('12','YYY'))insert into login_details(uid,username,password) values(3,'staurt',EncryptByPassPhrase('12','ZZZ'))
select * from login_details
DECRYPTBYPASSPHRASE:-
Encrypted column can be decrypted by using DECRYPTBYPASSPHRASE.
DECRYPTBYPASSPHRASE function takes two arguments one is 'PASSPHRASE'and text or column_name.
select uid,username, DECRYPTBYPASSPHRASE ('12',password) as Password from login_details
In the above result the password still in VarBinary.So we have to Convert the VarBianry in Varchar by using Convert function as follows.
select uid,username,convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password)) from login_details
