Author: way0utwest

This is a good T-SQL Tuesday topic for me. This month Ken Wilson asks everyone to write on encryption, which is a topic I’ve presented on quite a few times.

You can participate, too. Take a few hours, learn something, and tell us what you learned. Let everyone know how you view this topic and grow your blog with a little new knowledge.

T-SQL Tuesday is a great chance to force you to write a post on a specific topic. Your post needs to publish on the second Tuesday of the month, Aug 11 this month, to be included in the roundup from the host. However feel free to write about this topic anytime, and even include the T-SQL Tuesday title.

CASTing Results

A short post this month, as I’m a bit buried in a few things, but this is one of those encryption notes that I didn’t see well documented when I started working with the subject, and I’m hoping I can save you a few minutes of frustration.

If you encrypt your data, it will be stored as a binary type. This is because encrypted data is supposed to be random, and not easily decrypted.

Let’s imagine I have some simple setup like the code below. I’ll create a key, open it, and use it to encrypt some data that I’ll insert into a table.

CREATE TABLE MyEncryptionTest( intsource INT, charsource VARCHAR(50), intencrypt VARBINARY(max), charencrypt VARBINARY(max));
CREATE SYMMETRIC KEY Mykey WITH    ALGORITHM = AES_128 ENCRYPTION BY PASSWORD = 'M$test78';
GO
OPEN SYMMETRIC KEY MyKey DECRYPTION BY PASSWORD = 'M$test78';

INSERT dbo.MyEncryptionTest
        ( intsource ,
          charsource ,
          intencrypt ,
          charencrypt
        )
VALUES  ( 7, 
          'Spike' , 
          ENCRYPTBYKEY(KEY_GUID('MyKey'), CAST(7 AS VARCHAR(10))) ,
          ENCRYPTBYKEY(KEY_GUID('MyKey'), 'Spike')
        );

SELECT top 20
 * FROM dbo.MyEncryptionTest;

The results of this are that I get binary data:

Now, the decryption routine for T-SQL doesn’t need to specify the key. That means instead of a *, I can use the DECRYPTBYKEY function and pass in the column.

SELECT TOP 20
        intdecrypt = DECRYPTBYKEY(intencrypt),
        chardecrypt = DECRYPTBYKEY(charencrypt) ,
        intencrypt ,
        charencrypt
FROM    dbo.MyEncryptionTest;

This gives me this:

Not quite what I want. What if I cast this back to an integer? After all, the output of the function is listed as an nvarchar.

SELECT TOP 20
        intdecrypt = CAST(DECRYPTBYKEY(intencrypt) AS INT),
        chardecrypt = DECRYPTBYKEY(charencrypt) ,
        intencrypt ,
        charencrypt
FROM    dbo.MyEncryptionTest;

I see:

Again, not what I wanted. However, since I know something about conversions, I realize the output is close to what I want. In fact, what I need to do is perform a different CAST before I perform my final one. Here I’ll decrypt the results as NVARCHAR first, then as an INT.

SELECT TOP 20
        intdecrypt = CAST(CAST(DECRYPTBYKEY(intencrypt) AS NVARCHAR(30)) AS INT),
        chardecrypt = DECRYPTBYKEY(charencrypt) ,
        intencrypt ,
        charencrypt
FROM    dbo.MyEncryptionTest;

Now I see:

If I do the same for the character column:

SELECT TOP 20
        intdecrypt = CAST(CAST(DECRYPTBYKEY(intencrypt) AS NVARCHAR(30)) AS INT),
        chardecrypt = CAST( DECRYPTBYKEY(charencrypt) AS VARCHAR(50)) ,
        intencrypt ,
        charencrypt
FROM    dbo.MyEncryptionTest;

I’ll get the correct results.

Note that if I take the character column and cast to nvarchar, I’ll get something different. Try it and see.

And don’t forget to close your key.

CLOSE SYMMETRIC KEY mykey;