Will be presenting at the Seattle Code Camp https://seattle.codecamp.us/ this Saturday.  My talk is entitled "Kick Your Hash" and I will attempt to bridge the cryptography gap between code and theory by showing SQL Server and .NET code, real life examples, and practical correct uses for Hashing.

Example 1:

SELECT HashBytes('MD5','password')

SELECT HashBytes('MD5',CONVERT(varchar(max),'password'))

SELECT HashBytes('MD5',CONVERT(nvarchar(max),'password'))

Example 2a:

ALTER PROC CheckLogin
@Login varchar(50),
@Password varchar(50),
@Valid bit OUTPUT
AS

SET NOCOUNT ON

SELECT *
FROM [User]
WHERE @Login = [User].[Login] AND [User].Hash = 
    HashBytes('MD5',CONVERT(varchar(max),[User].Prefix) + @Password)

IF (@@ROWCOUNT>0)
    SET @Valid = 1
ELSE 
    SET @Valid = 0

Example 2b:

CREATE TABLE [dbo].[User](
    [UserId] [uniqueidentifier] NOT NULL CONSTRAINT [DF_User_UserId]  DEFAULT (newid()),
    [Login] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Hash] [varbinary](16) NOT NULL,
    [Prefix] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
    [UserId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Example 2c:

DECLARE @Prefix uniqueidentifier
SET @Prefix = NewId()

INSERT INTO [User]
(
    [Login],
    Hash,
    Prefix )
VALUES(
    'wayne',
    HashBytes('MD5',CONVERT(varchar(max),@Prefix) + 'password'),
    @Prefix)

Example 3:

CREATE PROC ChangePassword
@Login varchar(50),
@OldPassword varchar(50),
@NewPassword varchar(50)
AS

UPDATE [User]
SET Hash = HashBytes('MD5',CONVERT(varchar(max),[User].Prefix) + @NewPassword)
WHERE @Login = [User].[Login] AND [User].Hash = 
    HashBytes('MD5',CONVERT(varchar(max),[User].Prefix) + @OldPassword)

Example 4a:

CREATE PROC AddData
@Data varbinary(max)
AS

INSERT INTO Data
(
    Data,
    Hash,
    [Size]
)
VALUES
(
    @Data,
    HashBytes('MD5',@Data),
    DATALENGTH(@Data)
)

Example 4b:

CREATE PROC FindData
@Data varbinary(max),
@Id uniqueidentifier OUTPUT
AS

DECLARE @Hash varbinary(16)
SET @Hash = HashBytes('MD5',@Data)

DECLARE @Length bigint
SET @Length = DATALENGTH(@Data)

SELECT @Id = DataId
FROM Data
WHERE @Hash = Hash AND @Length = Size

{6230289B-5BEE-409e-932A-2F01FA407A92}