How to Encrypt and Decrypt Text

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 using the EncryptByPassPhrase function. The 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 derive an encryption key, and the 'text' data type should 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

An encrypted column can be decrypted using DECRYPTBYPASSPHRASE.

DECRYPTBYPASSPHRASE function takes two arguments: ' 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 using the Convert function as follows.

SELECT uid,username,convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password)) FROM login_details

Conclusion

This article taught us how to Encrypt and Decrypt text with code examples. Continue reading: Encryption And Decryption Using A Symmetric Key In C#.