encrypt a value

[timscronin]

Asking this for a friend whose internet died

How do you insert an encrypted value into a field in SQL Server? What
I'm looking for is something that would do an MD5 hash or something of a
value when you insert it, so you can't just look at what it is when you
do a select?

 

[JayKusch]

This might work for you. It is a function. Create the function and then test it on a table against a field named password set as a varchar. Of course put a couple of passwords in the table to see how it works and looks.

CREATE  FUNCTION fnc_Encrypt_Password 
(@Src VarChar (128), 
 @Key VarChar (128))

RETURNS VarChar (128)

WITH ENCRYPTION

	AS 

  BEGIN

	DECLARE	@EncryptedValue	 VarChar (128), 
			@CharPos SmallInt,
			@SrcPos	 SmallInt

    	SELECT	@EncryptedValue	 = '', 
			@CharPos = 1, 
			@SrcPos	 = 1

    	WHILE (@CharPos <= DATALENGTH(@Src))

	  BEGIN

		SELECT	@EncryptedValue	= @EncryptedValue + Char(ASCII(SUBSTRING(@Src, @CharPos, 1) ) ^ ASCII(SUBSTRING(@Key, @SrcPos, 1))), @CharPos = @CharPos + 1, @SrcPos =  @SrcPos	+ 1

	IF	@SrcPos > LEN(@Key)

			SELECT @SrcPos = 1

        END

	RETURN @EncryptedValue

  END

Then run a test ... something like:

SELECT 
Encrypted=dbo.fnc_Encrypt_Password (password, 'password'), 
Decrypted=dbo.fnc_Encrypt(dbo.fnc_Encrypt_Password(password, 'password'), 'password') 
FROM MyTable

Thanks

J. Kusch