Introduction
This article will demonstrate how to use MD5 encryption to read and write password information to a MSSQL 2000 database.
Background
The .NET Framework provides developers with some easy to use classes for modern encryption. One of the more popular methods these days is the use of MD5 encryption. MD5 encryption, to quote from RFC 1232, "takes as input a message of arbitrary length and produces as output a 128-bit "fingerprint" or "message digest" of the input. It is conjectured that it is computationally infeasible to produce two messages having the same message digest, or to produce any message having a given prespecified target message digest. The MD5 algorithm is intended for digital signature applications, where a large file must be "compressed" in a secure manner before being encrypted with a private (secret) key under a public-key cryptosystem such as RSA." It was developed by Professor Ronald L. Rivest of MIT, and has become widely used as a standard encryption method for ASP.NET applications. See the Points of Interest at the bottom of this article for more practical information about MD5 usage.
Getting Started
Since .NET has made MD5 encryption so easy to use, I'm not including a demo project. I'll just include the required C# methods, and a SQL script for creating a test database table.
Creating a Test Table
Using your local instance of MSSQL 2000 (this will probably work on 2005 as well, but I've not tested it as yet). You can rename the table and the columns at your leisure, just ensure you change the calls to the methods that I'll detail below. For now, just run the following SQL script from a database you own:
if exists (select * from dbo.sysobjects where
id = object_id(N'[dbo].[tblLogins]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblLogins]
GO
CREATE TABLE [dbo].[tblLogins] (
[Login] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [binary] (16) NULL
) ON [PRIMARY]
GO
All we're doing here is creating a table called 'tblLogins', with two columns: a login column (varchar 25) and a password column (binary 16).
Adding a New Login
You can use the following method in your ASP.NET Web Form or C# Windows Form application. All we're doing here is making a connection to the database, and inserting a new login. I created an enumeration called ValidationCode so that I can handle responses from the method a little more clearly, but you can just as well just a void function to the same effect.
public enum ValidationCode
{
LoginFailed=1,
LoginSucceeded=2,
ConnectionFailed=3,
UnspecifiedFailure=4,
LoginCreated=5
}
You will need to ensure that you have the following references added to your application:
using System.Data.SqlClient;
using System.Security.Cryptography;
using System.Text;
CreateNewLogin will accept 5 values. The first value is the name of the table in the database housing the logins (in this example, it's tblLogin), the second and third values will be our desired login and password, in plain text, and the last two values are the names of the login column (Login) and the password column (Password) in our table, respectively.
public ValidationCode CreateNewLogin(string tableName, string strLogin,
string strPassword, string loginColumn, string passColumn)
{
string strConnString = System.Configuration.ConfigurationSettings.
AppSettings["ConnString"];
SqlConnection objConn = new SqlConnection(strConnString);
string strSQL = "INSERT INTO " + tableName + " (" + loginColumn +
"," + passColumn + ") " + "VALUES(@Username, @Password)";
SqlCommand objCmd = new SqlCommand(strSQL, objConn);
SqlParameter paramUsername;
paramUsername = new SqlParameter("@Username", SqlDbType.VarChar, 10);
paramUsername.Value = strLogin;
objCmd.Parameters.Add(paramUsername);
MD5CryptoServiceProvider md5Hasher = new MD5CryptoServiceProvider();
byte[] hashedBytes;
UTF8Encoding encoder = new UTF8Encoding();
hashedBytes = md5Hasher.ComputeHash(encoder.GetBytes(strPassword));
SqlParameter paramPwd;
paramPwd = new SqlParameter("@Password", SqlDbType.Binary, 16);
paramPwd.Value = hashedBytes;
objCmd.Parameters.Add(paramPwd);
try
{
objConn.Open();
objCmd.ExecuteNonQuery();
return ValidationCode.LoginCreated;
}
catch
{
return ValidationCode.ConnectionFailed;
}
finally
{
objConn.Close();
}
}
You can test this method out by executing the function. If you attempt to select the information from the database directly, you will notice that the encryption has worked. Now, on to validation.
Validating a Login
This method will allow you to validate a login against a pre-existing login in the database. It's important to note that we never actually return data to the requestor for evaluation. All of our evaluation is done server side; we only return row counts to the function, making it that much more secure.
public ValidationCode ValidateLogin(string tableName, string strLogin,
string strPassword, string loginColumn, string passColumn)
{
try
{
string strConnString = this.ConnectionString;
SqlConnection objConn = new SqlConnection(strConnString);
string strSQL = "SELECT COUNT(*) FROM " + tableName +
" WHERE " + loginColumn + "=@Username AND " + passColumn +
"=@Password;";
SqlCommand objCmd = new SqlCommand(strSQL, objConn);
SqlParameter paramUsername;
paramUsername = new SqlParameter("@Username", SqlDbType.VarChar, 25);
paramUsername.Value = strLogin;
objCmd.Parameters.Add(paramUsername);
MD5CryptoServiceProvider md5Hasher = new MD5CryptoServiceProvider();
byte[] hashedDataBytes;
UTF8Encoding encoder = new UTF8Encoding();
hashedDataBytes =
md5Hasher.ComputeHash(encoder.GetBytes(strPassword));
SqlParameter paramPwd;
paramPwd = new SqlParameter("@Password", SqlDbType.Binary, 16);
paramPwd.Value = hashedDataBytes;
objCmd.Parameters.Add(paramPwd);
int iResults;
try
{
objConn.Open();
iResults = Convert.ToInt32(objCmd.ExecuteScalar().ToString());
}
catch
{
return ValidationCode.ConnectionFailed;
}
finally
{
objConn.Close();
}
if (iResults == 1)
return ValidationCode.LoginSucceeded;
else
return ValidationCode.LoginFailed;
}
catch
{
return ValidationCode.UnspecifiedFailure;
}
}
You probably noticed that both methods have the same signature. It would be easy to combine both into a single function, but for this example, I'm keeping them separate. But that's all there is to it. You can now create a click event on your page or form, and call either of the functions, handling the return code appropriately. Again, you don't have to use the return codes; you can easily just handle the exceptions or the counts.
Points of Interest
As of 2004, MD5 has a known collision weakness. [From Wikipedia] "Because MD5 makes only one pass over the data, if two prefixes with the same hash can be constructed, a common suffix can be added to both to make the collision more reasonable. And because the current collision-finding techniques allow the preceding hash state to be specified arbitrarily, a collision can be found for any desired prefix -- for any given string of characters X, two colliding files can be determined which both begin with X. All that is required to generate two colliding files is a template file, with a 128-byte block of data aligned on a 64-byte boundary, that can be changed freely by the collision-finding algorithm." Many developers have recommended switching to WHIRLPOOL, SHA-1, or RIPEMD-160.