The request was: replace the clear password on a big account database with its MD5 version as a string like the one we get with the md5() PHP function. There are a lot of suggestions on Stack Overflow, but many of them work only with SQLServer 2008 or greater version. Of course before to understand that limit I tried all the tips on my database (ok, not knowing it was a 2005 version).
But in the end there is a simple and definitive and tested (by me) solution to this stupid problem. Here the query to execute to replace a field with its own MD5 has like the one returned by PHP.
update users set password = substring(master.dbo.fn_varbintohexstr(hashbytes('md5', password)), 3, 100)
Of course you should replace the “users” table with your own, and the “password” fields with your own. Pay attention the password field is repeated in two place inside the query.
Hope this can be of help! For who work on SQLServer 2008 or greater there are simpler version of this query using the “convert” T-SQL function.
