Click here to Skip to main content
11,583,532 members (63,522 online)
Click here to Skip to main content

MD5 Hash SQL Server Extended Stored Procedure

, 7 Mar 2005 1.4M 10K 110
Rate this:
Please Sign up or sign in to vote.
An extended stored procedure for SQL Server that implements an optimized MD5 hash algorithm. Very small DLL (barely 7 KB).

Introduction

This is an extended stored procedure for Microsoft SQL Server 2000 that implements an optimized MD5 hash algorithm. It is intended to work much like the MySQL MD5() function. The algorithm was taken from here. I only added the md5_string() function. The DLL should work for older versions of SQL Server too, although I have not tested that. The source was compiled and tested on Microsoft Visual C++ 6.0 and .NET 2003.

Installation

  1. Extract or build the DLL file xp_md5.dll and place it in C:\Program Files\Microsoft SQL Server\MSSQL\Binn (or wherever appropriate). A precompiled DLL is in the Release directory of the source distribution.
  2. Create an Extended Stored Procedure called xp_md5 in the "master" database. Right-click "Extended Stored Procedures" under the master database in the Server Manager and click "New Extended Stored Procedure...". Enter xp_md5 for the "Name" and for the "Path", enter the full path to xp_md5.dll.

    Note: If you want to add it manually:

    USE master;
    EXEC sp_addextendedproc 'xp_md5', 'xp_md5.dll'
  3. Create a user-defined function for each database in which you plan to use the MD5 procedure. Right-click "User Defined Functions" under the appropriate database(s) and click "New User Defined Function...". Enter the following:
    CREATE FUNCTION [dbo].[fn_md5] (@data TEXT) 
    RETURNS CHAR(32) AS
    BEGIN
      DECLARE @hash CHAR(32)
      EXEC master.dbo.xp_md5 @data, -1, @hash OUTPUT
      RETURN @hash
    END
  4. (Optional) Create other user-defined functions to let you specify the data length (for substrings, BINARY and other fixed-width types). In this particular function, we take an IMAGE for input and an optional LENGTH. A negative LENGTH value causes the DLL to try to compute the length of the input automatically (this is the default):
    CREATE FUNCTION [dbo].[fn_md5x] (@data IMAGE, @len INT = -1) 
    RETURNS CHAR(32) AS
    BEGIN
      DECLARE @hash CHAR(32)
      EXEC master.dbo.xp_md5 @data, @len, @hash OUTPUT
      RETURN @hash
    END

Usage:

FN_MD5

The User-Defined Functions can be used as follows:

-- Sample SQL statement:
-- fn_md5() tries to automatically calculate the length of the input string
SELECT dbo.fn_md5('Hello world!');

-- fn_md5x() takes an optional length arg for substrings, fixed-width types, etc.
SELECT dbo.fn_md5x('Hello world!', 12);

Output for both statements:

86fb269d190d2c85f6e0468ceca42a20

XP_MD5: EXEC xp_md5 <@data> [@length = -1] [@hash OUTPUT]

To use the Extended Stored Procedure directly:

-- Sample command:
EXEC master.dbo.xp_md5 'Hello world!'

Output:

86fb269d190d2c85f6e0468ceca42a20

Or if you want the result saved to a variable instead:

DECLARE @hash CHAR(32)
EXEC master.dbo.xp_md5 'Hello world!', -1, @hash OUTPUT
PRINT @hash

Output:

86fb269d190d2c85f6e0468ceca42a20

Examples

-- These are just examples for use with the given functions above.
-- Feel free to create your own functions that take the input type you want.
-- Use CAST() with caution, as it may truncate and have other unintended consequences.

-- For TEXT, NTEXT, VARCHAR:
SELECT dbo.fn_md5(data) FROM table;

-- For VARBINARY:
SELECT dbo.fn_md5(CAST(data AS VARCHAR(8000))) FROM table;

-- For IMAGE:
SELECT dbo.fn_md5x(data, DEFAULT) FROM table;

-- For CHAR: (we can use LEN() on this fixed-width type to trim the padding)
SELECT dbo.fn_md5x(data, LEN(data)) FROM table;

-- For NCHAR: (we can use LEN() on this fixed-width type to trim the padding)
SELECT dbo.fn_md5x(CAST(data AS CHAR(4000)), LEN(data)) FROM table;

-- For BINARY:
SELECT dbo.fn_md5x(data, 12) FROM table;

-- FOR SQL_VARIANT:
SELECT dbo.fn_md5x(CAST(data AS VARCHAR(8000)), DATALENGTH(data)) FROM table;

Miscellaneous

For purposes of speed, I did not include any real input data verification (e.g., type checking, data length checking, etc.). I opted to exclude that in order to maximize speed, as I originally wrote this for use in an application that inserts millions of rows at a time. I also know that I'm always calling the procedure properly. If you want to make it more robust - like if you do not know what kind of data will be passed to the function - then I highly recommend you add those safeguards.

One last thing, I added the linker option /OPT:NOWIN98 to minimize the binary size. This may cause a performance hit on non-NT systems (e.g., Win95, Win98, etc.). If you're using the DLL on such a system, I would recompile it without that linker option.

Cheers.

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

Vic Mackey
Web Developer
United States United States
No Biography provided

You may also be interested in...

