Storing VARBINARY in a VARCHAR column

Question

Please go through this code:

DECLARE @EncryptedPassword VARBINARY(8000) = ENCRYPTBYPASSPHRASE('ABCD','password')
DECLARE @Setting AS TABLE (ID INT, Value VARCHAR(MAX))

INSERT INTO @Setting VALUES (1, 'true')
INSERT INTO @Setting VALUES (2, @EncryptedPassword)

SELECT * FROM @Setting

@Setting table can contain data with multiple data types. As I can see, its storing the value. But cannot retrieve it using SELECT command. How can I achieve this?

`CONVERT(VARCHAR(MAX), @EncryptedPassword, 2)` will give you a hexstring, which can be converted back in much the same way. — Jeroen Mostert, Feb 15 '19 at 06:05
Please reconsider this design. Making everything *stringly* typed makes things "simpler" right up until you retrieve a setting that *should* be an `int` (or here, the `varbinary`) and you find some completely inappropriate string there instead. Consider making your individual settings *columns* instead. — Damien_The_Unbeliever, Feb 15 '19 at 07:30

score 1 · Answer 1 · answered Feb 15 '19 at 07:42

As I said in the comments, I'd strongly recommend that you pivot your settings table and stop stingly-typing everything:

DECLARE @EncryptedPassword VARBINARY(8000) = ENCRYPTBYPASSPHRASE('ABCD','password')
DECLARE @Setting AS TABLE (
    Lock char(1) not null default 'X' primary key check (Lock='X'),
    SomethingBoolean bit not null,
    Password varbinary(8000) not null)

INSERT INTO @Setting (SomethingBoolean, Password) VALUES
(1,@EncryptedPassword)

SELECT * FROM @Setting

Results:

Lock SomethingBoolean Password
---- ---------------- -----------------------------------------------------------------------------------------------------------
X    1                0x02000000588C2EB2C67DED10B7828C05500008DB1B1E53EFF63168EC607CA80CA2A1147B75F0A9D50F6B95C810D6DCED37AE8186

This means that settings are actually stored with the correct data type. Not only can we rely on type and null checks to ensure settings are correct, we can also apply individual CHECK constraints if required on individual settings.

(Lock is just there to ensure that this table contains 1 row only)

score 0 · Answer 2 · answered Feb 15 '19 at 06:19

Try to change The Table Structure and While retrieving DECRYPT Value try to convert into Varbinary column Nvarchar.ENCRYPTBYPASSPHRASE OR DECRYPTBYPASSPHRASE require 2 or more paramter to give you result

DECLARE @Setting AS TABLE (ID INT,Value VARBINARY(8000))

INSERT INTO @Setting 
SELECT 1,ENCRYPTBYPASSPHRASE('password',N'ABCD')

SELECT * FROM @Setting
SELECT CONVERT(NVARCHAR(4000),DECRYPTBYPASSPHRASE('password',Value)) FROM @Setting

score 0 · Answer 3 · answered Feb 15 '19 at 08:11

You can just add encryption flag:

DECLARE @EncryptPhrase NVARCHAR(100) = 'ABCD'

DECLARE @Setting AS TABLE (ID INT, Value NVARCHAR(MAX), IsEncrypted BIT)

INSERT INTO @Setting VALUES (1, 'true',  0)
INSERT INTO @Setting VALUES (2, ENCRYPTBYPASSPHRASE(@EncryptPhrase,N'password'), 1)

SELECT * FROM @Setting

SELECT 
ID
,CASE 
    WHEN IsEncrypted = 1 THEN CAST(DecryptByPassPhrase(@EncryptPhrase, CAST(Value AS VARBINARY(MAX))) as NVARCHAR(MAX)) 
    ELSE Value 
END
FROM @Setting

Storing VARBINARY in a VARCHAR column

3 Answers3