LINQ to SQL decrypt value before populating object

Question

There is a field in one of my tables that is encrypted using

ENCRYPTBYPASSPHRASE(<passphrase>,<value>)

When the value is placed into the object the field is still encrypted so I can't do anything with it. I can't create a view or stored proc or any other item that decrypts the fields on the database because then it defeats the purpose of encrypting the fields. Is there a way of having the frame work run something like

DECRYPTBYPASSPHRASE(<passphrase>, <columnName>)

before assigning the value to the object?

Right now I'm Getting the data then calling ExecuteQuery to decrypt the value. and assigning that new value over the encrypted value on my data model class. It works but I was just wondering if it could be done automatically through some options I don't know about. I've tried searching but have not found anything.

How will you automatically know the passPhrase and the type to convert the clear varbinary too, or do you want to retrieve a byte[]? — Jodrell
– Jodrell, Commented Sep 20, 2013 at 13:32
I know the PassPhrase Right now I get the Decrypt the value calling ExecuteQuery with something like the following. — I can be anything
– I can be anything, Commented Sep 20, 2013 at 17:38
I know the PassPhrase Right now I get the Decrypt the value calling ExecuteQuery with something like the following. "Select CAST(DECRYPTBYPASSPHRASE('{0}', {1}) AS NVARCHAR(MAX)) AS {2} FROM Table Whered PrimaryKey = {3}" Where 0 = phassphrase, 1 = column, 2 = name of column returned, 3 = key to exact record i want to decrypt. So I"m getting the value now. Its just that it takes me an extra step. After the data is populated into the object from the database. I have to run an extra query to to get the decrypted values. — I can be anything
– I can be anything, Commented Sep 20, 2013 at 17:46

scourge192 · Accepted Answer · 2013-09-20 13:08:29Z

1

I'm assuming that you are using linq-to-sql and that the table you are pulling from is structured like so:

+--------+---------------+
| UserId |   Passphrase  |
+--------+---------------+
|      1 | laskdfmlsadkf |
+--------+---------------+

With this information, you can apply the decrypt method during your select.

var password = "password";
var userId = 1;

var result = usertable.Where(c => c.UserId == userId).ToList()
.Select(t => new 
{
    Passphrase = DECRYPTBYPASSPHRASE(t.Passphrase)
}).First()

bool areSame = (password == result.Passphrase);

edited Sep 20, 2013 at 13:08

answered Aug 22, 2013 at 16:53

scourge192

2,21919 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

I can be anything Over a year ago

I haven't had a chance to go back and try this. I hope to get some time here soon.

Jodrell Over a year ago

DECRYPTBYPASSPHRASE is TSQL function, not a .Net one.

Scott Chamberlain Over a year ago

@Jodrell You would need to push the DECRYPTBYPASSPHRASE stored procedure in to your linq2sql model so it would be useable from .NET code.

Jodrell Over a year ago

@ScottChamberlain, is that implied by this answer?

Jodrell Over a year ago

The DecryptByPassphrase function takes at least 2 parameters and returns some clear varbinary, not a passphrase for comparison with a password. technet.microsoft.com/en-us/library/ms188910.aspx

Jodrell · Accepted Answer · 2013-09-23 11:54:40Z

0

As demonstrated by this fiddle, the following SQL is perfectly effective,

SELECT
            E.[Key],
            CAST(
                      DecryptByPassPhrase(
                          'test',
                          E.[Encrypted])
                AS
                    varchar(8000)) [Clear],
            E.[Other]
    FROM
            [Example] E;

So use this overload of ExecuteQuery to do something like,

var examples = context.ExecuteQuery<Example>(
    @"SELECT
                E.[Key],
                CAST(
                          DecryptByPassPhrase(
                              @p0,
                              E.[Encrypted])
                    AS
                        varchar(8000)) [Clear],
                E.[Other]
        FROM
                [Example] E;",
    passPhrase);

to retrieve and decrypt your data in one call.

answered Sep 23, 2013 at 11:54

Jodrell

35.9k6 gold badges94 silver badges131 bronze badges

1 Comment

I can be anything Over a year ago

yeah I was just coming to the conclusion that I was going to have to run the ExecuteQuery and provide the query myself.

Collectives™ on Stack Overflow

LINQ to SQL decrypt value before populating object

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related