Member Avatar for Fost

Hi All,

I'm having a bit of an issue with a stored procedure I have written which encrypts a value using ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE.

The problem is these functions return Varbinary data types which I need to append to a Varchar data type.

Can anyone help with being able to display this as a Varchar data type without converting it? ie have a Varchar whose value is "0x010000004C4101804DD79FC3CA2258826140B2A3B66A0130F4AECB34" rather than the actual Varchar conversion of the Varbinary.

I'm not limited to using ENCRYPTBYPASSPHRASE if anyone knows of a more convenient method. Please bear in mind the values are stored in plaintext so encrypting the columns is the only no-no.

Thanks!

Fost

  • 2 Contributors
  • 1 Reply
  • 137 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by buddylee17
Member Avatar for buddylee17

The master database has a scalar function, fn_varbintohexstr, for this.

Example:

DECLARE @text varchar(256), @varbinary varbinary(256);
SET @text = 'A1B2C3';
SET @varbinary = cast(@text as varbinary(256));

SELECT 
     @text OriginalString
    , sys.fn_varbintohexstr(@varbinary) HexString /* returns nvarchar(max)*/
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.