December 20, 2011 at 9:33 am
How can I convert a string or chararcter data into Binary/Hash
and then convert it back to String or Character.
I tried two functions but doesnt work for me.
SELECT HashBytes('MD5', 'email@dot.com')
DECLARE @binvalue varbinary(255)
SELECT @binvalue = HashBytes('MD5', 'email@dot.com')
SELECT @binvalue
----
SELECT SUBSTRING(master.dbo.fn_varbintohexstr(@binvalue), 3, 32)
----
SELECT master.dbo.fn_varbintohexsubstring(0, @binvalue, 1, 0)
December 20, 2011 at 9:44 am
Are you looking for something like this:
DECLARE @TestString VARCHAR(100) = 'Test String' ;
SELECT @TestString AS [Raw],
CONVERT(VARBINARY(100), @TestString) AS ToBinary,
CONVERT(VARCHAR(100), CONVERT(VARBINARY(100), @TestString)) AS StringFromBinaryFromString ;
Kind of the whole point of hashing to MD5 is that you really can't convert it back to a readable string. That would pretty much defeat the purpose of a secure hash in the first place.
If what you're looking for is encryption/decryption, not just text-to-binary-to-text, what kind of use are you looking at for that? Are you looking to store encrypted data and be able to query it in a decrypted format, like with TDE? Are you looking to transmit (e-mail or FTP maybe) encrypted text and decrypt it at the receipt point? Something else?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 20, 2011 at 10:02 am
I am looking to send BINARY data instead of XML in the Service Broker to reduce message payload.
I was thinking to HASH the string but then couldnt figure out how will I read it back on the
recieving side. I think BINARY should also work in my case. its not a higly encrypted or secured
environment anyways. what do you reckon ?
December 20, 2011 at 10:28 am
Hashing is a one way operation. You cannot get the original value from the hash value.
December 20, 2011 at 11:03 am
Then in what scenarios would we need to decrypt data by HASHING it if the client application cant read it ?
December 20, 2011 at 11:33 am
I'm not clear if you want to encrypt/decrypt a string or just convert it to binary. So, here is s way to to Cast to binary and back again:DECLARE @string VARCHAR(MAX)
DECLARE @bin VARBINARY(MAX)
SET @string = 'My Test String'
SELECT @string
SET @bin = CAST(@string AS VARBINARY(MAX))
SELECT @bin
SELECT CONVERT(VARCHAR(MAX), @bin)
And here is a way to encrypt and decrypt a string using a "pass phrase":DECLARE @bin VARBINARY(MAX)
SET @bin = EncryptByPassPhrase('encryption_key', N'My Test String', NULL, NULL)
SELECT @bin
SELECT CAST(DecryptByPassPhrase('encryption_key', @bin, NULL, NULL) AS NVARCHAR(MAX))
EDIT: I forgot to mention that Encrypt/DecryptByPassPhrase can only do a maximum of 8,000 bytes, so that may or may not work for you.
December 20, 2011 at 11:41 am
WangcChiKaBastar (12/20/2011)
I am looking to send BINARY data instead of XML in the Service Broker to reduce message payload.I was thinking to HASH the string but then couldnt figure out how will I read it back on the
recieving side. I think BINARY should also work in my case. its not a higly encrypted or secured
environment anyways. what do you reckon ?
You can certainly convert to varbinary from XML, but it's more likely to increase data size and bandwidth needs than the other way around.
You'd do better to use some sort of lossless compression algorithm for that.
But keep in mind, that will decrease data size, but increase CPU load, because you'll have to compress and decompress the data.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 20, 2011 at 11:46 am
WangcChiKaBastar (12/20/2011)
Then in what scenarios would we need to decrypt data by HASHING it if the client application cant read it ?
Non-reversible, reproducable hashing (like the SQL Server use of MD5) is mainly used for storing things like passwords.
If you store a hash of a password, and someone hacks the table it's stored in, they don't have the password, just the hash. But when a user types in the password, you compare the hash of what they typed in to the hash you have stored, and if those match, they typed in the right password. That prevents you from being able to tell the user what their password is (hence the use of password resets instead of revealing the password back to the user). Still has weaknesses, but much less than storing passwords in plaintext.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 21, 2011 at 8:26 am
Thanks, I will look into converting to Binary and compression.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply