SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 RC4 Encryption - Decryption
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 07/28/2008 :  05:20:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yes, I can!

http://www.google.com

Or more exactly
http://www.google.co.uk/search?hl=en&safe=off&q=%2B%22.net%22+implemenation+%2B%22RC4%22+algorithm+%2B%22source+code%22&meta=



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Ahmad Farid
Starting Member

7 Posts

Posted - 07/28/2008 :  08:19:00  Show Profile  Reply with Quote
Thank you Peso for your interest and those fast replies. But you know what, I was able to find 2 codes from the internet till now. First of all what I want to do is to do the encryption using a .net code library and the decryption using the SQL stored procedure. The first one after encrypting and decrypting it had about 2 or three characters difference. The second one which was on codeproject, I had to do the encryption on .net then decryption of the SQL then the encryption then decryption again, i.e. I did it twice which is not efficient because like that I had to go to the database twice and during encryption even so it's not good. I had been trying to find the encryption somewhere else but was not able to find except those two times.
Sorry for disturbing you all that. Thank you very much again :)
Go to Top of Page

bobbydharrell
Starting Member

1 Posts

Posted - 08/14/2008 :  13:07:07  Show Profile  Reply with Quote
Is there a way I can speed these two function up ... the work great but my boss says 1 sec to long .. and I tend to agree when it has to go through a 177,000 records :-) thanks in advance.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 08/14/2008 :  13:21:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You can prestore the @Box records in a normal table and make a unique clustered index on i column.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 08/14/2008 :  13:23:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
And change the other function to point at the new "box" table.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

willieb
Starting Member

5 Posts

Posted - 11/13/2008 :  17:15:12  Show Profile  Reply with Quote
So, I have a question similar to one that has been asked, but I think it is still different enough to warrant asking. I am encoding data for one field in a db BEFORE posting it to SQL Server, no problem. However, I want to decrypt it as I pull it out, so I used your two functions and called it like this

select master.dbo.fnEncDecRc4('plithy',ccnumber)
from insidesales_pmtdetail

