Data encryption - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Data encryption

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-06-14 : 01:50:13

I want to change the first_name and last_name field values to some other values so that some other persons looking at will have a different first and last name.The same with the SSN number and another thing is I need to know the reverse logic to get back these values if needed.This is just for security reasons.Please advise.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-06-14 : 02:21:58

please advise

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 02:45:09

Use this function for very simple encryption/decryption

CREATE FUNCTION dbo.fnSimpleEncDec
(
	@StringText VARCHAR(8000),
	@PasswordCharacter CHAR(1)
)
RETURNS VARCHAR(8000)
AS

BEGIN
	DECLARE @Index SMALLINT,
		@ReturnText VARCHAR(8000)

	SELECT	@Index = DATALENGTH(@StringText),
		@ReturnText = ''

	WHILE @Index > 0
		SELECT	@ReturnText = CHAR(ASCII(SUBSTRING(@StringText, @Index, 1)) ^ ASCII(@PasswordCharacter)) + @ReturnText,
			@Index = @Index - 1

	RETURN @ReturnText
END

To encrypt the fields, write

UPDATE MyTable
SET    first_name = dbo.fnSimpleEncDec(first_name, '?'),
       last_name = dbo.fnSimpleEncDec(last_name, '*'),
       ssn = dbo.fnSimpleEncDec(ssn, 'x')

To decrypt the fields, write

UPDATE MyTable
SET    first_name = dbo.fnSimpleEncDec(first_name, '?'),
       last_name = dbo.fnSimpleEncDec(last_name, '*'),
       ssn = dbo.fnSimpleEncDec(ssn, 'x')

You can use whatever character you like as password. There is one caveat or drawback! You can't use a password character that normally exist in the field, such as 'e'. If you do, SQL server truncates the string at the position where field contains same character as password character used.

For SSN you can use whatever character not used for SSN, such as '*', '?' or 'x'. For names, I would prefer using

SELECT dbo.fnSimpleEncDec(first_name, CHAR(31))

because then I will XOR 5 bits out of 8 for every character in the text to be encrypted or decrypted! And the text still looks somewhat normal.
Another exiting password character is CHAR(255)! That makes the field text go "inverse".

Peter Larsson
Helsingborg, Sweden

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-06-14 : 03:21:49

a b c d e f g h i j k l m n o p q r s t u v w x y z
b c d e f g h i j k l m n o p q r s t u v w x y z a
c d e f g h i j k l m n o p q r s t u v w x y z a b
d e f g h i j k l m n o p q r s t u v w x y z a b c
e f g h i j k l m n o p q r s t u v w x y z a b c d
f g h i j k l m n o p q r s t u v w x y z a b c d e
g h i j k l m n o p q r s t u v w x y z a b c d e f
h i j k l m n o p q r s t u v w x y z a b c d e f g
i j k l m n o p q r s t u v w x y z a b c d e f g h
j k l m n o p q r s t u v w x y z a b c d e f g h i
k l m n o p q r s t u v w x y z a b c d e f g h i j
l m n o p q r s t u v w x y z a b c d e f g h i j k
m n o p q r s t u v w x y z a b c d e f g h i j k l
n o p q r s t u v w x y z a b c d e f g h i j k l m
o p q r s t u v w x y z a b c d e f g h i j k l m n
p q r s t u v w x y z a b c d e f g h i j k l m n o
q r s t u v w x y z a b c d e f g h i j k l m n o p
r s t u v w x y z a b c d e f g h i j k l m n o p q
s t u v w x y z a b c d e f g h i j k l m n o p q r
t u v w x y z a b c d e f g h i j k l m n o p q r s
u v w x y z a b c d e f g h i j k l m n o p q r s t
v w x y z a b c d e f g h i j k l m n o p q r s t u
w x y z a b c d e f g h i j k l m n o p q r s t u v
x y z a b c d e f g h i j k l m n o p q r s t u v w
y z a b c d e f g h i j k l m n o p q r s t u v w x
z a b c d e f g h i j k l m n o p q r s t u v w x y

Above shown is a matrix,

Can anybody help me in creating a function where I pass field name and it has a value called Smith
Now I have code say 'masktext'

Now the function should compare 'S' and 'M' and take the join which is 'E' and now take the next one
'M' and 'A' which is 'M'

Like that it should take the join and create a word so that Thats my encrypted value for Smith.Iam finding difficult to create
the function.Please help me with that

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 03:24:13

Looks like a home assignment to me. For further reference, take a look at http://en.wikipedia.org/wiki/Caesar_cipher

In your example above, how do you encrypt numbers and spaces such as Street addresses?

110 North Street

or names that looks like "Mathers Junior" or "Astor III"

Peter Larsson
Helsingborg, Sweden

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-06-14 : 03:30:00

No..this is kind of function I need to use for scrampling data.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 03:39:23

quote:
Originally posted by sqllearner

No..this is kind of function I need to use for scrampling data.

And my function I posted earlier does not work for you?

Peter Larsson
Helsingborg, Sweden

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-06-14 : 03:45:04

