How to handle encrypted column?

Question

Dear All,

I have a User Table, with password column which I need to encrypt before insert into database. And Descrypt when retrieving.

I can probably automatically decrypt the password during OnLoad(), but how do I automatically encrypt it before saving?

A better solution is to encrypt / decrypt only when I access to the password field.

I knew I can do that with Rational product. Hopefully LINQ have something similar.

Answer 1

Don't store passwords (encrypted or not), store a signature instead and validate that signature when validating the password.

The following class contains everything you need to generate a key pair (GetNewDSAKey), sign a password (GetDSASignature), verify a signature (IsValidDSASignature), and extracting only the public portion of the key from the key pair after signing (GetPublicDSAKey). It uses the DSACryptoServiceProvider which is documented here.

public sealed class HashAndSign
{
    /// <summary>
    /// Retrieves a DSA signature for a string, using the key passed in.
    /// </summary>
    /// <param name="privateKey">Private key used for signing</param>
    /// <param name="value">Value to sign</param>
    /// <returns>A DSA signature for the value passed in</returns>
    public static string GetDSASignature(string privateKey, string value)
    {
        DSACryptoServiceProvider ds = new DSACryptoServiceProvider();
        ds.FromXmlString(privateKey);
        return Convert.ToBase64String(ds.SignData(Encoding.UTF8.GetBytes(value)));
    }

    /// <summary>
    /// Verifies a DSA signature using a public key.
    /// </summary>
    /// <param name="publicKey">The public portion of the key used for signing</param>
    /// <param name="value">Value that was signed</param>
    /// <param name="signature">Signature to verify</param>
    /// <returns>True if the signature is valid for the value/key pair. False if not.</returns>
    public static bool IsValidDSASignature(string publicKey, string value, string signature)
    {
        DSACryptoServiceProvider ds = new DSACryptoServiceProvider();
        ds.FromXmlString(publicKey);
        return ds.VerifyData(Encoding.UTF8.GetBytes(value), Convert.FromBase64String(signature));
    }

    /// <summary>
    /// Retrieves a new DSA key pair
    /// </summary>
    /// <param name="keySize">Key size in bits (512-1024). Increment in steps of 64 bits.</param>
    /// <returns>Key pair string</returns>
    public static string GetNewDSAKey(int keySize)
    {
        if (keySize < 512) { keySize = 512; }
        if (keySize > 1024) { keySize = 1024; }
        keySize = keySize - keySize % 64;
        DSACryptoServiceProvider ds = new DSACryptoServiceProvider(keySize);
        return ds.ToXmlString(true);
    }

    /// <summary>
    /// Extracts a public key from a DSA key pair
    /// </summary>
    /// <param name="privateDSAKey">Key pair to extract the public key from</param>
    /// <returns>The public key portion of the key pair</returns>
    public static string GetPublicDSAKey(string privateDSAKey)
    {
        DSACryptoServiceProvider ds = new DSACryptoServiceProvider();
        ds.FromXmlString(privateDSAKey);
        return ds.ToXmlString(false);
    }
}

.

---

Kristofer - Huagati Systems Co., Ltd.
Cool tools for Linq-to-SQL and Entity Framework:
huagati.com/dbmltools (add-in with new features for Visual Studio 2008's L2S and EF designers)
huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)

Answer 2

Linq To Sql data model is a database related and represents database structure as close as possible. So entity class should have property for encrypted password. We suggest you to create another property for opened password. "Get" method will decrypt password and return the value, "set" method will encrypt the opened value.

---

Pavel Kolesnikov, Devart Team
http://www.devart.com/dotconnect
ADO.NET data providers for Oracle, MySQL, PostgreSQL, SQLite with Entity Framework and LINQ to SQL support