- Drupal Projects Are Not Immune To Burnout
- Redfin Family Grows
- Announcing the CashNET module for Ubercart 6.x
- Redfin Solutions in IT World Magazine: The CMS Shootout
- Theming Flashvideo Thumbnails with Imagecache in Views
- DrupalCon Chicago Sessions on Email Marketing and Data Import
- Stashing stuff in $_SESSION in your form's _submit handler in Drupal
- Views "rewrite output" and you wish you had PHP? Don't always resort to customfield!
- Ubuntu 10.4 Based Drupal VM Partition File System ext4 to ext3 Conversion
- Using drupal_execute but it's not working? Load that include!
Replicate MySQL's MD5() Function in SQL Server 2005 Using HASHBYTES()Submitted by patrick on Mon, 03/23/2009 - 17:46MySQL and PostgreSQL's MD5() functions are nice, simple, ways to hash your data. Unfortunately, SQL Server 2005 and up (sorry, in order to get it working in SQL Server 2000, you'll need to try a stored proceedure), you can use the HASHBYTES() function. In a recent SQL Server port of the Actions module for Drupal 5, I ran into the following MySQL query: SELECT aid FROM {actions} WHERE MD5(aid) = '%s'In order to get this working in SQL Server 2005, we have to first use the HASHBYTES() function instead: SELECT aid FROM {actions} WHERE HASHBYTES('MD5',aid) = '%s'This technically does the trick, BUT, the catch is that SQL Server prepends "0x" onto the result hash. I believe this is because it wants to specify the base of the result (in this case, hexadecimal). In my case, I needed to compare this result with a hash that did not have the 0x prefix. To do so, we need to drop the prefix using SUBSTRING(). Since you can't manipulate binary data directly, I used an "undocumented" SQL Server function, sys.fn_sqlvarbasetostr(), to convert the hash to a string. SELECT aid FROM {actions} WHERE SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5',$1)),3,32) = '%s'The 32 in the SUBSTRING function refers to the 32 characters in the MD5 hash. |
Blog Posts |
thnx a lot... u saved me much
thnx a lot... u saved me much time with this solution...
very nice explained ...
Muito bom seu post. Falei um
Muito bom seu post. Falei um pouco sobre ele em http://pantanet.ning.com/profiles/blogs/usando-md5-nativamente-pelo
Thank you! Very helpful.
Thank you! Very helpful.
the result is not the same
the result is not the same compared to function in PHP or MySQL
for example
string : Buchanan
in PHP or MySQL
result : 59d84555ef2cd66b8a2ca56b4d1f0249
in SQL Server HASHBYTES
result : ebec6138ad08a9370753ffaebb931af0
why ?
Are the datatypes you are
Are the datatypes you are using the same? That is, it may be that your string in PHP is different from the "string" in SQL Server.
I agree this could be a
I agree this could be a datatype problem. I found this article when I had different results:
http://weblogs.sqlteam.com/mladenp/archive/2009/04/28/Comparing-SQL-Serv...
I need to add some extra info
I need to add some extra info here as the biggest headache with this is data types:
make sure what you are matching is of the exact same data type.
The below from some testing, I was looking for the hashed value in the database.
The first SQL line I've got 8 harcoded just to see what the hash result looks like, then I pasted it in the 2nd SQL which I use in my actual application to test the URI password reset call - will obviously concatenate a Salt into this.
SELECT SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5','8')),3,32);------
SELECT * FROM acl_auth
WHERE SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5',cast(acl_auth_id as varchar))),3,32) = 'c9f0f895fb98ab9159f51fd0297e236d'
This worked for me, hope it helps someone.
Post new comment