The one posted earlier is excellent but for my requirement it should be replaced with text values (when I use the function iam getting ascii values)and the above shown is a proposed method as well.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 03:50:00

quote:
Originally posted by sqllearner

The one posted earlier is excellent but for my requirement it should be replaced with text values (when I use the function iam getting ascii values)and the above shown is a proposed method as well.

Strange. My function above returns text VARCHAR(8000), not numeric values.
Try writing in QA

SELECT dbo.fnSimpleEncDec('Fzl>?Kwz?yjq|kvpq?hpmtl?xmz~k?hvkw?kzgk1', CHAR(31))

What does the function return to you?

Peter Larsson
Helsingborg, Sweden

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-06-14 : 03:53:00

something like this '7ZZZZZZ'.Can u please help me with the function I have mentioned

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 03:57:53

quote:
Originally posted by sqllearner

something like this '7ZZZZZZ'.Can u please help me with the function I have mentioned

That result should not be possible, unless you are using UNICODE.
What happens when you try to decode the text? Write

SELECT dbo.fnSimpleEncDec('7ZZZZZZ', *the password character you used before to encrypt*)

Anyone else getting the same strange result? Please post your result here of the query.
Write following query in QA

SELECT dbo.fnSimpleEncDec(CONVERT(VARCHAR(8000), 'Fzl>?Kwz?yjq|kvpq?hpmtl?xmz~k?hvkw?kzgk1'), CONVERT(VARCHAR, CHAR(31)))

At the page for which I sent you a link, there are examples for writing code for Caesar Cipher.

Otherwise use algorithm like this

DECLARE @InputCharacter CHAR(1), @MaskCharacter CHAR(1), @UseUpperCase BIT
SELECT @InputCharacter = 's', @MaskCharacter = 'm', @UseUpperCase = 0

SELECT CHAR((ASCII(LOWER(@InputCharacter)) + ASCII(LOWER(@MaskCharacter)) - 194) % 26 + CASE WHEN @UseUpperCase = 1 THEN 65 ELSE 97 END)

You are aware that the function you need is irreversible? Text like "North Street" will never look the same again efter first encrypting and then decrypting? "North Street" will look like "north street" or (with same change to function) "NORTH STREET".

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-06-14 : 04:16:40

"Anyone else getting the same strange result?"

Yes! The function works great with text.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 04:25:04

quote:
Originally posted by Kristen

"Anyone else getting the same strange result?"

Yes! The function works great with text.

Thanks Kristen. I knew I could count on you

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 04:36:01

This is the substitution schema used, just for allowing A-Z, a-z, space and 0-9. A 63 by 63 matrix. Allowing substition of complete ASCII set, is a 256 by 256 matrix that is 16 times larger than this one.

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z  0 1 2 3 4 5 6 7 8 9
B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A
C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B
D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C
E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D
F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E
G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F
H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G
I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H
J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I
K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J
L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K
M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L
N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M
O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N
P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O
Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P
R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q
S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R
T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S
U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T
V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U
W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V
X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W
Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X
Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y
a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a
c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b
d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c
e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d
f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e
g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f
h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g
i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h
j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i
k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j
l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k
m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l
n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m
o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n
p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o
q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p
r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q
s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r
t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s
u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t
v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u
q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v
x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q
y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x
z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y
 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z
0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0
1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0
2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1
3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2
4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3
5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4
6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5
7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6
8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7
9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8

Peter Larsson
Helsingborg, Sweden

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-06-14 : 05:12:55

How can I create a function out of the matrix given above which compares the field value like name 'smith' to one mask password value like 'maskvalue.So the comparison would be

's' to 'm' and 'm' to 'a' and 'i' to 's'...likewise.Please advise a function something like

DECLARE @name varchar(50)
SET @name ='smith'
DECLARE @password varchar(50)
SET @password='maskvalue'
DECLARE @Index INT
DECLARE @ReturnText varchar(1000)
SELECT @Index = 1,
@ReturnText = ''
PRINT @index

