mysql: error code [1267]; Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='

Question

I want to make the password column of my User table to be case sensitive in mysql.

Following is the description of the table:

/*Table: mst_user*/

   FIELD          TYPE          COLLATION        
-------------  ------------  -----------------
user_id        VARCHAR(100)  latin1_swedish_ci
first_name     VARCHAR(25)   latin1_swedish_ci
last_name      VARCHAR(25)   latin1_swedish_ci
USER_PASSWORD  VARCHAR(50)   latin1_swedish_ci
user_status    INT(11)       (NULL)           
version_id     INT(11)       (NULL)           
active_status  INT(11)       (NULL)           
user_type      INT(11)       (NULL)    

To make the USER_PASSWORD field case sensitive I executed following query:

ALTER TABLE `mst_user` MODIFY `USER_PASSWORD` VARCHAR(50) COLLATE `latin1_general_cs`;

This worked and the field is now case sensitive.

But I have a store procedure which executes a SELECT query on this table to check if the user exists for the given credentials.

Stored Proc::

CREATE PROCEDURE `usp_password_verify`(ip_login_id         VARCHAR(200),
                                 ip_user_password    VARCHAR(200),
                                INOUT success     INT(1),
INOUT tbl_usr_password          VARCHAR(100),
INOUT  pkg_user_password         VARCHAR(100))
BEGIN
  SELECT COUNT(*)
    INTO success
    FROM mst_user
   WHERE UPPER (user_id) = UPPER (ip_login_id)
   AND USER_PASSWORD=ip_user_password;

   SET tbl_usr_password = '';
   SET pkg_user_password= '';
END$$

When I call this stored proc from my java code I am getting the following error:

**error code [1267]; Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='**

Can anyone help what is wrong with this? Something that works as a simple query gives error while executing it in a stored proc!?

Answer 1

As documented under Collation of Expressions:

MySQL assigns coercibility values as follows:

[ deletia ]

• The collation of a column or a stored routine parameter or local variable has a coercibility of 2.

[ deletia ]

MySQL uses coercibility values with the following rules to resolve ambiguities:

[ deletia ]

• If both sides have the same coercibility, then:

  • If both sides are Unicode, or both sides are not Unicode, it is an error.

You could add an explicit COLLATE clause in your expression to force one of the operands to have an explicit collation with a lower coercibility value:

USER_PASSWORD=ip_user_password COLLATE 'latin1_general_cs'

You might even want to consider latin1_bin in this case?

In any event, you should not be storing passwords in plaintext. Instead, store salted hashes of your users' passwords and simply verify that the hash matches that which is stored.