Categories
Databases

MS SQL and MD5

SQL Server doesn’t appear to have a nice MD5 function like MySQL does (shame), but there is still a way to do it.

DECLARE @value VARCHAR(255)
SET @value = "This is an MD5 encoded string'
SELECT SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', @value)), 3, 32)

This was useful for resetting the admin password within an instance of Moodle that had recently been migrated to SQL Server.

UPDATE [moodle].[dbo].[mdl_user] SET [password] = SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', 'newpassword')), 3, 32) WHERE [username] = 'admin'