Simple Encryption

With the closer integration of SQL Server and dotNET, this bit of functionality may well be obsolete, but looking through my history of saved scripts, I ran across a very simplistic Encryption routine I had created some time ago. Originally I had written a Visual Basic (3.0?)  routine to encrypt data back in the mid 90’s. At some point on another project I had a reason (although I don’t recall why any more) o move that code to the back-end, and thus wrote this SQL script.
This routine takes 3 parameters (the string to manipulate; ‘Encrypt’ or ‘Decrypt’; and the encryption key value), and doing some quite simple math, hides the original value from prying eyes. Since the SP is created using the WITH ENCRYPTION parameter, nobody can see the actual math you are doing to the value to determine the return value. With a few minor modifications you could include a SALT value, as another parameter or keep the encryption key stored in the code itself.
While the recommended approach to encryption would be to choose more advanced option using the dotNET Framework, this should give at least a cursory overview of the potential for string manipulation to secure data.
The code:
CREATE procedure SimpleEncryption
(@strToConvert varchar(2000),
@strConvertType char(1),
@strEncryptKey varchar(100))
WITH ENCRYPTION AS
DECLARE @strConvertTo varchar(2000)
DECLARE @strConLetter char(5)
DECLARE @strKeyLetter char(5)
DECLARE @i int
DECLARE @k int
set @i = 0
set @strConvertTo = ”
if @strEncryptKey <> ”
IF @strConvertType = ‘E’
WHILE @i < len(@strToConvert)
BEGIN
set @i = @i + 1
set @strConLetter = ASCII(SUBSTRING(@strToConvert, @i, 1))
set @k = @i % len(@strEncryptKey)
if @k = 0
set @k = len(@strEncryptKey)
set @strKeyLetter = ASCII(SUBSTRING(@strEncryptKey, @k, 1))
set @strConvertTo = @strConvertTo + char(ASCII(SUBSTRING(@strToConvert, @i, 1)) + ASCII(SUBSTRING(@strEncryptKey, @k, 1)))
END
ELSE IF @strConvertType=’D’
WHILE @i < len(@strToConvert)
BEGIN
set @i = @i + 1
set @strConLetter = ASCII(SUBSTRING(@strToConvert, @i, 1))
set @k = @i % len(@strEncryptKey)
if @k = 0
set @k = len(@strEncryptKey)
set @strKeyLetter = ASCII(SUBSTRING(@strEncryptKey, @k, 1))
set @strConvertTo = @strConvertTo + char(ASCII(SUBSTRING(@strToConvert, @i, 1)) – ASCII(SUBSTRING(@strEncryptKey, @k, 1)))
END
ELSE
select ‘No Valid Parameter Passed’
else
select ‘No Valid Encyption Key Passed’
select @strConvertTo
GO
Advertisements

About Rodibidably

Jeff Randall is a frequent volunteer for free-thought organizations, including the Center For Inquiry – DC. Having been blogging since January 2008, he decided that a community of bloggers would be an interesting new experience (or at the very least a fun way to annoy his friends into reading his posts more frequently). Since finding out about about the existence of, and then joining, the atheist/skeptic community in 2007 he has been committed to community activism, critical thinking in all aspects of life, science, reason, and a fostering a secular society.
This entry was posted in SQL Server. Bookmark the permalink.

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