Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Convert from hex stored proc question SQL 2005

Author  Topic 

pmccullo123
Starting Member

2 Posts

Posted - 2014-11-20 : 18:54:04
Can someone tell me how to execute this stored procedure to decode a varchar hexadecimal string? My SQL syntax stills have faded with the sands of time... I want to do a select * from the users table and decode the Password field (but not update) in the process.

CREATE PROCEDURE spPasswordDecode (@hex varchar (100), @passwordtext varchar (100) output)
AS

declare @n int,
@len int,
@str nvarchar(500),
@output varchar(100)

set @n = 0
set @len = len(@hex)/2

WHILE @n < @len
SELECT @str = coalesce(@str,N'SELECT @output =') + N'Char(0x' + Substring(@hex, @n * 2 + 1, 2) + ') + ', @n = @n + 1

set @str = left(@str, len(@str)-2)

--select (@str)
exec sp_executesql @str,N'@output varchar(100) output',@output output
select @PasswordText = @output


GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-20 : 18:57:07
declare @passwordtext varchar(100)

exec spPasswordDecode @hex='somestring',@passwordtext=@passwordtext output

select @passwordtext

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-11-20 : 19:18:06
My only addendum is that the @hex string looks to represent a string of hex characters (0-9 and A-F) and that there must be an even number of characters passed in.[CODE]declare
@vhex varchar (100) = '3120416263',
@vpasswordtext varchar (100) = ''

exec spPasswordDecode
@hex = @vhex,
@passwordtext = @vpasswordtext OUTPUT

select @vpasswordtext[/CODE]The sproc takes a string of hex characters and returns the equivalent ASCII character string.



No amount of belief makes something a fact. -James Randi
Go to Top of Page

pmccullo123
Starting Member

2 Posts

Posted - 2014-11-20 : 19:30:19
You are a goddess...this much is certain. Married? Your equation is functional.

One last question though... so I have a table called users that contain fields login and password . How do I execute a select statement on that table which decodes and shows me the unencoded password. Don't want to update the table...just show the decoded password.

Thanks for all your help.
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-11-21 : 18:54:53
I'd convert the stored procedure into a scalar function. Once you have that, return the output of the function in a SELECT statement, a la:
select dbo.fnPasswordDecode(mt.hex) PlainText
from MyTable mt
HTH



No amount of belief makes something a fact. -James Randi
Go to Top of Page
   

- Advertisement -