Simple string encryption and decryption function in SQL

Simple Password encryption / decryption SQL function:

In the SQL Query we can easily do Encryption and Decryption. Mostly we use to encrypt password column value for the security purpose.  For the SQL Encryption the below function will help you to easy encrypt and decrypt your value.

The below function for Encrypt the user value using SQL Encryption. The ‘Your Key’ value is for the encrypt KEY. The same key you need to use for the decryption also.

CREATE FUNCTION [dbo].[FN_ENCRYPTION]

(

            @INPUT VARCHAR(MAX)

)

            RETURNS VARBINARY(MAX)

            AS BEGIN

            DECLARE @OUTPUT VARBINARY(MAX)

            SELECT @OUTPUT = ENCRYPTBYPASSPHRASE('Your Key',@INPUT)

            RETURN @OUTPUT

END

Example :

SELECT [dbo].[FN_ENCRYPTION] ('vijay')

Result: 0x010000008A2D548FFD5D193EE01FF10DA1356C7D858ADAAA2A014A4F

The below function will decrypt the encrypted binary value to normal string value. The same encryption KEY you need to pass for the decryption.

CREATE FUNCTION [dbo].[FN_DECRYPTION]

(

            @INPUT VARBINARY(MAX)

)

            RETURNS VARCHAR(MAX)

            AS BEGIN

            DECLARE @OUTPUT VARCHAR(MAX)

            SELECT @OUTPUT = DECRYPTBYPASSPHRASE(Your Key ',@INPUT)

            RETURN @OUTPUT

END

Example :

SELECT [dbo].[FN_ENCRYPTION] ('vijay')

Result: 0x010000008A2D548FFD5D193EE01FF10DA1356C7D858ADAAA2A014A4F

SELECT [dbo].[FN_DECRYPTION] (0x010000008A2D548FFD5D193EE01FF10DA1356C7D858ADAAA2A014A4F)

Result: vijay