Free Encryption
-
I'll take a look at it and post something for you tonight. Thanks!
-
Thanks, Mike. I have also noticed that it could be somehow related to the lenght of the message. If I make my testing message the same lenght as your sample, it works fine (both key and encrypted message change). Hopefully this could help you troubleshoot the problem.
Igor
-
UPDATE POSTED
I've found the issue, and I'll see if I can get the guys here to post an update to the code shortly. Basically Blowfish deals with text in multiples of 8 bytes. This is fairly normal, all modern algorithms encrypt in blocks of 8 bytes (Blowfish/DES, etc.) or blocks of 16 bytes (Rijndael/AES, etc.)
The problem is that I forgot to add the code into the XP itself to pad out the input string to 8 bytes, so Blowfish isn't encrypting it properly. As a temporary work-around, you can create a "pad" function to pad your string with spaces to make it a multiple of 8:
CREATE FUNCTION dbo.udf_PadZeroChar8(@string VARCHAR(2000))
RETURNS VARCHAR(2000)
AS
BEGIN
SET @string = @string + SUBSTRING(CHAR(0) + CHAR(0) + CHAR(0) + CHAR(0) + CHAR(0) + CHAR(0) + CHAR(0), 1, 8 - LEN(@string) % 8)
RETURN @string
END
CREATE FUNCTION dbo.udf_StripZeroChar(@string VARCHAR(2000))
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @i INT
SET @i = LEN(@string)
WHILE @i > 0 AND SUBSTRING(@string, @i, 1) = CHAR(0)
SET @i = @i - 1
IF @i > 0
SET @string = SUBSTRING(@string, 1, @i)
RETURN @string
END
udf_PadZeroChar8 will right-pad your plaintext string with CHAR(0) out to the next highest multiple of 8 characters. udf_StripZeroChar will remove right-padded CHAR(0) characters from your string after a decryption. To use your example, you can use these like this:
DECLARE @plain_text VARCHAR(500) -- Our plain text
DECLARE @enc_text VARCHAR(500) -- Our encrypted text
DECLARE @dec_text VARCHAR(500) -- Our decrypted text
DECLARE @key VARCHAR(500) -- Our encryption key
--SET @plain_text = 'Now is the time for all good men to come to the aid of their countrymen.'
SET @plain_text='This is my test of encrypted message.'
EXEC master..xp_generatekey 448, @key OUTPUT
SET @plain_text = dbo.udf_PadZeroChar8(@plain_text)
EXEC master..xp_blowfishencrypt @plain_text, @key, @enc_text OUTPUT
EXEC master..xp_blowfishdecrypt @enc_text, @key, @dec_text OUTPUT
SET @dec_text = dbo.udf_StripZeroChar(@dec_text)
SET @plain_text = dbo.udf_StripZeroChar(@plain_text)
PRINT 'Test 3: Longer String/Longer Key'
PRINT '--Plain Text: ''' + @plain_text + ''''
PRINT '--Key: ''' + @key + ''''
PRINT '--Encrypted Text: ''' + @enc_text + ''''
PRINT '--Decrypted Text: ''' + @dec_text + ''''
IF @plain_text = @dec_text
PRINT '--Result: PLAIN TEXT IS EQUAL TO DECRYPTED TEXT'
ELSE
PRINT '--Result: PLAIN TEXT IS NOT EQUAL TO DECRYPTED TEXT'
PRINT '-----------------------------------------'
Like I said, this is a temporary work-around until I can get the update posted. I apologize for the inconvenience.
-
Thanks, Mike. Appreciate your prompt reply.
Igor
-
Well Mike,
I found out that I have the OpenDS60.dll in the SQL Binn Directory, but MSVCRT71.dll does not exist on ANY of my servers. Know where I can get a copy?
Chris
---===<<< Edit >>>===---
I found the following article from Microsoft. MSVCRT71.dll is no longer installed with the system files and that VC++ .net packages need to re-distribute this dll in an application directory. But instead of me paraphrasing the whole article. Why don't I just give you a Link to it instead...
-
Hmm. It's in the \WINDOWS\Microsoft.NET\Framework\v1.1.4322 directory on all my computers. I believe it's installed with the .NET Framework.
-
Is there any reason that your cool stuff won't work with TEXT datatypes? I figured I'd ask before I spent any time trying it. It turns out that I have a few TEXT columns that would benefit from this - and last time I looked (April CTP) the encryption built into SQL2005 only supports encryption of strings of 8K or less in length.
-
Not sure, I didn't try it with TEXT datatype. The only potential problem I can see is if SQL Server passes TEXT parameters differently from other parameters (or if it won't allow them in XP's.) That might be affected by whether or not you store your TEXT 'in row' as well.
I'm really not sure how SQL Server passes TEXT parameters though. I didn't put any restrictions on the type of Parameters that can be passed to the routines though (no type checking), so you might give it a try on a test table and see if it works. I've tested them with CHAR/VARCHAR (and the AES versions with BINARY and VARBINARY as well), but hadn't tried it with TEXT.
Thanks.
-
I'll give it a try next week - thanks.
-
Just ran into the first issue - you can't declare a TEXT local variable. I'll keep looking into it and see what I can come up with, but TEXT type's built-in restrictions appear to be the main limiting factor here.
I'll let you know if I come up with some other ideas.
Thanks.
-
Thanks Mike,
This is one of the best articles I have met on the net recently.
Frank
-
Thanks for the feedback Frank. I hope you find it useful!
-
I try to run
DECLARE @out VARCHAR(5000)
execute xp_rot13 'test', @out OUTPUT
PRINT @out
and get error
Stored function 'xp_rot13' in the library 'xp_rot13.dll' generated an access violation
Also for some reason I can't register any of those DLL
I am running Windows Server 2000
-
I just tried it over here and can't reproduce the error. The result I got from your test string was 'grfg'. The DLLs don't need to be 'registered' per se, just drop them in the MSSQL\Binn directory. You might also need to modify the install script to point to the full path for the DLLs (see the INSTALL-NOTES.RTF file for more specific directions and for other installation notes that might apply).
-
Great package. Thanks!
I followed the steps: copied the dlls, and ran the add_xp.sql (which finished successfully).
But when I try to call the blowfish encrytion, I get the error:
ODBC: Msg 0, Level 16, State 1
Cannot load the DLL xp_generatekey.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
I verified that the DLL was there, and it is. My simple example I was trying to run:
DECLARE @key VARCHAR(500)
EXEC master.dbo.xp_generatekey 128, @key OUTPUT
insert into Customers
(firstName, lastName, address1, address2, city, state, postal_code,
ssn,
credit_card_number,
expiration_date)
values
('John','Doe','123 Main St.','','Jax','FL', '32224',
master.dbo.udf_blowfishencrypt('123-45-6789', @key),
master.dbo.udf_blowfishencrypt('1232-3244-1312-1432', @key),
master.dbo.udf_blowfishencrypt('10/07', @key)
 
Help!? Thank you
Viewing 15 posts - 16 through 30 (of 153 total)
You must be logged in to reply to this topic. Login to reply