Transferring Cryptography from Python to TSQL

Transact-SQL
1

Recently I found out how to check my password against the one stored for a user using Python. This works exactly as I want it to:

import base64
import hashlib
salt = "1c183f8f-cc58-495c-b793-43e44439e79a"
password = "my_password"
m = hashlib.md5()
m.update(salt.encode() + password.encode())
print(m.digest())

That results in this output:

b'\xd9\xecI\xa2\xac%\x82!Q\xec\x98\xde\xf9b\x1c\x0b'

The one problem is that I also need to work with a language that doesn't have the same tools (MD5 & base64) so I need to move the heavy lifting to the MSSQL Side. However, when I attempt the same thing here (with both Varchar & Nvarchar):

SELECT HASHBYTES('MD5',  '1c183f8f-cc58-495c-b793-43e44439e79a'+'my_password'), 'varchar'
UNION
SELECT HASHBYTES('MD5', N'1c183f8f-cc58-495c-b793-43e44439e79a'+N'my_password'), 'nvarchar'

The output looks like this:

0xD9EC49A2AC25822151EC98DEF9621C0B  varchar
0x85AABEB6B209C53FAB7A015EE7344B7D  nvarchar

What am I missing? The one difference seems to be that Python requires it to be a Byte as an input. I'm not sure if that's what's impacting the results.

2

Actually

b'\xd9\xecI\xa2\xac%\x82!Q\xec\x98\xde\xf9b\x1c\x0b'

in hex, is

0xD9EC49A2AC25822151EC98DEF9621C0B
3

Thanks! That made all of the difference.

4

For anyone interested this is the stored procedure. I don't think you'll need to do exactly the same thing, but parts might come in handy.

USE SC EXEC uspVerifyCredentials @VerifyUser ='user@company.com', @VerifyPass='swordfish'



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE uspVerifyCredentials
    @VerifyUser NVARCHAR(max)
	,@VerifyPass VARCHAR(32)
AS
BEGIN
    SET NOCOUNT ON;
	DECLARE @VerifySalt [varchar](50) = (SELECT [Salt] FROM [DB].[dbo].[User_Table] WHERE Email=@VerifyUser)
    DECLARE @HashedPass VARBINARY(20) = HASHBYTES('MD5',  @VerifySalt + @VerifyPass)
	DECLARE @Base64ofHash VARCHAR(32) = CAST(N'' as xml).value('xs:base64Binary(xs:hexBinary(sql:variable("@HashedPass")))', 'VARCHAR(32)')
    DECLARE @OriginPass VARCHAR(MAX) = (SELECT [Password] FROM [DB].[dbo].[User_Table] WHERE Email=@VerifyUser)
	IF(@Base64ofHash = @OriginPass)
	    SELECT '1' as 'Access'
    ELSE
	    SELECT '0' as 'Access'
END
GO