Using MD5 Encryption with C# and MSSQL 2000

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

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.

/* Return types that are thrown when login is attempted */
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)
{
    //Create a connection
    string strConnString = System.Configuration.ConfigurationSettings.
                                            AppSettings["ConnString"];
    SqlConnection objConn = new SqlConnection(strConnString);
    
    // Create a command object for the query
    string strSQL = "INSERT INTO " + tableName + " (" + loginColumn + 
                    "," + passColumn + ") " + "VALUES(@Username, @Password)";
    
    SqlCommand objCmd = new SqlCommand(strSQL, objConn);
    
    //Create parameters
    SqlParameter paramUsername;
    paramUsername = new SqlParameter("@Username", SqlDbType.VarChar, 10);
    paramUsername.Value = strLogin;
    objCmd.Parameters.Add(paramUsername);
    
    //Encrypt the password
    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);
      
    //Insert the record into the database
    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.

//Returns a validation code based on the control's set login info
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);
        //Create the parameters
        SqlParameter paramUsername;
        paramUsername = new SqlParameter("@Username", SqlDbType.VarChar, 25);
        paramUsername.Value = strLogin;
        objCmd.Parameters.Add(paramUsername);

        //Hash the password
        MD5CryptoServiceProvider md5Hasher = new MD5CryptoServiceProvider();
        byte[] hashedDataBytes;
        UTF8Encoding encoder = new UTF8Encoding();
        hashedDataBytes = 
          md5Hasher.ComputeHash(encoder.GetBytes(strPassword));      

        //Execute the parameterized query
        SqlParameter paramPwd;
        paramPwd = new SqlParameter("@Password", SqlDbType.Binary, 16);
        paramPwd.Value = hashedDataBytes;
        objCmd.Parameters.Add(paramPwd);
        //The results of the count will be held here
        int iResults;
        try
        {
            objConn.Open();
            //We use execute scalar, since we only need one cell
            iResults = Convert.ToInt32(objCmd.ExecuteScalar().ToString());
        }
        catch
        //Connection failure (most likely, though 
        //you can handle this exception however)
        {
            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.