?
Solved

Using xor operator with a string field and a character in ms sql 2005

Posted on 2007-10-03
1
Medium Priority
?
1,142 Views
Last Modified: 2008-01-09
I have a table in my ms sql 2005 with a fields called name. I want to save the name in a simple encrypted form which I can descrypt later and easily.

IU would like to use anx xor operator and a character that can later be used to recover the original field data

0
Comment
Question by:robrodp
1 Comment
 
LVL 18

Accepted Solution

by:
PFrog earned 2000 total points
ID: 20020450
Try this...


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF NOT OBJECT_ID('myEncrypt') IS NULL
      DROP FUNCTION myEncrypt
GO
IF NOT OBJECT_ID('myDecrypt') IS NULL
      DROP FUNCTION myDecrypt
GO

CREATE FUNCTION myEncrypt
(
      @Name varchar(max),
      @Encryptor char(1)
)
RETURNS varchar(max)
AS
BEGIN
      DECLARE @ret varchar(max)
      DECLARE @i int

      SET @i=1
      SET @ret = ''
      
      WHILE @i<=len(@Name)
      BEGIN
            SET @ret = @ret + Right('000' + CAST((ascii(substring(@Name,@i,1)) ^ ascii(@Encryptor)) AS varchar(3)),3)
            SET @i = @i + 1
      END

      RETURN @ret

END
GO

CREATE FUNCTION myDecrypt
(
      @Name varchar(max),
      @Encryptor char(1)
)
RETURNS varchar(max)
AS
BEGIN
      DECLARE @ret varchar(max)
      DECLARE @i int

      SET @i=1
      SET @ret = ''
      
      WHILE @i<=len(@Name)
      BEGIN
            SET @ret = @ret + Char(CAST(substring(@Name,@i,3) AS INT) ^ ascii(@Encryptor))
            SET @i = @i + 3
      END

      RETURN @ret

END
GO

SELECT dbo.myEncrypt('hello','b')

SELECT dbo.myDecrypt('010007014014013','b')
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question