While it may be popular practice to store passwords in a database, it is generally not so common to store them correctly. You have three choices on how to do it when you store a password in a database. You can save your password as:
- Unencrypted text that can be viewed by anyone with the read access to the table
- Encrypted text that can be decrypted
- Encrypted text that can never be decrypted
Usually, we need to decrypt our encrypted values for various reasons. So option 2 “Encrypted text that can be decrypted” is usually followed for this purpose.
In this article, we will discuss how to Encrypted and decrypt text, what errors arise while doing so and what is the recommended way to follow.
Encrypted text that can be decrypted:
Everyone understands what a password is, fewer individuals are familiar with passphrases. The passphrase is comparatively more secure. “Entropy” is a notion in the theory of information technology that refers to the amount of randomness a password holds. Usually, the more randomness a password contains, the more difficult it is to break the password by the hackers. That’s the reason longer passwords are preferred as they are probably more “random“. By adding words to password the entropy can be increased. In SQL Server 2008 and above “ENCRYPTBYPASSPHRASE” function supports encryption of passphrase where you can use phrases like “I would love some ice cream” as your password than passwords which don’t support spaces. Moreover, it can encrypt your more secure password.
The “ENCRYPTBYPASSPHRASE” function allows us to encrypt any string or VARBINARY value up to 7975 bytes. It needs two parameters, @passphrase, and @cleartext, and returns a value containing the encrypted values of type VARBINARY(8000).
Encrypt passphrase information using the 128-key bit length TRIPLE DES algorithm. A passphrase is a space-including password. The benefit of using a passphrase is that remembering a significant sentence or phrase is simpler than remembering a comparatively lengthy character string.
“ENCRYPTBYPASSPHRASE” offers a fast and easy way to encrypt SQL Server columns of data. This function is available in SQL Server 2008 and up.
EncryptByPassPhrase (@passphrase, @cleartext)
An asymmetric key is generated from a passphrase. This can be varchar, char, varchar, binary, VARBINARY, or NCHAR type variable that contains a passphrase from which to produce a symmetric key. Any other data type, such as a DATE, you must first explicitly convert to a binary value to encrypt a birthdate.
An NVARCHAR, char, varchar, binary, VARBINARY, or NCHAR type variable containing the plain text. The maximum size is eight thousand bytes.
VARBINARY with 8,000 bytes maximum size.
CREATE TABLE dbo.encrypted_data ( [Id] INT IDENTITY(1,1) PRIMARY KEY, [password] VARBINARY(8000) );
The id value will stay unencrypted, whereas the password column is of type VARBINARY(8000), which matches the return type of the ENCRYPTBYPASSPHRASE function.
INSERT INTO dbo.encrypted_data([password]) VALUES(ENCRYPTBYPASSPHRASE('SQL SERVER 2017','This is my credit card number!'));
It is seen that the first parameter is the passphrase which provides a symmetric key. Whereas the second one is the text value needs to be encrypted.
select * from encrypted_data
The result set will look like.
That statement of SELECT returns a value of VARBINARY such as:
It is important to note that the VARBINARY value returned from ENCRYPTBYPASSPHRASE is non-deterministic. This means that it will not generate the same result every time even with the same input. So the same SELECT statement if we run them two times, we can get two different outputs.
SELECT ENCRYPTBYPASSPHRASE('SQL SERVER 2017','This is my credit card number!'); SELECT ENCRYPTBYPASSPHRASE('SQL SERVER 2017','This is my credit card number!'); SELECT ENCRYPTBYPASSPHRASE('SQL SERVER 2017','This is my credit card number!');
The select statement will give out put like this:
“0x010000002D69EDEEA6B486FAF38FB353CF2682EE30B450488109BD0E23B88E03CD48F72673C6A462B210D6CE38E00E9F8BA18AB7” for first select statement.
“0x010000007C5736E4B0F15D971D771D2D146924F741D2DB04477C882A6BAF65703F01D950B1AD01E7E4E27E89243F6A4F549170E2” for second statement.
“0x010000009B49BEBAB4B5E510C0C2998604A005617398631C2EBFB0E3C08DDF27C2A06F447FC11BD10B416C7E58038109EBD52021” for third statement.
We need to be able to decrypt it now because our password has been encrypted. This is done easily by using the feature DECRYPTBYPASSPHRASE with the same passphrase with which we encrypted our password.
Execute the following code. And you will get the password with some long length of alphanumeric characters as an output like this.
select id,DECRYPTBYPASSPHRASE('SQL SERVER 2017',password) as password from encrypted_data
That is not a password. Like ENCRYPTBYPASSPHRASE, DECRYPTBYPASSPHRASE returns a VARBINARY value to be encoded into a functional string. By adding a CONVERT or cast function to our SELECT statement, this can be done easily.