CODEWITHSUNDEEP
encryptionsql-server-2008-r2
bmurrell30
bmurrell30

Reputation: 715

SQL EncryptByKey / DecryptByKey turns data saved in English into non-English characters

I'm trying to use encryption to obfuscate a column in my SQL database. I started out following the steps shown at MSDN and that works fine, but isn't good for production since it displays the data I'm trying to keep secure in an unencrypted column.

So I'm tying to apply EncryptByKey to the SQL INSERT command, like so:

INSERT INTO [myTable]
VALUES (foo, 'bar', EncryptByKey(Key_GUID('EncryptionKey'), 'fooBar'));
GO

When I check the results after executing this command, I see the expected string of varbinary gobbledygook in the column containing encrypted data value 'fooBar' (my encrypted data column is set in varbinary(128) format.) However, when I try to decrypt my data thus:

SELECT [encryptedColumn],
    CONVERT(nvarchar, DecryptByKey(encryptedColumn)) 
    AS 'Decrypted Column'
    FROM [myTable];
GO

value 'fooBar' is returned in some cuneiform, Asian-looking script in 'Decrypted Column'. What's causing this? I am using SQL Server 2008 R2, and the AES_256 encryption algorithm.

Upvotes: 3

Views: 11564

Answers (2)

breusshe
breusshe

Reputation: 59

Your post gave me the insight to fix this problem. Your way does work, you can also just add a 'N' to the start of the string 'foobar', so like this:

INSERT INTO [myTable]
VALUES (foo, 'bar', EncryptByKey(Key_GUID('EncryptionKey')
       ,CONVERT(nvarchar(50), N'fooBar')));
GO

Does the same thing for you without having to guess how many characters you need for your convert statement... and you don't have to embed a convert statement.

Upvotes: 0

bmurrell30
bmurrell30

Reputation: 715

Turns out this is another object lesson in why it's a bad idea to make a computer guess what you're thinking.

When I inserted my new row with

INSERT INTO [myTable]
VALUES (foo, 'bar', EncryptByKey(Key_GUID('EncryptionKey'), 'fooBar'));
GO

...I neglected to specify what type of data 'fooBar' was. SQL took a stab at filling the blank, and so tragedy struck when it came time to decrypt.

Adding a CONVERT statement like so:

INSERT INTO [myTable]
VALUES (foo, 'bar', EncryptByKey(Key_GUID('EncryptionKey'), CONVERT(nvarchar(50), 'fooBar')));
GO

eliminates such guessing and 'fooBar' decrypts correctly. In this case, 'fooBar' is in fact an nvarchar.

Upvotes: 3

Related Questions