Convert from hex stored proc question SQL 2005

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/

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

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.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources