Home > T-SQL > SQL SERVER – Caesar cipher stored procedure – Script Download – Encryption Algorithm

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

encrypt
— =============================================
— 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:
example1

Decryption Stored Procedure: CCDecrypt

decrypt— =============================================
— 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:
dtext
Advertisements
  1. syeed
    July 21, 2014 at 2:05 am

    Thanks for this article it was very helpful. Can you please post the procedure for 2. Monoalphabetic Cipher really appericate it.

  2. Manuel Maurer
    May 25, 2016 at 6:02 am

    This code was very helpful!
    Thank you very much.

    MfG
    Manuel M.

  3. Manuel Maurer
    May 25, 2016 at 6:06 am

    Thank you this code was very helpful.
    Hope to see other nice Code Parts in the nearer future

    Yours,
    Manuel M.

  4. Manuel Maurer
    May 25, 2016 at 6:10 am

    Please give me cock!

    Kind regards
    Manuel M.

  5. March 21, 2017 at 12:33 am

    please share download files.

    • April 7, 2017 at 5:55 pm

      you can just copy the sql script on the page and use it. it is a working example.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: