| Author |
 Topic  |
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3451 Posts |
 Posted - 01/26/2010 : 09:49:17
|
Hi all,
I want to do something simple (hopefully) but can't find out how -- also not sure how to look either. Having a hard time expressing what I want in keywords.
This is what I need:
I need to express the binary result of an SHA1 HASH brought back from the HASHBYTES function as a literal string for each hexadecimal value.
For instance I need to turn the output of this:
SELECT(HASHBYTES('SHA1', 'password')
(which is 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8) into the string
'5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8'
You can't do this with a simple CAST.
I'm sure there is an easy way but I'm having a hard time searching for it.
Hope this makes sense.
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3451 Posts |
Posted - 01/26/2010 : 09:56:21
|
Addendum -- using CONVERT to CHAR with a style of 2 works fine on 2008. It doesn't work the same way on 2005 (I get a character rpresentation).
For instance taken from books online for 2008
SELECT CONVERT(char(8), 0x4E616d65, 2) AS 'Style 1, binary to character'
Gives me '4E616d65' which is exactly what I want.
But on 2005 I get: 'Name'
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
Kristen
Test
United Kingdom
22859 Posts |
Posted - 01/26/2010 : 09:57:41
|
There's a BINtoHEX function or somesuch, isn't there?
EDIT:
I thought there was a way back in SQL 2000, maybe not (but this is a workaround)
http://support.microsoft.com/kb/104829 |
Edited by - Kristen on 01/26/2010 10:05:03 |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3451 Posts |
Posted - 01/26/2010 : 10:00:09
|
Think I've found it!
SELECT sys.fn_sqlvarbasetostr(HASHBYTES('SHA1', 'password'))
I was a bit floored when the CONVERT behaviour I was using on 2008 suddenly wasn't working for me in a different environment!
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
Edited by - Transact Charlie on 01/26/2010 10:00:54 |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3451 Posts |
Posted - 01/26/2010 : 10:05:10
|
hmm. I can't find the documentation for this function in my BOL
Does anyone know if this function is deprecated?
Generally I find the documentation on SQL SEVER to be very, very good but there are odd times where I just can't find the information I am looking for.
Cheers for the reply Kristen.
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Kristen
Test
United Kingdom
22859 Posts |
Posted - 01/26/2010 : 10:07:35
|
| fn_sqlvarbasetostr ... undocumented (Why? Goodness knoly knows, what sort of "threat" is that, compared to its usefulness) |
|
|
|
Kristen
Test
United Kingdom
22859 Posts |
Posted - 01/26/2010 : 10:17:02
|
Madhivanan will be along in a minute ... "Format in your front end application"  |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3451 Posts |
Posted - 01/26/2010 : 10:26:58
|
ah -- but it isn't. I'm trying to replicate the way that an application is storing hashes for passwords. I've got a bulk update to do and the application still has to be able to understand the hashes afterwards.
OFC the application SHOULD be storing the hashes as a binary value rather than as string but that's another matter entirely!
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
Edited by - Transact Charlie on 01/26/2010 10:28:00 |
|
|
|
Kristen
Test
United Kingdom
22859 Posts |
Posted - 01/26/2010 : 10:31:19
|
"the application SHOULD be storing the hashes as a binary value rather than as string"
Ah ... I get it now. One of the DEVs probably tossed a coin and it came up "Strings"  |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3451 Posts |
Posted - 01/26/2010 : 10:56:49
|
Sadly, they do that a lot! Though thank the SQL gods that they at least treat dates as dates.
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
madhivanan
Premature Yak Congratulator
India
22864 Posts |
Posted - 01/27/2010 : 02:49:28
|
quote:
Originally posted by Transact Charlie
Hi all,
I want to do something simple (hopefully) but can't find out how -- also not sure how to look either. Having a hard time expressing what I want in keywords.
This is what I need:
I need to express the binary result of an SHA1 HASH brought back from the HASHBYTES function as a literal string for each hexadecimal value.
For instance I need to turn the output of this:
SELECT(HASHBYTES('SHA1', 'password')
(which is 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8) into the string
'5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8'
You can't do this with a simple CAST.
I'm sure there is an easy way but I'm having a hard time searching for it.
Hope this makes sense.
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Am I missing something?
SELECT substring(cast(sys.fn_sqlvarbasetostr(HASHBYTES('SHA1', 'password')) as varchar(1000)),3,100)
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Kristen
Test
United Kingdom
22859 Posts |
Posted - 01/27/2010 : 03:29:08
|
"Am I missing something?"
fn_sqlvarbasetostr is undocumented? (in both SQL 2005 and SQL 2008 ... which is worrying because it clearly wasn't just a DOC omission in SQL 2005.
Why do MS add an innocuous feature like this and make it unsupported and prone to dropping from future versions? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 01/27/2010 : 03:33:38
|
Why not use XML?-- Prepare value
DECLARE @bin VARBINARY(MAX)
SET @bin = 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8
-- Display the results
SELECT @bin AS OriginalValue,
CAST('' AS XML).value('xs:hexBinary(sql:variable("@bin"))', 'VARCHAR(MAX)') AS ConvertedString
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
|
|
madhivanan
Premature Yak Congratulator
India
22864 Posts |
Posted - 01/27/2010 : 03:46:49
|
<<
Why do MS add an innocuous feature like this and make it unsupported and prone to dropping from future versions?
>>
Only they can answer
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Kristen
Test
United Kingdom
22859 Posts |
Posted - 01/27/2010 : 04:36:14
|
| Yeah, it was a rhetorical question Madhi |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3451 Posts |
Posted - 01/27/2010 : 05:12:58
|
quote:
Originally posted by Peso
Blog post here http://weblogs.sqlteam.com/peterl/archive/2010/01/27/Convert-binary-value-to-string-value.aspx
N 56°04'39.26"
E 12°55'05.63"
Well,
Obviously it works and it's very clever.....
But it seems a long way to go to replicate an undocumented function call in 2005.
Also it is a little harder to understand for code review purposes by someone else. (OFC as without a little internet searching you can't find and documentation on the function then...)
As you don't need to do either of these in 2008 and up is there any possibility of a 2005 service pack removing the function?
EDIT: Guess the big + for the XML is that it is future proofed. Code written that uses this in 2005 will still *work* in 2008 and future versions albeit in a completely ass backward way on those platforms. Guess I'll use the XML if I ever need to do this again.
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
Edited by - Transact Charlie on 01/27/2010 05:18:28 |
|
|
|
Kristen
Test
United Kingdom
22859 Posts |
Posted - 01/27/2010 : 05:42:39
|
There are Sprocs out there that do BIN to HEX conversion. Would that do you? If so you would have a future-proof solution.
sp_helptext 'fn_sqlvarbasetostr'
will show you the source ... adopt it as your own UDF for future-proofing? Put a note in your diary to compare it against every future service pack and new version to make sure you adopt any bug fixes that the Vendor introduces  |
|
|
| |
Topic |
|
Express Hexadecimal binary as char