Encryption & Decryption

These scripts can be used encrypt and decrypt data in a table. This can be used in columns with data integer and characters. This can be useful when you need to save your data in a format that no one can read. Only the DBA who knows the integer key value to return back can read the content back.

Function to encrypt data:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[encryptdata] (@firstname VARCHAR(50), @key INT)
RETURNS VARCHAR(60)
AS
BEGIN
DECLARE @firstname1 INT
DECLARE @key2 VARCHAR(10)
DECLARE @encryptdata VARCHAR(60)
DECLARE @len INT
SELECT @encryptdata = ''
SELECT @len=LEN(@firstname)
WHILE @len>0
BEGIN
SELECT @firstname1 = ASCII(SUBSTRING(@firstname,@len,1))
SELECT @key2=@firstname1+@key
SELECT @encryptdata = @encryptdata+CHAR(@key2)
SELECT @len = @len-1
END
RETURN(@encryptdata)
END;
GO

Function to decrypt data:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[decryptdata] (@firstname VARCHAR(50), @key INT)
RETURNS VARCHAR(60)
AS
BEGIN
DECLARE @firstname1 INT
DECLARE @key2 VARCHAR(10)
DECLARE @encryptdata VARCHAR(60)
DECLARE @len INT
SELECT @encryptdata = ''
SELECT @len=LEN(@firstname)
WHILE @len>0
BEGIN
SELECT @firstname1 = ASCII(SUBSTRING(@firstname,@len,1))
SELECT @key2=@firstname1-@key
SELECT @encryptdata = @encryptdata+CHAR(@key2)
SELECT @len = @len-1
END
RETURN(@encryptdata)
END;
GO

How to Use the script

CREATE TABLE #tmp(acno INT)
INSERT #tmp VALUES('25469863')
GO
SELECT acno FROM #tmp

Normal Output:

 

acno
———–
25469863

Now we are going to encrypt the data 25469863 with the key value as 8 (this can be changed with your value)

SELECT dbo.encryptdata(acno,8) AS acno FROM #tmp

Encrpted Output:
acno
—————-
;>@A><=:

 

The value 25469863 has been encrypted to ;>@A><=:, hence once you decrypt this using decryptdata function you can get the original value.

select dbo.decryptdata(';>@A><=:',8) as acno

Decrypted Output
acno
—————-
25469863


Posted

in

by

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *