Skip to content

Decryption and CASTing

July 3, 2012

In my last encryption post I showed how to encrypt and decrypt data with a symmetric key. However there was a piece of the explanation I left out. If you look at that post, suppose that you ran this query after you’d encrypted the data:

-- decrypt the data
select 
  id
,firstname
,lastname
,title
,Salary = DecryptByKey(EnryptedSalary)
,EnryptedSalary
 from Employees
go

The results wouldn’t be what you’d expect:

decrypt

The binary data is returned, which isn’t rendered correctly. The salary column is the decryption, and the EncryptedSalary is the encrypted data. Note they are different.

This stumped me for awhile when I was playing with encryption and I checked the dercryptbykey page thoroughly before I realized that the return type was varbinary and needed to be CAST.

If I cast this back to nvarchar, I get the data:

select 
  id
, title
, Salary = cast(DecryptByKey(EnryptedSalary) as nvarchar) 
, EnryptedSalary
 from Employees

decrypt2

In my example, I CAST to nvarchar, and then to numeric, mostly for clean coding. This is numeric data. Can I cast directly?

select 
  id
, title
, Salary = cast(DecryptByKey(EnryptedSalary) as numeric(10,4))
, EnryptedSalary
 from Employees
go

No. I get an error.

Msg 8115, Level 16, State 6, Line 1

Arithmetic overflow error converting varbinary to data type numeric.

This isn’t a valid CAST, so I need to double up the CASTs as shown in the original post.

About these ads

From → Blog

3 Comments
  1. Kevin Boisits permalink

    The reason you can not cast directly to numeric is because you casted to varchar when you encrypted the data.

    If when you encrypted it you casted to varbinary: ENCRYPTBYKEY(key_guid(‘MySalaryProtector’),CAST(salary AS VARBINARY))

    You could then use a single cast to decrypt it: cast(DecryptByKey(EnryptedSalary) as numeric(10,4))

    This seems to work well for encrypting/decrypting numbers and dates.

    Hope that helps,
    Kevin

  2. Ken permalink

    Still requires 2 CAST statements…

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 4,323 other followers

%d bloggers like this: