Decryption - 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

General SQL Server Forums

New to SQL Server Programming

Decryption

Author

Topic

avmreddy17
Posting Yak Master

180 Posts

Posted - 2007-10-11 : 10:18:31

I am using the below function written by PESO to encrypt/Decrypt the data for my SSN Numbers stored as a Clear Text

First I am Encrypting all my SSN's using this function and storing it in the database in a seperate Column,

Some thing like this..

SELECT DBACentral.dbo.fnEncDecRc4( '145678908' , 'ty6578dmp1203' )
OUPPUT
--------
âUÙN_{��

How to Decrypt ( âUÙN_{�� ) using the below function.

CREATE FUNCTION dbo.fnEncDecRc4
(
@Pwd VARCHAR(256),
@Text VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS

BEGIN
DECLARE @Box TABLE (i TINYINT, v TINYINT)

INSERT @Box
(
i,
v
)
SELECT i,
v
FROM dbo.fnInitRc4(@Pwd)

DECLARE @Index SMALLINT,
@i SMALLINT,
@j SMALLINT,
@t TINYINT,
@k SMALLINT,
@CipherBy TINYINT,
@Cipher VARCHAR(8000)

SELECT @Index = 1,
@i = 0,
@j = 0,
@Cipher = ''

WHILE @Index <= DATALENGTH(@Text)
BEGIN
SELECT @i = (@i + 1) % 256

SELECT @j = (@j + b.v) % 256
FROM @Box b
WHERE b.i = @i

SELECT @t = v
FROM @Box
WHERE i = @i

UPDATE b
SET b.v = (SELECT w.v FROM @Box w WHERE w.i = @j)
FROM @Box b
WHERE b.i = @i

UPDATE @Box
SET v = @t
WHERE i = @j

SELECT @k = v
FROM @Box
WHERE i = @i

SELECT @k = (@k + v) % 256
FROM @Box
WHERE i = @j

SELECT @k = v
FROM @Box
WHERE i = @k

SELECT @CipherBy = ASCII(SUBSTRING(@Text, @Index, 1)) ^ @k,
@Cipher = @Cipher + CHAR(@CipherBy)

SELECT @Index = @Index +1
END

RETURN @Cipher
END

GO

Thanks for any help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-11 : 10:32:30

Some thing again!
Use same password and put the encrypted text as parameter and you will get the original text back.

Hence the name fnEncDecRc4
Encryption
Decryption

I have told you this before.

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-11 : 10:37:29

I think you are doing it wrong avmreddy17.

To encrypt SSN:

SELECT dbo.fnEncDecRc4('ty6578dmp1203', '145678908')

To decrypt it back:

Select dbo.fnEncDecRc4('ty6578dmp1203', 'ò/�—¶¡ñ—')

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

avmreddy17
Posting Yak Master

180 Posts

Posted - 2007-10-11 : 10:42:19

Thanks Guys for all the help

Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources