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}