Recently, I had a requirement to fetch the encrypted string passed from SQL Server using function
EncryptByPassPhrase (more details about this function
here). This encrypted string was passed from a totally different setup to my ASP.Net web application.
My ASP.Net web Application required the decoded text. To do this I had to create a stored procedure which accepted the pass phrase and encrypted string as input and returned the decrypted string. The encrypted string type was
VARBINARY. The stored procedure was:
CREATE PROCEDURE [dbo].sp_DecryptText
(@PassPhrase NVARCHAR(128),
@EncryptedText VARBINARY(MAX),
@DecryptedText NVARCHAR(MAX) OUTPUT)
AS
BEGIN
SET @DecryptedText = DECRYPTBYPASSPHRASE(@PassPhrase, @EncryptedText);
RETURN
END
So I wrote a C# code to connect to stored procedure passing all the values as:
command.Parameters.AddWithValue("@PassPhrase", PassPhrase);
command.Parameters.Add("@EncryptedText", SqlDbType.VarBinary, 8000).Value = Encoding.Unicode.GetBytes(encryptedString);
command.Parameters.Add("@DecryptedText", SqlDbType.NVarChar, -1).Direction = ParameterDirection.Output;
But this did not work as the
encryptedString was already in hexadecimal format and converting it to bytes resulted in a totally different string.
To get the stored procedure working, I converted the
encryptedString to hexadecimal before passing it to the stored procedure by writing a separate function
ParseHexString as:
static byte[] ParseHexString(string value)
{
if (string.IsNullOrEmpty(value)) return null;
if (1 == (1 & value.Length)) throw new ArgumentException("Invalid length for a hex string.", "value");
int startIndex = 0;
int length = value.Length;
char[] input = value.ToCharArray();
if ('0' == input[0] && 'x' == input[1])
{
if (2 == length) return null;
startIndex = 2;
length -= 2;
}
Func<char, byte> charToWord = c =>
{
if ('0' <= c && c <= '9') return (byte)(c - '0');
if ('A' <= c && c <= 'F') return (byte)(10 + c - 'A');
if ('a' <= c && c <= 'f') return (byte)(10 + c - 'a');
throw new ArgumentException("Invalid character for a hex string.", "value");
};
byte[] result = new byte[length >> 1];
for (int index = 0, i = startIndex; index < result.Length; index++, i += 2)
{
byte w1 = charToWord(input[i]);
byte w2 = charToWord(input[i + 1]);
result[index] = (byte)((w1 << 4) + w2);
}
return result;
}
So my call to stored procedure changed to:
command.Parameters.AddWithValue("@PassPhrase", PassPhrase);
command.Parameters.Add("@EncryptedText", SqlDbType.VarBinary, 8000).Value = ParseHexString(encryptedString);
command.Parameters.Add("@DecryptedText", SqlDbType.NVarChar, -1).Direction = ParameterDirection.Output;
And the output was correct and in the right format!!
