CODEWITHSUNDEEP
asp.netsql-serverencryption
182764125216
182764125216

Reputation: 956

SQL Server Decrypt Alternate characters are (char)0

I have a web-app that is encrypting data in SQL Server, when I decrypt the data alternating characters show up as: �, (char)0, or \0 (depending on how you look at it)

If I just encrypt/decrypt in the database it all works.

Example:

SELECT DecryptByKey(Value) FROM Table WHERE TableID=0

Returns: 0x5400450053005400
Should Return: 0x54455354

It adds 00 in alternating characters, and can't CONVERT to VARCHAR

DECLARE @T1 VARCHAR(10);SET @T1='TEST';
DECLARE @T2 VARBINARY(MAX); SET @T2 = ENCRYPTBYKEY(Key_GUID('Test_Key'), @T1);
SELECT DecryptByKey(@T2)

Returns: 0x54455354

Correctly returns the binary array, and can CONVERT to VARCHAR

I created the Cert/Key as such:

CREATE CERTIFICATE Test_Cert
ENCRYPTION BY PASSWORD = '***************'
WITH SUBJECT = 'TEST CERT',
EXPIRY_DATE = '20160202';
GO

CREATE SYMMETRIC KEY Test_Key
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Test_Cert
GO

The app pool in IIS is set to allow 32-bit applications because it is also accessing an Oracle server and the dll requires it.

Upvotes: 0

Views: 376

Answers (1)

Sebastian Meine
Sebastian Meine

Reputation: 11793

Your input value is an NVARCHAR() value. NVARCHAR() stores characters in two bytes each. To make the text readable again, after decryption, just convert it back to NVARCHAR():

SQL Fiddle

Query 1:

DECLARE @in NVARCHAR(100) = 'A test';
DECLARE @ciph VARBINARY(100) = ENCRYPTBYPASSPHRASE('APassPhrase!',@in);

SELECT @in [@in], CONVERT(VARCHAR(MAX),@ciph,1) [@ciph],
       CONVERT(VARCHAR(MAX),DECRYPTBYPASSPHRASE('APassPhrase!',@ciph),1) [DECRYPTBYPASSPHRASE],
       CAST(DECRYPTBYPASSPHRASE('APassPhrase!',@ciph) AS NVARCHAR(100)) [out];

Results:

|    @in |                                                                      @ciph |        DECRYPTBYPASSPHRASE |    out |
|--------|----------------------------------------------------------------------------|----------------------------|--------|
| A test | 0x010000000CC6E5C9F1BC1B4C0B964DEF53F9F18B5AB8AD3D68AC4595A812E054C301187F | 0x410020007400650073007400 | A test |

Note 1:

My example is using ENCRYPTBYPASSPHARSE instead of ENCRYPTBYKEY, but the idea is the same.

Note 2:

To make the example work in SQLFiddle I had to add varbinary-to-string conversion to display varbinary values correctly: CONVERT(VARCHAR(MAX),...,1)

In SSMS, you could just use this statement:

Query 2:

DECLARE @in NVARCHAR(100) = 'A test';
DECLARE @ciph VARBINARY(100) = ENCRYPTBYPASSPHRASE('APassPhrase!',@in);

SELECT @in [@in], @ciph [@ciph],
       DECRYPTBYPASSPHRASE('APassPhrase!',@ciph) [DECRYPTBYPASSPHRASE],
       CAST(DECRYPTBYPASSPHRASE('APassPhrase!',@ciph) AS NVARCHAR(100)) [out];

Note 3:

If your data is never going to require unicode characters, you can alternatively convert the string to VARCHAR before encrypting it. That will also save a lot of storage space:

Query 3:

DECLARE @in NVARCHAR(100) = 'A test';
DECLARE @ciph VARBINARY(100) = ENCRYPTBYPASSPHRASE('APassPhrase!',CAST(@in AS VARCHAR(100)));

SELECT @in [@in], @ciph [@ciph],
       DECRYPTBYPASSPHRASE('APassPhrase!',@ciph) [DECRYPTBYPASSPHRASE],
       CAST(DECRYPTBYPASSPHRASE('APassPhrase!',@ciph) AS VARCHAR(100)) [out];

Upvotes: 1

Related Questions