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

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

---

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"

---

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"

---

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

---