which gave me this
1kÛTop+Eõ¨¼a‰—‘Å™ ~"£à¶%…"¾Ï÷`.ï»*C¯Ûr'Ïušœ#H

Not quite the test number that I had encrypted. Any thoughts on why this wouldn't decrypt the field it is pulling out of SQL Server? Oh, I encrypted and then posted as binary data in the stored procedure (adBinary), storing it in a binary data type.

Thanks for any help you can give me.

wb
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 11/14/2008 :  01:51:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Works for me
DECLARE	@Text VARCHAR(100),
	@vc2bin VARBINARY(100),
	@vc2vc VARCHAR(100),
	@bin2vc VARCHAR(100)

SELECT	@Text = 'Peso almost got 20K posts',
	@vc2bin = CAST(dbo.fnEncDecRc4('SQLTeam', @Text) AS VARBINARY(100)), 
	@vc2vc = dbo.fnEncDecRc4('SQLTeam', @Text)

SELECT	@Text AS Original,
	@vc2bin AS vc2bin,
	@vc2vc AS vc2vc

SELECT	@bin2vc = dbo.fnEncDecRc4('SQLTeam', @vc2bin), 
	@vc2vc = dbo.fnEncDecRc4('SQLTeam', @vc2vc)

SELECT	@Text AS Original,
	@bin2vc AS bin2vc, 
	@vc2vc AS vc2vc



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

willieb
Starting Member

5 Posts

Posted - 11/14/2008 :  10:20:55  Show Profile  Reply with Quote
Sorry I forgot to post a follow-up yesterday. Just before I left I figured out that I could not create UDFs on that particular database, so I created them on the Master database and everything works great. So, thank you for the reply and thank you very much for the code!

wb
Go to Top of Page

avmreddy17
Posting Yak Master

180 Posts

Posted - 03/25/2009 :  10:15:07  Show Profile  Reply with Quote
Quick Clarification..

How does the function know when to decrypt and when to encrypt as we
are using the same function?

declare @test varchar(50)
select @test = 'password'
select fnInitRc4(@test, '2345')
print @test = ÍÆ

declare @test varchar(50)
select @test = 'password'
select ufn_EncDecRc4(@test, 'ÍÆ ;')

Thanks
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 03/25/2009 :  10:39:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You can think of it as Alice In Wonderland.

The cleartext message and the encrypted message are the same, seen from different side of a glass mirror.
It doesn't matter which side of the mirror you are, or see the message, the function transfer the characters through the mirror.
And ends up on the other side.

Which side which is the cleartext side, and which side is the encrypted side is up to you to decide.
The function doesn't know, and doesn't care either.


E 12°55'05.63"
N 56°04'39.26"

Edited by - SwePeso on 03/25/2009 10:39:50
Go to Top of Page

avmreddy17
Posting Yak Master

180 Posts

Posted - 03/25/2009 :  10:51:52  Show Profile  Reply with Quote
Thanks Peso..

Its a magic for me....

I am not very good very good in Programming.
Can you explain a little more detail..

I want to use this function but just want to understand a little more as to how its able to know when to encrypt/Decrypt

Thanks Again
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 03/25/2009 :  10:58:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The function doesn't know if to encrypt or decrypt.
We humans call it that, because we have decided which side of mirror is human readable.

The function only "scrambles" the characters according to a specific algorithm, in this case RC4.
And when done twice, all letter are back to original, it you use the same password key both times.


E 12°55'05.63"
N 56°04'39.26"

Edited by - SwePeso on 03/25/2009 11:00:44
Go to Top of Page

lbs2009
Starting Member

India
3 Posts

Posted - 11/25/2009 :  04:09:43  Show Profile  Reply with Quote
Peter,

I am using thease sql functins to encrypt/decrypt SSNumber based on RC4 algotithm but a major issue is very slow speed of sql query when i applied it to my stored procedure which has to search records based on search criteria. If you have the optimized RC4 sql to overcome the sql speed as you have said in your comments, please reply with optimized RC4 EncDec.
quote:
Originally posted by Peso

Thanks.

I kept it in two parts, because then the EncDec can be rewritten to encrypt/decrypt a column with same password a lot faster, without having to call Init for each row.

As of now, any row could have different password.


Peter Larsson
Helsingborg, Sweden



lbsingh
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 11/25/2009 :  06:17:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The optimization is that you store the values from function "fnInitRc4" in a table, put clustered index onto it, and use that table in the function, if you are using same password everywhere.
Otherwise, there are SQLCLR functions available for free downloads. Use Google to find them.




N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

lbs2009
Starting Member

India
3 Posts

Posted - 11/25/2009 :  06:36:27  Show Profile  Reply with Quote
Thanks Peso,
I am not so good in sql. Please help me to speed up this RC4 algorithm UDFs (fnInitRc4, fnEncDecRc4).
can you explain the first option to update fnInitRc4 UDF or send me the piece of sql code?

Thanks again for giving me your valuable time.

quote:
Originally posted by Peso

The optimization is that you store the values from function "fnInitRc4" in a table, put clustered index onto it, and use that table in the function, if you are using same password everywhere.
Otherwise, there are SQLCLR functions available for free downloads. Use Google to find them.




N 56°04'39.26"
E 12°55'05.63"




lbsingh
Go to Top of Page

lbs2009
Starting Member

India
3 Posts

Posted - 11/25/2009 :  07:46:56  Show Profile  Reply with Quote
Thanks Peso for your fast reply,

i am using sql 2000 and SQLCLR integration is not availbale in sql server 2000. So there is first option left for me to improve the performance of fnInitRc4 and fnEncDecRC4 UDF.
Please explain me in detail how to modify the thease UDFs to speed up RC4 encryption/decryption.


quote:
Originally posted by Peso

The optimization is that you store the values from function "fnInitRc4" in a table, put clustered index onto it, and use that table in the function, if you are using same password everywhere.
Otherwise, there are SQLCLR functions available for free downloads. Use Google to find them.




N 56°04'39.26"
E 12°55'05.63"




lbsingh
Go to Top of Page

mudassar001
Starting Member

Pakistan
1 Posts

Posted - 10/31/2010 :  08:29:08  Show Profile  Reply with Quote
quote:
Originally posted by Peso

This function is used to initialize the seed for the RC4 algorithm
CREATE FUNCTION dbo.fnInitRc4
(
	@Pwd VARCHAR(256)
)
RETURNS @Box TABLE (i TINYINT, v TINYINT)
AS

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

	DECLARE	@Index SMALLINT,
		@PwdLen TINYINT

	SELECT	@Index = 0,
		@PwdLen = LEN(@Pwd)

	WHILE @Index <= 255
		BEGIN
			INSERT	@Key
				(
					i,
					v
				)
			VALUES	(
					@Index,
					 ASCII(SUBSTRING(@Pwd, @Index % @PwdLen + 1, 1))
				)

			INSERT	@Box
				(
					i,
					v
				)
			VALUES	(
					@Index,
					@Index
				)

			SELECT	@Index = @Index + 1
		END


	DECLARE	@t TINYINT,
		@b SMALLINT

	SELECT	@Index = 0,
		@b = 0

	WHILE @Index <= 255
		BEGIN
			SELECT		@b = (@b + b.v + k.v) % 256
			FROM		@Box AS b
			INNER JOIN	@Key AS k ON k.i = b.i
			WHERE		b.i = @Index

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

			UPDATE	b1
			SET	b1.v = (SELECT b2.v FROM @Box b2 WHERE b2.i = @b)
			FROM	@Box b1
			WHERE	b1.i = @Index

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

			SELECT	@Index = @Index + 1
		END

	RETURN
END
ANd this function does the encrypt/decrypt part
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


Peter Larsson
Helsingborg, Sweden



Dear Peter
kindly tell how and where this function I have to use
Thanks.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 10/31/2010 :  08:33:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You don't HAVE to use it. It's a business decision and if you NEED the function ,put it in your database for later use.



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

kishan_kant
Starting Member

India
1 Posts

Posted - 11/03/2012 :  10:13:07  Show Profile  Reply with Quote
Dear Please show me how can we encrypt and decrypt
'Kumar Gaurav', with @text='password'

reply Urgently
--Kumar Gaurav
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.8 seconds. Powered By: Snitz Forums 2000