亂馬客

前言

為資料做加解密處理是使用SQL Server的OPEN/CLOSE SYMMETRIC KEY 來實作，在「[SQL]為資料做加解密處理」中說明在資料庫中利用Trigger與View來實作。

而「[.NET]為資料做加解密處理-整合篇」說明了透過AP去使用SQL Server的資料加解/密，但是其中SYMMETRIC KEY的PASSWORD是寫死在程式碼之中。

這篇筆者將說明如何將Key的Password抽離出程式碼之外，讓資料加/解密可以更符合實際企業的應用。

 

實作

如果要將對稱金鑰的Password抽出程式碼的話，就要考慮將這Password放在某處，可放在檔案、機碼等地方。放在AP Server 或是DB Server上。

這裡筆者選擇將它放在DB之中，但直接放入明碼的話，顯然不夠安全，所以先將對稱金鑰的Password加密再放入；而要使用時，從DB取出後，再解密才能使用。

以下筆者將一步一步說明，

1.建立存放 對稱金鑰名稱 及 對稱金鑰的Password 的TABLE

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[SymmetricKeyInfo]') AND type IN (N'U')) 
BEGIN 
   DROP TABLE [SymmetricKeyInfo] 
END 
GO 
CREATE TABLE [SymmetricKeyInfo]( 
   [KeyName] VARCHAR(250) NOT NULL, -- 存放對稱金鑰名稱
   [KeyPassword] VARCHAR(250) NOT NULL -- 存放對稱金鑰的Password
)  
GO 

 

2.建立一個提供加/解密的DLL專案(CryptographyHelper)，加/解密使用DESCryptoServiceProvider。

要將加/解密資料從Byte Array轉成字串，可以使用Convert.ToBase64String；將字串轉成Byte Array，可以使用Convert.FromBase64String。

using System;
using System.Text;
using System.Security.Cryptography;
using System.IO;

namespace CryptographyHelper
{
    public class DESCryptoHelper
    {
        public DESCryptoHelper()
        {}

        public DESCryptoHelper(string key, string iv)
        {
            _key = key;
            _iv = iv;
        }

        // Create a new DES key.
        DESCryptoServiceProvider des = new DESCryptoServiceProvider();

        UTF8Encoding utf8 = new UTF8Encoding();

        private const string defKey = "rainrain";
        private const string defIV = "hohohoho";
        private string _key = string.Empty;
        private string _iv = string.Empty;

        public byte[] GetKeyArray()
        {
            byte[] result;
            if (string.IsNullOrEmpty(_key))
            {
                _key = defKey;
            }
            result = utf8.GetBytes(_key);
            Array.Resize(ref result, 8);
            return result;
        }

        public byte[] GetIVArray()
        {
            byte[] result;
            if (string.IsNullOrEmpty(_iv))
            {
                _iv = defIV;
            }
            result = utf8.GetBytes(_iv);
            Array.Resize(ref result, 8);
            return result;
        }


        // Encrypt the string.
        public   string Encrypt(string PlainText)
        {
                  // Create a memory stream.
            MemoryStream ms = new MemoryStream();
             
            // Create a CryptoStream using the memory stream and the 
            // CSP DES key.  
            CryptoStream encStream = new CryptoStream(ms
                                                    , des.CreateEncryptor(GetKeyArray(), GetIVArray())
                                                    , CryptoStreamMode.Write);

            // Create a StreamWriter to write a string
            // to the stream.
            StreamWriter sw = new StreamWriter(encStream);

            // Write the plaintext to the stream.
            sw.WriteLine(PlainText);

            // Close the StreamWriter and CryptoStream.
            sw.Close();
            encStream.Close();

            // Get an array of bytes that represents
            // the memory stream.
            byte[] buffer = ms.ToArray();

            // Close the memory stream.
            ms.Close();

            // Byte Array轉成字串，使用Convert.ToBase64String
            return Convert.ToBase64String(buffer);
        }

 
        // Decrypt the byte array.
        public string Decrypt(string CypherText)
        {
            // 從字串轉成Byte Array，使用Convert.FromBase64String
            byte[] CypherTextArray = Convert.FromBase64String(CypherText);
            // Create a memory stream to the passed buffer.
            MemoryStream ms = new MemoryStream(CypherTextArray);

            // Create a CryptoStream using the memory stream and the 
            // CSP DES key. 
            CryptoStream encStream = new CryptoStream(ms
                                                , des.CreateDecryptor(GetKeyArray(), GetIVArray())
                                                , CryptoStreamMode.Read);

            // Create a StreamReader for reading the stream.
            StreamReader sr = new StreamReader(encStream);

            // Read the stream as a string.
            string val = sr.ReadLine();

            // Close the streams.
            sr.Close();
            encStream.Close();
            ms.Close();

            return val;
        }
    }
}

 

 

3.建立一個產生加密字串SQL的Windows Form專案(GenSymmetricKeyInfoSQL)，並將該SQL新增到資料庫之中。

