SQL SERVER – Caesar cipher stored procedure – Script Download – Encryption Algorithm

Cryptography is the oldest technique, we were using in the past (before computer born) to secure our communication. Encryption is a methodology to convert the original readable text to unreadable format. Decryption is the reverse engineering of encryption.

In modern world, the cryptography has evolved drastically with mathematics, computer science, and electrical engineering. It help the encryption process more complex. So, It cannot (may be)  broken by the third-party.

In this blog post, I am going to introduce two stored procedures that encrypt the plain text and other procedure decrypt the encrypted text. I am using caesar’s ciper technique. It is a type of substitution cipher in which each letter in the plaintext is replaced by a letter some fixed number of positions down the alphabet -Wikipedia.

Encryption Stored procedure: CCEncrypt

— =============================================
— Author:        Ayyappan Thangaraj, SQLServerRider.com
— Create date: 1/22/2013
— Description:    Caesar Encryption algorithm
— =============================================
Create PROCEDURE SP_CCEncrypt
@Ptext as varchar(500)
AS
BEGIN
SET NOCOUNT ON;
declare @SHIFTNO as tinyint = 3
declare @Etext as varchar(500) =”
declare @pc as varchar(1)
Declare @i as smallint = 1
Declare @n as smallint

set @n = len(@Ptext)
set @Ptext = upper(@Ptext)

while @i < = @n
BEGIN
set @pc = SUBSTRING(@Ptext, @i, 1)
if ascii(@pc) between 65 and 90
if ascii(@pc)+@SHIFTNO > 90
set @pc = char((ascii(@pc)+@SHIFTNO)-90+64)
else
set @pc = char((ascii(@pc)+@SHIFTNO))

set @Etext = @Etext + @pc

Set @i = @i + 1
END

select @Etext
END
GO

 

Example:

Decryption Stored Procedure: CCDecrypt

— =============================================
— Author:        Ayyappan Thangaraj, SQLServerRider.com
— Create date: 1/22/2013
— Description:    Caesar Decryption algorithm
— =============================================
Create PROCEDURE SP_CCDecrypt
@Etext as varchar(500)
AS
BEGIN
SET NOCOUNT ON
declare @SHIFTNO as smallint = -3
declare @Ptext as varchar(500) =”
declare @Ec as varchar(1)
Declare @i as smallint = 1
Declare @n as smallint

set @n = len(@Etext)
set @Etext = upper(@Etext)

while @i < = @n
BEGIN
set @Ec = SUBSTRING(@Etext, @i, 1)
if ascii(@Ec) between 65 and 90
if ascii(@Ec)+@SHIFTNO < 65
set @Ec = char(91-(65-(ascii(@Ec)+@SHIFTNO)))
else
set @Ec = char((ascii(@Ec)+@SHIFTNO))

set @Ptext = @Ptext + @Ec

Set @i = @i + 1
END

select lower(@Ptext)
END
GO

 

Example: