checksums and unicode data

Question

Post reply

checksums and unicode data

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

just looking for confirmation...i used my google-fu and found info and examples for using hashbytes with SHA1, ie

select HASHBYTES('SHA1','My Plain Text')

--results

0x6D99DDF6FE7A32547B6766E0BF88B1F50835F0FF

everything i read says that this is a one way operation, so you can use it to generate a unique value like checksum, but you cannot unhash this back to it's original value, right?

so this is not encryption, but rather a tool to generate a unique identifier, correct?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 2

Paul White (8/10/2009)
RBarryYoung (8/10/2009)
To clarify: HashBytes w/ SHA1 is nigh-unbreakable.
Hey Barry,
On the other hand, it is relatively slow, only works on strings, and returns varbinary(8000).
If the task is to detect changes in a row of data, would you use HashBytes (SHA1) alone?
Paul

In Lowell's example, it also turned a 13 byte string into a 20 byte Varbinary so it's expensive, too.

But, I believe that Barry's point was simply that it is unbreakable... not like CHECKSUM at all. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 3

Jeff Moden (8/10/2009)
In Lowell's example, it also turned a 13 byte string into a 20 byte Varbinary so it's expensive, too. But, I believe that Barry's point was simply that it is unbreakable... not like CHECKSUM at all. 🙂

So...SHA1 always returns 160 bits (20 bytes) regardless of the input. HashBytes also appears to be limited to 8000 bytes of input - 8000 ANSI characters or 4000 Unicode. Finally, it doesn't appear unbreakable either: Wikipedia Link.

I mentioned HashBytes way back - now I'm just pointing out some of the reasons that I have yet to use it in a real production system.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Paul White (8/10/2009)
RBarryYoung (8/10/2009)
To clarify: HashBytes w/ SHA1 is nigh-unbreakable.
Hey Barry,
On the other hand, it is relatively slow, only works on strings, and returns varbinary(8000).
If the task is to detect changes in a row of data, would you use HashBytes (SHA1) alone?
Paul

Despite the datatype, I think that it actually only returns 19 bytes. And since VARBINARY is a string and *everything* converts to varbinary very easily, thats not that big a problem (nulls are a bigger problem). So yeah, I *might* use it in situations were I was doing remote comparisons through linked servers and just recording the hash every day for later comparison. The problem with the field by field comparison is that you have to have the entire previous record around to do it.

Though I think that I usually used MD5 in the past.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Paul White (8/10/2009)
Jeff Moden (8/10/2009)
In Lowell's example, it also turned a 13 byte string into a 20 byte Varbinary so it's expensive, too. But, I believe that Barry's point was simply that it is unbreakable... not like CHECKSUM at all. 🙂
So...SHA1 always returns 160 bits (20 bytes) regardless of the input. HashBytes also appears to be limited to 8000 bytes of input - 8000 ANSI characters or 4000 Unicode. Finally, it doesn't appear unbreakable either: Wikipedia Link.
I mentioned HashBytes way back - now I'm just pointing out some of the reasons that I have yet to use it in a real production system.
Paul

Hmm, I though that I counted 19 bytes... Anyway, qualifying myself again :-), "nigh-unbreakable" was referring to its use as a signature in detecting random data changes, not as a security device.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]