Introduction
This article is about how to use web.config to store and retrieve MS SQL database connection strings. Side by side, the security of the information stored is also taken care of. The following is a sample web.config file:
<APPSETTINGS>
<ADD value="127.0.0.1" key="Server Name" />
<ADD value="hq" key="Database Name" />
<ADD value="false" key="Login Secure" />
<ADD value="jzd" key="User Name" />
<ADD
value="4F4A68462B30794B51743655503470724373593955413D3D"
key="Password"/>
</APPSETTINGS>
In this appSettings section in web.config, the value of the Password we provide is encrypted using DES RC2 and Rijndael. For this, you need to add a line at the top of the encrypting class: using System.Security.Cryptography;
So, in order to implement this, we need to write an encrypting class, a data connection class and a web control to manipulate the web.config.
Encrypting class
Here are some overridden methods:
public string EncryptingHex(string Source, byte[] key, byte[] iv)
public string DecryptingHex(string Source, byte[] key, byte[] iv)
public string EncryptingHex(string Source, string key, string iv)
public string DecryptingHex(string Source, string key, string iv)
public byte[] EncryptingStream(byte[] bytIn,string key,string iv)
public byte[] DecryptingStream(byte[] bytIn, string key, string iv)
using System;
using System.Security.Cryptography;
using System.IO;
using System.Text;
using System.Web;
namespace eReach.Fundamental.Security
{
public class Key
{
#region Private Members
private SymmetricAlgorithm CryptoSvc;
private int KeySize, IVSize;
#endregion
#region Constractors
public Key()
{
CryptoSvc = new RijndaelManaged();
KeySize = 32;
IVSize = 16;
}
public Key(int Crypto)
{
switch (Crypto)
{
case 0:
CryptoSvc = new DESCryptoServiceProvider();
KeySize = 8;
IVSize = 8;
break;
case 1:
CryptoSvc = new RC2CryptoServiceProvider();
KeySize = 8;
IVSize = 8;
break;
case 2:
CryptoSvc = new RijndaelManaged();
KeySize = 32;
IVSize = 16;
break;
}
}
#endregion
#region Private Methods
private byte[] GetLegalKey(string key)
{
if (key.Length < KeySize)
key = key.PadRight(KeySize,' ');
else
{
key = key.Substring(0,KeySize);
}
return ASCIIEncoding.ASCII.GetBytes(key);
}
private byte[] GetLegalIV(string iv)
{
if (iv.Length < IVSize)
iv = iv.PadRight(IVSize,' ');
else
{
iv = iv.Substring(0,IVSize);
}
return ASCIIEncoding.ASCII.GetBytes(iv);
}
private byte HexVal(char chr)
{
byte retvar = 0;
switch (chr)
{
case 'A':
retvar = 10;
break;
case 'B':
retvar = 11;
break;
case 'C':
retvar = 12;
break;
case 'D':
retvar = 13;
break;
case 'E':
retvar = 14;
break;
case 'F':
retvar = 15;
break;
case '9':
retvar = 9;
break;
case '8':
retvar = 8;
break;
case '7':
retvar = 7;
break;
case '6':
retvar = 6;
break;
case '5':
retvar = 5;
break;
case '4':
retvar = 4;
break;
case '3':
retvar = 3;
break;
case '2':
retvar = 2;
break;
case '1':
retvar = 1;
break;
case '0':
retvar = 0;
break;
default:
retvar = 0;
break;
}
return retvar;
}
private string HexEncoding(string hexdest)
{
char[] passchr = hexdest.ToCharArray();
string dest = "";
int first = 0, second = 0;
for (int i = 0; i <= passchr.Length - 1; i += 2)
{
first = HexVal(passchr[i]) * 16;
second = HexVal(passchr[i+1]);
dest += (char)(first + second);
}
return dest;
}
#endregion
#region Protected Method
protected string Encrypting(string Source,byte[] key,byte[] iv)
{
byte[] bytIn =
System.Text.ASCIIEncoding.ASCII.GetBytes(
System.Web.HttpUtility.UrlEncode(Source));
System.IO.MemoryStream ms = new System.IO.MemoryStream();
CryptoSvc.Key = key;
CryptoSvc.IV = iv;
ICryptoTransform encrypto = CryptoSvc.CreateEncryptor();
CryptoStream cs = new CryptoStream(ms, encrypto,
CryptoStreamMode.Write);
cs.Write(bytIn, 0, bytIn.Length);
cs.FlushFinalBlock();
byte[] bytOut = ms.GetBuffer();
int i = 0;
for (i = 0; i < bytOut.Length; i++)
if (bytOut[i] == 0)
break;
return System.Convert.ToBase64String(bytOut, 0, i);
}
private string Decrypting(string Source, byte[] key, byte[] iv)
{
byte[] bytIn =
System.Convert.FromBase64String(Source);
System.IO.MemoryStream ms = new System.IO.MemoryStream(bytIn,
0, bytIn.Length);
CryptoSvc.Key = key;
CryptoSvc.IV = iv;
ICryptoTransform encrypto = CryptoSvc.CreateDecryptor();
CryptoStream cs = new CryptoStream(ms, encrypto,
CryptoStreamMode.Read);
System.IO.StreamReader sr = new System.IO.StreamReader( cs );
string sEncoded = sr.ReadToEnd();
return System.Web.HttpUtility.UrlDecode(sEncoded);
}
protected string Encrypting(string Source,string key,string iv)
{
byte[] bytIn =
System.Text.ASCIIEncoding.ASCII.GetBytes(
System.Web.HttpUtility.UrlEncode(Source));
System.IO.MemoryStream ms = new System.IO.MemoryStream();
CryptoSvc.Key = GetLegalKey(key);
CryptoSvc.IV = GetLegalIV(iv);
ICryptoTransform encrypto = CryptoSvc.CreateEncryptor();
CryptoStream cs = new CryptoStream(ms, encrypto,
CryptoStreamMode.Write);
cs.Write(bytIn, 0, bytIn.Length);
cs.FlushFinalBlock();
byte[] bytOut = ms.GetBuffer();
int i = 0;
for (i = 0; i < bytOut.Length; i++)
if (bytOut[i] == 0)
break;
return System.Convert.ToBase64String(bytOut, 0, i);
}
private string Decrypting(string Source, string key, string iv)
{
byte[] bytIn = System.Convert.FromBase64String(Source);
System.IO.MemoryStream ms = new System.IO.MemoryStream(bytIn,
0, bytIn.Length);
CryptoSvc.Key = GetLegalKey(key);
CryptoSvc.IV = GetLegalIV(iv);
ICryptoTransform encrypto = CryptoSvc.CreateDecryptor();
CryptoStream cs = new CryptoStream(ms, encrypto,
CryptoStreamMode.Read);
System.IO.StreamReader sr =
new System.IO.StreamReader( cs );
string sEncoded = sr.ReadToEnd();
return System.Web.HttpUtility.UrlDecode(sEncoded);
}
#endregion
#region Public Methods
public string EncryptingHex(string Source, byte[] key, byte[] iv)
{
string dest = Encrypting(Source,key,iv);
byte[] hexbty = Encoding.ASCII.GetBytes(dest.ToCharArray());
string hexdest = "";
for (int i = 0; i <= hexbty.Length - 1; i++)
{
hexdest += hexbty[i].ToString("X2");
}
return hexdest;
}
public string DecryptingHex(string Source, byte[] key, byte[] iv)
{
Source = HexEncoding(Source);
return Decrypting(Source,key,iv);
}
public string EncryptingHex(string Source, string key, string iv)
{
string dest = Encrypting(Source,key,iv);
byte[] hexbty = Encoding.ASCII.GetBytes(dest.ToCharArray());
string hexdest = "";
for (int i = 0; i <= hexbty.Length - 1; i++)
{
hexdest += hexbty[i].ToString("X2");
}
return hexdest;
}
public string DecryptingHex(string Source, string key, string iv)
{
Source = HexEncoding(Source);
return Decrypting(Source,key,iv);
}
public byte[] EncryptingStream(byte[] bytIn,string key,string iv)
{
System.IO.MemoryStream ms = new System.IO.MemoryStream();
CryptoSvc.Key = GetLegalKey(key);
CryptoSvc.IV = GetLegalIV(iv);
ICryptoTransform encrypto = CryptoSvc.CreateEncryptor();
CryptoStream cs = new CryptoStream(ms, encrypto,
CryptoStreamMode.Write);
cs.Write(bytIn, 0, bytIn.Length);
cs.FlushFinalBlock();
byte[] bytOut = ms.GetBuffer();
int i = 0;
for (i = 0; i < bytOut.Length; i++)
if (bytOut[i] == 0)
break;
return bytOut;
}
protected byte[] DecryptingStream(byte[] bytIn, string key, string iv)
{
System.IO.MemoryStream ms = new System.IO.MemoryStream(bytIn,
0, bytIn.Length);
CryptoSvc.Key = GetLegalKey(key);
CryptoSvc.IV = GetLegalIV(iv);
ICryptoTransform encrypto = CryptoSvc.CreateDecryptor();
CryptoStream cs = new CryptoStream(ms, encrypto,
CryptoStreamMode.Read);
System.IO.StreamReader sr = new System.IO.StreamReader( cs );
return System.Text.ASCIIEncoding.ASCII.GetBytes(
System.Web.HttpUtility.UrlEncode(sr.ReadToEnd()));
}
#endregion
}
}
Data connection class
Some of the methods are given here:
public string GenConfigPassword(string password)
public DataSet GetDataSet(string selectcmd)
public DataSet GetDataSet(string selectcmd,string database)
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
namespace eReach.Fundamental.Data
{
public class Connection : eReach.Fundamental.Security.Key
{
#region Declares
private string servername = "";
private string dbname = "";
private string username = "";
private string password = "";
private string loginsecure = "";
private string connstr = "";
private const string Key =
"m2pxltd9Qrs7yAjco0eFuh3vb6iw4k8ng5z1";
private const string IV = "d9cz13gmvb67iwhy";
protected SqlConnection conn;
#endregion
#region 构造和析构
public Connection() : base()
{
try
{
servername =
ConfigurationSettings.AppSettings["Server Name"];
dbname =
ConfigurationSettings.AppSettings["Database Name"];
username =
ConfigurationSettings.AppSettings["User Name"];
password =
ConfigurationSettings.AppSettings["Password"];
loginsecure =
ConfigurationSettings.AppSettings["Login Secure"];
if (loginsecure == "true")
connstr = "Data Source=" + servername +
";Initial Catalog=" + dbname +
";Trusted_Connection=yes;Connect Timeout=30";
else
{
connstr = "Data Source=" + servername +
";Initial Catalog=" +
dbname + ";User ID=" +
username + ";Password=" +
this.DecryptingHex(password,Key,IV);
}
conn = new SqlConnection(connstr);
}
catch
{
conn = new SqlConnection();
}
}
~Connection()
{
conn.Close();
}
#endregion
#region Protected Method
protected string GetPrototypeConfigPassword(string password)
{
return this.DecryptingHex(password,Key,IV);
}
#endregion
#region public methods
public string GenConfigPassword(string password)
{
return this.EncryptingHex(password,Key,IV);
}
public DataSet GetDataSet(string selectcmd)
{
try
{
SqlCommand cmd = new SqlCommand(selectcmd,conn);
cmd.CommandTimeout = 30;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
conn.Open();
DataSet ds = new DataSet();
adapter.Fill(ds,"usersbase");
conn.Close();
return ds;
}
catch
{
return (new DataSet());
}
}
public DataSet GetDataSet(string selectcmd,string database)
{
try
{
SqlCommand cmd = new SqlCommand(selectcmd,conn);
cmd.CommandTimeout = 30;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
conn.Open();
conn.ChangeDatabase(database);
DataSet ds = new DataSet();
adapter.Fill(ds,"usersbase");
conn.Close();
return ds;
}
catch
{
return (new DataSet());
}
}
public SqlDataReader GetReader(string selectcmd)
{
try
{
SqlCommand cmd = new SqlCommand(selectcmd, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
return reader;
}
catch
{
return null;
}
}
public int ExecuteSQLCmd(string sqlcmd)
{
try
{
SqlCommand cmd = new SqlCommand(sqlcmd, conn);
conn.Open();
return cmd.ExecuteNonQuery();
}
catch
{
return 0;
}
}
#endregion
}
}
Web control
This is the last step of our goal. To write a web control that can manipulate the web.config conveniently, download the web control source code and make a simple update for the password key.
#region Events
private void LinkButton1_Click(object sender, EventArgs e)
{
if (this.TextBox1.Text.Trim() != "")
this.AppSettings("Server Name",this.TextBox1.Text.Trim());
if (this.TextBox2.Text.Trim() != "")
this.AppSettings("Database Name",this.TextBox2.Text.Trim());
if (this.CheckBox1.Checked)
this.AppSettings("Login Secure","true");
else
this.AppSettings("Login Secure","false");
if (this.TextBox3.Text.Trim() != "")
this.AppSettings("User Name",this.TextBox3.Text.Trim());
if (this.TextBox4.Text.Trim() != "")
{
if (this.TextBox4.Text.Trim() == this.TextBox5.Text.Trim())
this.AppSettings("Password",
this.ocon.GenConfigPassword(this.TextBox4.Text.Trim()));
else
this.Page.RegisterStartupScript("",
"<script>alert('Confirm is not correct!');</script>");
}
}
#endregion
