SQL Server 2005 encryption trigger

Question

Hello,

I have a script that successfully encrypts a credit card. I need it to work as a trigger, so that any insert done to the creditcard column automatically encrypts it. Right now, my trigger works BUT the creditcard column is a varchar. When an app tries to insert, I do this:

DECLARE @encryptedCreditCardNumber varbinary(max)
SET @encryptedCreditCardNumber = EncryptByKey(Key_GUID('testKey'), @CreditCardNumber));

That works fine, but how do I then convert the varbinary(max) to a varchar (which is what the credit card column is). The creditcard column has been a varchar for a long time at my company and a lot of legacy code depends on it being a varchar.

Thank you

Answer 1

Your simplest approach is going to be to convert the binary to base64 and store that in the varchar column. Base64 is a method for rendering binary data using ascii encoding so that it could be represented in formats such as XML. You can perform the conversion by doing:

select cast(N'' as xml).value('xs:base64Binary(xs:hexBinary(sql:variable("@encryptedCreditCardNumber")))', 'varchar(max)');

This goes through an XML intermediary to correctly encode the varbinary to a varchar. To reverse the process on the DB use:

select cast(N'' as xml).value('xs:base64Binary(sql:variable("@base64stringvariable"))', 'varbinary(20)');

Edit: Useful reference - http://blogs.msdn.com/sqltips/archive/2008/06/30/converting-from-base64-to-varbinary-and-vice-versa.aspx