-
Encryption/Decryption function in Sql Server
almost 10 years ago
To encrypt and decrypt a string we have a predefined functions in Sql Server 2008 that are easy to use. Suppose we want to save some data that doesn't need to be exposed as a plain text then in such case we can use these functions, in real scenario as we are storing password in a database.
These functions are ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE. The result of the encrypted string is in VARBINARY data type format. To encrypt the text this function uses Triple DES algorithm.
- DECLARE @EncryptedString as VARBINARY(MAX);
- SET @EncryptedString = (SELECT ENCRYPTBYPASSPHRASE ('Key@12','StringToEncrypt'));
- SELECT @EncryptedString AS 'Encrypted String '
- SELECT CONVERT(VARCHAR(150), DECRYPTBYPASSPHRASE ('Key@12',@EncryptedString)) AS 'Decrypted String'
Here is a sample of practical use to save passwords for each user in encrypted form and decrypt their password in case we want to authenticate the user.
- CREATE TABLE tblUser
- (
- ID INT,
- Username VARCHAR(250),
- EncryptedPassword VARBINARY(MAX)
- )
- INSERT INTO tblUser VALUES(1, 'User1', ENCRYPTBYPASSPHRASE('Key@12!','Password1'))
- INSERT INTO tblUser VALUES(1, 'User2', ENCRYPTBYPASSPHRASE('Key@12!','Password2'))
- SELECT * FROM tblUser
- SELECT ID, Username, EncryptedPassword, CONVERT(VARCHAR(150), DECRYPTBYPASSPHRASE ('Key@12!', EncryptedPassword)) AS 'Plain Password'
- FROM tblUser
The result of the above script is shown below:
Note: For more security we can save unique key like guid as password salt for each user.
0 Comment(s)