Comments and Discussions

 
GeneralRe: 'Connection broken' error using fn_md5 Pin
Vic Mackey25-Mar-05 9:48
memberVic Mackey25-Mar-05 9:48 
GeneralRe: 'Connection broken' error using fn_md5 Pin
judyflora25-Mar-05 11:09
memberjudyflora25-Mar-05 11:09 
GeneralShould use native CryptoAPI Pin
ashish desai7-Mar-05 3:22
memberashish desai7-Mar-05 3:22 
GeneralRe: Should use native CryptoAPI Pin
Vic Mackey7-Mar-05 3:46
memberVic Mackey7-Mar-05 3:46 
General255 character truncation issue fixed Pin
Vic Mackey7-Mar-05 1:41
memberVic Mackey7-Mar-05 1:41 
QuestionHashing Error? Pin
googootou3-Mar-05 11:07
membergoogootou3-Mar-05 11:07 
AnswerRe: Hashing Error? Pin
Vic Mackey3-Mar-05 15:22
memberVic Mackey3-Mar-05 15:22 
GeneralRe: Hashing Error? Pin
Anonymous4-Mar-05 5:05
sussAnonymous4-Mar-05 5:05 
GeneralFor a more complete article. Pin
yafan1-Mar-05 5:51
sussyafan1-Mar-05 5:51 
GeneralMy mistake - MD5 is not FIPS approved Pin
yafan1-Mar-05 6:15
sussyafan1-Mar-05 6:15 
GeneralUsing for ntext hashing Pin
Anonymous28-Feb-05 23:56
sussAnonymous28-Feb-05 23:56 
GeneralRe: Using for ntext hashing Pin
Vic Mackey1-Mar-05 21:37
memberVic Mackey1-Mar-05 21:37 
GeneralMD5 has a known collision weakness Pin
owillebo28-Feb-05 9:27
memberowillebo28-Feb-05 9:27 
GeneralRe: MD5 has a known collision weakness Pin
Jack Handy28-Feb-05 11:44
memberJack Handy28-Feb-05 11:44 
Do you spend all day googling for "MD5" and post this everywhere? I'm just curious.

-Jack


There are 10 types of people in this world, those that understand binary and those who don't.


Generalxp_md5 on a 64 bit server Pin
Anil Kotla23-Feb-05 9:56
memberAnil Kotla23-Feb-05 9:56 
GeneralRe: xp_md5 on a 64 bit server Pin
Vic Mackey25-Feb-05 9:01
memberVic Mackey25-Feb-05 9:01 
GeneralRe: xp_md5 on a 64 bit server Pin
Anil Kotla25-Feb-05 11:13
memberAnil Kotla25-Feb-05 11:13 
GeneralRe: xp_md5 on a 64 bit server Pin
Vic Mackey27-Feb-05 11:52
memberVic Mackey27-Feb-05 11:52 
GeneralRe: xp_md5 on a 64 bit server Pin
CRINET20-Mar-06 5:56
memberCRINET20-Mar-06 5:56 
GeneralRe: xp_md5 on a 64 bit server Pin
elgransan5-Apr-06 5:31
memberelgransan5-Apr-06 5:31 
QuestionRe: xp_md5 on a 64 bit server Pin
jssastre1-Sep-06 1:53
memberjssastre1-Sep-06 1:53 
QuestionRe: xp_md5 on a 64 bit server Pin
deyvid!william4-Oct-07 4:29
memberdeyvid!william4-Oct-07 4:29 
AnswerRe: xp_md5 on a 64 bit server Pin
Balrok2-Sep-08 11:18
memberBalrok2-Sep-08 11:18 
AnswerRe: xp_md5 on a 64 bit server Pin
thiagosantosleite3-Sep-08 4:22
memberthiagosantosleite3-Sep-08 4:22 
GeneralRe: xp_md5 on a 64 bit server Pin
sameulla16-Sep-08 9:21
membersameulla16-Sep-08 9:21 
GeneralRe: xp_md5 on a 64 bit server Pin
fredrj21-Oct-08 8:13
memberfredrj21-Oct-08 8:13 
GeneralAbout the xp_md5.dll Pin
rahul1816-Jan-05 18:11
memberrahul1816-Jan-05 18:11 
GeneralRe: About the xp_md5.dll Pin
Vic Mackey18-Jan-05 13:31
memberVic Mackey18-Jan-05 13:31 
GeneralCannot use the MD5 hash Pin
arongutan1-Oct-04 8:34
memberarongutan1-Oct-04 8:34 
GeneralRe: Cannot use the MD5 hash Pin
Gerald Schwab1-Oct-04 9:30
memberGerald Schwab1-Oct-04 9:30 
GeneralRe: Cannot use the MD5 hash Pin
arongutan1-Oct-04 12:12
memberarongutan1-Oct-04 12:12 
GeneralRe: Cannot use the MD5 hash Pin
Vic Mackey4-Oct-04 3:25
memberVic Mackey4-Oct-04 3:25 
GeneralRe: Cannot use the MD5 hash Pin
arongutan4-Oct-04 10:36
memberarongutan4-Oct-04 10:36 
GeneralRock On. Pin
um, let me post will ya?4-Feb-04 21:00
memberum, let me post will ya?4-Feb-04 21:00 
GeneralExcellent Pin
zero.sg2-Feb-04 20:39
memberzero.sg2-Feb-04 20:39 
GeneralRe: Excellent Pin
AArnott7-Feb-04 3:16
memberAArnott7-Feb-04 3:16 
GeneralRe: Excellent Pin
zero.sg8-Feb-04 6:44
memberzero.sg8-Feb-04 6:44 
GeneralAbout this kind of things I was talking about... Pin
zero.sg13-Feb-04 23:43
memberzero.sg13-Feb-04 23:43 
GeneralManaged C++ / C# Pin
sinus-c29-Jan-04 5:29
membersinus-c29-Jan-04 5:29 
GeneralRe: Managed C++ / C# Pin
Heath Stewart13-Feb-04 20:54
editorHeath Stewart13-Feb-04 20:54 

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 | Terms of Use | Mobile
Web04 | 2.8.150603.1 | Last Updated 8 Mar 2005
Article Copyright 2004 by Vic Mackey
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid