Encrypt and Decrypt Text in SQL

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.

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