SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Express Hexadecimal binary as char
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/26/2010 :  09:49:17  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/26/2010 :  09:57:41  Show Profile  Reply with Quote
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
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/26/2010 :  10:00:09  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/26/2010 :  10:05:10  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/26/2010 :  10:07:35  Show Profile  Reply with Quote
fn_sqlvarbasetostr ... undocumented (Why? Goodness knoly knows, what sort of "threat" is that, compared to its usefulness)
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/26/2010 :  10:17:02  Show Profile  Reply with Quote
Madhivanan will be along in a minute ... "Format in your front end application"
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/26/2010 :  10:26:58  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/26/2010 :  10:31:19  Show Profile  Reply with Quote
"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"
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/26/2010 :  10:56:49  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 01/27/2010 :  02:49:28  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/27/2010 :  03:29:08  Show Profile  Reply with Quote
"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?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30113 Posts

Posted - 01/27/2010 :  03:33:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30113 Posts

Posted - 01/27/2010 :  03:39:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 01/27/2010 :  03:46:49  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
<<
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/27/2010 :  04:36:14  Show Profile  Reply with Quote
Yeah, it was a rhetorical question Madhi
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/27/2010 :  05:12:58  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/27/2010 :  05:42:39  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000