Introduction
This article explains how to Encrypt and Decrypt 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 using the 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 should be VarBinary.
Creating a 'login_details' table:
CREAE 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:
An encrypted column can be decrypted 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 is 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