WHILE @Index <= DATALENGTH(@name)
SELECT @ReturnText = CASE
WHEN SUBSTRING(@name, @Index, 1)IN ('a') Then 'm'
WHEN SUBSTRING(@name, @Index, 1)IN ('b') Then 'n'
WHEN SUBSTRING(@name, @Index, 1)IN ('c') Then 'o'
WHEN SUBSTRING(@name, @Index, 1)IN ('d') Then 'p'
WHEN SUBSTRING(@name, @Index, 1)IN ('e') Then 'q'
WHEN SUBSTRING(@name, @Index, 1)IN ('f') Then 'r'
WHEN SUBSTRING(@name, @Index, 1)IN ('g') Then 's'
WHEN SUBSTRING(@name, @Index, 1)IN ('h') Then 't'
WHEN SUBSTRING(@name, @Index, 1)IN ('i') Then 'u'
WHEN SUBSTRING(@name, @Index, 1)IN ('j') Then 'v'
WHEN SUBSTRING(@name, @Index, 1)IN ('k') Then 'w'
WHEN SUBSTRING(@name, @Index, 1)IN ('l') Then 'x'
WHEN SUBSTRING(@name, @Index, 1)IN ('m') Then 'y'
WHEN SUBSTRING(@name, @Index, 1)IN ('n') Then 'z'
WHEN SUBSTRING(@name, @Index, 1)IN ('o') Then 'a'
WHEN SUBSTRING(@name, @Index, 1)IN ('p') Then 'b'
WHEN SUBSTRING(@name, @Index, 1)IN ('q') Then 'c'
WHEN SUBSTRING(@name, @Index, 1)IN ('r') Then 'd'
WHEN SUBSTRING(@name, @Index, 1)IN ('s') Then 'e'
WHEN SUBSTRING(@name, @Index, 1)IN ('t') Then 'f'
WHEN SUBSTRING(@name, @Index, 1)IN ('u') Then 'g'
WHEN SUBSTRING(@name, @Index, 1)IN ('v') Then 'h'
WHEN SUBSTRING(@name, @Index, 1)IN ('w') Then 'i'
WHEN SUBSTRING(@name, @Index, 1)IN ('x') Then 'j'
WHEN SUBSTRING(@name, @Index, 1)IN ('y') Then 'k'
WHEN SUBSTRING(@name, @Index, 1)IN ('z') Then 'l'
ELSE 's'
END + @ReturnText,@Index = @Index + 1

PRINT @ReturnText

This is not giving me the right answer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 05:15:36

With this,

DECLARE @InputCharacter CHAR(1), @MaskCharacter CHAR(1), @UseUpperCase BIT
SELECT @InputCharacter = 's', @MaskCharacter = 'm', @UseUpperCase = 0

SELECT CHAR((ASCII(LOWER(@InputCharacter)) + ASCII(LOWER(@MaskCharacter)) - 194) % 26 + CASE WHEN @UseUpperCase = 1 THEN 65 ELSE 97 END)

I am pointing you out in the right direction.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 05:25:45

Use this function

CREATE FUNCTION dbo.fnCaesarCipher
(
	@TextToCipher VARCHAR(8000),
	@MaskText VARCHAR(100),
	@UseUpperCase BIT
)
RETURNS VARCHAR(8000)
AS

BEGIN
	DECLARE @Index SMALLINT,
		@CipheredText VARCHAR(8000)

	SELECT	@MaskText = REPLICATE(@MaskText, 1 + LEN(@TextToCipher) / LEN(@MaskText)),
		@Index = DATALENGTH(@TextToCipher),
		@CipheredText = ''

	WHILE @Index > 0
		SELECT	@CipheredText = CHAR((ASCII(LOWER(SUBSTRING(@TextToCipher, @Index, 1))) + ASCII(LOWER(SUBSTRING(@MaskText, @Index, 1))) - 194) % 26 + CASE WHEN @UseUpperCase = 1 THEN 65 ELSE 97 END) + @CipheredText,
			@Index = @Index - 1

	RETURN @CipheredText
END

And call with

select dbo.fnCaesarCipher('HomeWorkAssignment', 'xyz', 0)

I really hope you are satisfied now when your home work assignment is done.

Peter Larsson
Helsingborg, Sweden

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-06-14 : 05:48:07

Yes Sir..Thanks a lot for the help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 06:35:29

Use this function to decipher the Caesar Cipher.

CREATE FUNCTION dbo.fnCaesarDecipher
(
	@TextToDecipher VARCHAR(8000),
	@MaskText VARCHAR(8000),
	@UseUpperCase BIT
)
RETURNS VARCHAR(8000)
AS

BEGIN
	DECLARE @Index SMALLINT,
		@DecipheredText VARCHAR(8000)

	SELECT	@MaskText = REPLICATE(@MaskText, 1 + LEN(@TextToDecipher) / LEN(@MaskText)),
		@Index = DATALENGTH(@TextToDecipher),
		@DecipheredText = ''

	WHILE @Index > 0
		SELECT	@DecipheredText = CHAR(CASE WHEN @UseUpperCase = 1 THEN 65 ELSE 97 END + ((26 + ASCII(LOWER(SUBSTRING(@TextToDecipher, @Index, 1))) - ASCII(LOWER(SUBSTRING(@MaskText, @Index, 1)))) % 26)) + @DecipheredText,
			@Index = @Index - 1

	RETURN @DecipheredText
END

select dbo.fnCaesarDecipher('emlbunoizpqhdllbls', 'xyz', 0)

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 06:39:04

You will have to write your logic yourself to implement some security features, such as stripping off all other characters than a-z in the function.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 06:41:55

quote:
Originally posted by sqllearner

No..this is kind of function I need to use for scrampling data.

It's better if you are true to us who helps you. A clever person would not use an irreversible function to scramble data "for security reasons" when also asking for "reverse logic".

However, it was fun to write the functions. Hopefully someone else might also benifit from them.

Peter Larsson
Helsingborg, Sweden

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources