Symmetric Key Decryption returning values with space between characters

Question

I am using following Scaler-valued function to decrypt data that is encrypted using a symmetric key:

USE [DBNAME]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[DECRYPTDATA] 
(
    @CipherText NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Result NVARCHAR(MAX)
    SELECT @Result = CONVERT(VARCHAR(MAX),DECRYPTBYKEY(@CipherText))
    RETURN @Result
END

And I am using following query to get the data:

EXEC [dbo].OpenKeys
SELECT ([dbo].DECRYPTDATA([Value])) AS VALUE FROM [Table1] WHERE [UsrId] =2 AND [GroupID] = 44

I executed this query in SQL Server 2012 Management Studio, it's showing only the first letter of the decrypted text:

And when I checked the DataTable in C# code, it's showing space between characters:

Actual data must be like this:

╔═══════╗
║ VALUE ║
╠═══════╣
║ 232   ║
║ hgjhg ║
║ 12    ║
║ 53    ║
║ 0     ║
╚═══════╝

What's happening here?

UPDATE

I tried inserting directly like INSERT INTO Table1([Value]) VALUES([dbo].ENCRYPTISDATA('432')) but when I fetch this row it shows 4 only. So I think the problem is at the SQL Server side. What it is I don't know. Hope somebody can figure out soon. I have to submit this project in some days.

I am using TripleDES algorithm.

Answer 1

It appears that there are a couple of things at play here:

1. decryptbykey takes a varbinary, not an nvarchar. I'd guess that there is some unwanted conversion going on there.
2. You're converting the result of the decryption to varchar but returning from your function as nvarchar. Again unwanted conversion.

Answer 2

Regardless of the underlying cause of this issue, in case your original data does not contain blank spaces between characters, then you can apply a quick fix like: strWithSpaces=strWithSpaces.Replace(" ", String.Empty);

In regards to the cause: your original string before encryption probably contains some non-printable (i.e. invisible) characters, which get encoded. I recommend to analyze the original data prior to encryption, and remove the unnecessary chars at that stage using similar technique.

Hope this may help.