Ouputing varbinary data

[AZDeveloper]

Hello CF fellows,

When outputing varbinary data directly from a column of datatype "varbinary"
in MS SQL Server, I get the error "ByteArray objects cannot be converted to
strings" in ColdFusion MX. I don't get this error in earlier version of CF,
e.g. CF 4.0...

Does anybody know how I can fix this?



For example, if I have a column called "User_ID" (datatype=varbinary), when I
output it as follows, I get the error.

<cfoutput>
GetVarbinary.User_ID
</cfoutput>

[mxstu]

You might want to try the suggestions in [url]http://www.macromedia.com/cfusion/webforums/forum/messageview.cfm?catid=6&threadid=976780[/url]. A) cast as varchar in query or B) use java.lang.String

[AZDeveloper]

Hi mxstu,

First of all, I'd like to thank you very much in advance!

Here's the SQL code that I use to encrypt some username and store it in a
column of datatype "varbinary" in MS SQL Server.

What I need help on is: I'd like to decrypt this varbinary Username_Encrypted
= ''0xAAC1D3CFCEA8' back to "JasonH".

I've tried using your CAST AS VARCHAR approach but I don't really know how to
do it. Please help!

Thank you!




DECLARE @position int, @string varchar(16), @binary binary(1), @varbinary
varbinary(32)

SET @position = 1

SET @string = 'JasonH'

SET @varbinary = 0x


WHILE @position <= DATALENGTH(@string)

BEGIN

SET @binary = CAST(ASCII(SUBSTRING(@string, @position, 1)) + 96 AS
binary(1))

SET @varbinary = @varbinary + @binary

SET @position = @position + 1

END

SELECT Username = @string, Username_Encrypted = @varbinary

[mxstu]

If it wasn't encrypted, I would just suggest using a simple

SELECT cast(yourVarbinaryColumn AS varchar(16)) FROM yourTable

There might be a better way, but I would probably just reverse the encryption
logic. Maybe put it in a UDF depending on your security concerns.






CREATE FUNCTION dbo.decryptString (@binString varbinary(32))
RETURNS varchar(16)
AS
BEGIN
DECLARE @revertString varchar(16)
DECLARE @finalString varchar(16)
DECLARE @varbinary varbinary(32)
DECLARE @string varchar(1)
DECLARE @position int

SET @finalString = ''
SET @position = 1
SET @revertString = (SELECT CAST(@binString AS varchar(16)))

WHILE @position <= DATALENGTH(@revertString)
BEGIN
SET @string = CAST(CHAR(ASCII(SUBSTRING(@revertString, @position, 1))-96)
AS varchar(1))
SET @finalString = @finalString + @string
SET @position = @position + 1
END


RETURN @finalString

END
GO

SELECT dbo.decryptString(0xAAC1D3CFCEA8)

[AZDeveloper]

:)

Thank you very much, mxstu. Your code is extremely helpful and is greatly apprecitated!