asked
MD5 conversion for each value in all rows
Hello, I'm new to this forum, I've found it already very helpfull, but now I'm in need of your knowledge. I'm trying to give every emailadress an MD5 code. Problem is that I can do that for one emailadress between single quotes, but not for all emailadresses. When I use this in my select statement CONVERT(VARCHAR(32), HashBytes('MD5',
'mailadress@mail.com'), 2) it works perfect. But when I use CONVERT(VARCHAR(32), HashBytes('MD5', *MyColumn*), 2) it gives another value. Problem are the single quotes. Does anybody have an idea how to solve this? Btw I'm using SQL 2008. Thx a lot
Hello, Thx a lot, I've found it. The problem was that the emailadress was a nvarchar so it gave a different result, when I converted it to a varchar before the hasbytes CONVERT(VARCHAR(32), HashBytes('MD5',CONVERT(varchar(32),t2.emailaddress1) ), 2) it was OK. thx a lot for the answers
If this was the solution, you should mark it by clicking on the check box. If any of the other answers were helpful though, indicate those by clicking on the thumbs up next to them.
If the email address you type into a string matches what's stored in the column, you should get the same hash. Do you single quotes around the email address inside the column? What hash values do you get with the following query: CREATE TABLE #email (email varchar(32) NULL) INSERT #email VALUES (
'mailadress@mail.com') SELECT CONVERT(VARCHAR(32), HashBytes('MD5', email), 2) FROM #email UNION ALL SELECT CONVERT(VARCHAR(32), HashBytes('MD5',
'mailadress@mail.com'), 2) DROP TABLE #email You should get `F5712309C01262583899545A20ED3284` for both rows
If you're getting different hashes, then you're not comparing the same data. Is the data in your table as a char(32) rather than varchar(32)? Or unicode? Or contains traliing / leading spaces? or surrounded by apostrophes / quote marks?
Assuming there is enough room to handle multiple MD5's, each address will use a minimum of 32 bytes plus one byte for a separator, and assuming the multiple email addresses are correctly separated with a semi-colon (;). In other words, you're going to need 32 bytes for each MD5/email address. You'll first need to split the email addresses, encode them separately and place them in the email address column, probably space separated. (any character that will never appear in the MD5 will do.) Next, matching the MD5 will be using a LIKE on that column. Each email address must be it's own MD5 since the hashing outputs a consistent 32 bytes. An alternate would be to add the email MD5 to another table keyed to your primary table. MD5 always outputs 32 bytes regardless of the length of the input string. It is an excellent way of checking your source code, but it is not a very good security measure. You can further enhance the security of the MD5 by adding a salt to the email address such as a User ID. The two together would produce a much more unique string for each address. Personally, I prefer doing an XOR of one over the other before I MD5 hash it.
18 People are following this question.
