Mysql's MD5 not playing well with encodings

Question

I ran into an interesting problem today. In my user table all fields are latin1_swedish_ci. Passwords are stored as a md5 hash of a user specific salt and their password.

This query results in error:

SELECT * FROM users 
WHERE email = '...' 
AND password = MD5('1234' + salt)

complaining about a #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

When I change the last line to:

AND password = CAST(MD5('1234' + salt) AS CHAR CHARACTER SET latin1)

the query performs just fine.

First I doubted that my input, here '1234', might be confusing the charsets, but I tried simple test selects with only a md5 of salt and no other input, and still the character encoding error remained.

I suppose casting the md5 as latin1 for comparing is one option, another maybe to set the password column to utf8_general_ci. My question however is this:

Why? Why does the md5 return something that seems to be utf8 even if the parts you feed it are in a different encoding?

Answer 1

According to the MySQL documentation:

Some encryption functions return strings of ASCII characters: MD5(), OLD_PASSWORD(), PASSWORD(), SHA(), SHA1(). As of MySQL 5.5.3, their return value is a nonbinary string that has a character set and collation determined by the character_set_connection and collation_connection system variables. Before 5.5.3, these functions return binary strings. The same change was made for SHA2() in MySQL 5.5.6.

In addition to the things you already mention you could change the column definition. Do you really want your password hashes to be sorted and compared according to rules used in Swedish? Perhaps binary or varbinary would be better column types.