private void btnGenEncrypt_Click(object sender, EventArgs e)
{
    var cryptoHelper = new CryptographyHelper.DESCryptoHelper();
    txtENSymmetricKeyName.Text  = cryptoHelper.Encrypt(txtSymmetricKeyName.Text.Trim());
    txtENSymmetricKeyPassword.Text = cryptoHelper.Encrypt(txtSymmetricKeyPassword.Text.Trim());
    txtSQL.Text = string.Format(@"DELETE FROM SymmetricKeyInfo;
                        INSERT INTO SymmetricKeyInfo(KeyName, KeyPassword) VALUES ('{0}', '{1}')"
                    , txtENSymmetricKeyName.Text, txtENSymmetricKeyPassword.Text);
    MessageBox.Show("Encrypt OK!"); 
}

 

4.修改DataEncryptionDemo專案，增加從資料庫取得對稱金鑰名稱及Password的資料並解密。

private string symmetricKeyName = string.Empty;
private string GetSymmetricKeyName(SqlConnection conn)
{
    if (string.IsNullOrEmpty(symmetricKeyName))
    {
        SqlCommand cmd = new SqlCommand(@"SELECT TOP 1 KeyName FROM SymmetricKeyInfo", conn);
        string encrptyedSymmetricName = cmd.ExecuteScalar() as string;
        var cryptoHelper = new CryptographyHelper.DESCryptoHelper();
        symmetricKeyName = cryptoHelper.Decrypt(encrptyedSymmetricName);
    }
    return symmetricKeyName;
}

private string symmetricKeyPwd = string.Empty;
private string GetSymmetricKeyPassword(SqlConnection conn)
{
    if (string.IsNullOrEmpty(symmetricKeyPwd))
    {
        SqlCommand cmd = new SqlCommand(@"SELECT TOP 1 KeyPassword FROM SymmetricKeyInfo", conn);
        string encrptyedSymmetricKeyPassword = cmd.ExecuteScalar() as string;
        var cryptoHelper = new CryptographyHelper.DESCryptoHelper();
        symmetricKeyPwd = cryptoHelper.Decrypt(encrptyedSymmetricKeyPassword);
    }
    return symmetricKeyPwd;
}

 

 

5.修改DataEncryptionDemo專案中的OpenSymmetricKey及CloseSymmetricKey，改從資料庫取得資料解密後再執行。

因為在「[.NET]為資料做加解密處理-整合篇」中，已將OpenSymmetricKey及CloseSymmetricKey抽離成Method，所以這裡只要調整這2個Method即可。

private void OpenSymmetricKey(SqlConnection conn)
{
    //1.要先下 OPEN SYMMETRIC KEY DB_KEY1 DECRYPTION BY PASSWORD = 'rainmaker';
    string mySymmetricKeyName = GetSymmetricKeyName(conn);
    string mySymmetricKeyPwd = GetSymmetricKeyPassword(conn);
    string openSymmetricKeySQL = string.Format(@"OPEN SYMMETRIC KEY {0} DECRYPTION BY PASSWORD = N'{1}'"
                            , mySymmetricKeyName, mySymmetricKeyPwd);
    SqlCommand cmd = new SqlCommand(openSymmetricKeySQL, conn);
    cmd.ExecuteNonQuery();
}

private void CloseSymmetricKey(SqlConnection conn)
{
    //3.最後，要把Key Close
    string mySymmetricKeyName = GetSymmetricKeyName(conn);
    string closeSymmetricKeySQL =string.Format(@"CLOSE SYMMETRIC KEY {0}", mySymmetricKeyName);
    SqlCommand cmd = new SqlCommand(closeSymmetricKeySQL, conn);
    cmd.ExecuteNonQuery();
}

 

 

結論

從範例程式中可看到每次查詢都需要在執行的SQL前後去加入Open/Close Symmetric Key，筆者公司因為有將ADO.NET 包裝起來，所以可將Open/Close Symmetric Key包裝在底層之中，而讓開發者不需要每次都處理這些事情。當然也可搭配AOP哦。

將對稱金鑰的密碼加密存在DB中，資料庫管理人員如果不知解密方式，也無法得知對稱金鑰的密碼。開發人員如果沒有連接資料庫的權限，也無法取得資料來解密。

算是比較安全一些，但是如果還覺得不太保險的話，將加/解密的Key讓使用者來保管，在建立加密的密碼時(GenSymmetricKeyInfoSQL.EXE)，讓使用者輸入他要加密的Key。

如下，

var cryptoHelper = new CryptographyHelper.DESCryptoHelper(使用者自行保管Key輸入 , string.Empty );

只是這樣使用者在看這些機密資料時，都要輸入只有他記得的Key來解開。

 

另外，使用OPEN SYMMETRIC KEY時，透過SQL Profiler是無法錄出密碼的哦! 如下圖，

 

參考資料

OPEN SYMMETRIC KEY (Transact-SQL)

[SQL]為資料做加解密處理

[.NET]為資料做加解密處理-整合篇

 

範例程式

 

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活」

請大家繼續支持 ^_^