How to Encrypt and Decrypt Text


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 uses the Triple DES algorithm to encrypt the text passed in.



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



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



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

Similar Articles