Click here to Skip to main content
Click here to Skip to main content

Using MD5 Encryption with C# and MSSQL 2000

, 10 Oct 2006
Rate this:
Please Sign up or sign in to vote.
Demonstrates methods for creating and validating MD5 encrypted passwords in 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

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.

/* 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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

UsualDosage
Architect
United States United States
I have been an ASP.NET/C# Programmer/Software Architect for about 10 years, specializing in web architecture, user interface, and user experience. I formerly wrote business applications for mortgage banking front-ends in C++ before switching to the .NET Framework many years ago, and I've never looked back. I'm an evangelist of HTML5 and web standards, and spend the majority of my time working on front end design, performance and scale.

My primary website is located at http://www.usualdosage.com

Comments and Discussions

 
Generalthnx... Pinmemberjlin070730-Nov-08 23:15 
Jokegood one Pinmemberdj.rock10-Apr-07 19:46 
GeneralMD5 is not encryption PinmemberShawn Poulson20-Sep-06 16:37 
GeneralRe: MD5 is not encryption PinmemberUsualDosage20-Sep-06 17:49 
GeneralSalt your hash PinmemberSteven Berkovitz4-Jan-06 7:25 
GeneralRe: Salt your hash PinmemberUsualDosage4-Jan-06 8:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.141015.1 | Last Updated 10 Oct 2006
Article Copyright 2006 by UsualDosage
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid