4

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!?

Andrew Curioso
  • 2,181
  • 13
  • 24
DarkKnightFan
  • 1,913
  • 14
  • 42
  • 61
  • 6
    Whoaaaa... **don't store plaintext passwords**! Store instead a *salted* hash so that, in the event your database ever becomes compromised, it is difficult for anyone to discover your users' passwords. See [The definitive guide to forms based website authentication](http://stackoverflow.com/a/477578) for more information. – eggyal Nov 08 '12 at 08:53
  • well that is going to be my next step... first i want to get through this problem.. i want to have a case sensitivity check and get rid of the error mentioned above. – DarkKnightFan Nov 08 '12 at 08:58

4 Answers4

8

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.

eggyal
  • 122,705
  • 18
  • 212
  • 237
  • `USER_PASSWORD=ip_user_password COLLATE 'latin1_general_cs'` worked for this. Next step will encrypt the password. Thanks guys – DarkKnightFan Nov 08 '12 at 09:05
2

I came here after facing the same error. After going through the solutions provided it became apparent that the COLLATION was the trigger for my error. My code was similar to the original question but my error was MySQL said: #1267 - Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='.

I was trying to collect the password and username too (currently no hashing) and I fixed it by explicitly adding the COLLATION for both parameters.

First code (the erratic code):

WHERE user.username = username
AND user.password = password

My current code (the working code):

WHERE user.username = username COLLATE utf8mb4_0900_ai_ci
AND user.password = password COLLATE utf8mb4_0900_ai_ci

Adding a collation only to the password to force a collation match to username or adding only to the username didn't work. Even though one parameter had the right collation to it already, it still had an error. So I added the same collation to both parameters and it worked.

Take note this is the actual COLLATION that is being used in my database already, but for some reason on procedure calls it wasn't consistent.

Please do remember to salt/hash your passwords when you are done, my code is still a work in progress.

Wylie
  • 350
  • 3
  • 12
  • I have one doubt , I am trying to do an update using a select subquery and added something like - CASE flag WHEN 1 THEN 'TRUE' ELSE 'FALSE' END AS flagVal to compare it with the main tables's column(varchar) with this flagVal value, though it worked in my local DB ,but got the exact error you mentioned on a different set-up, is this what is causing the issue as I am suspecting? -as the error was gone when i updated the where clause as per your answer, and if this is the cause then, is there any way to have the CASE -then expression set the correct char set when setting the true/false values? – soumitra goswami May 11 '21 at 12:45
  • I am still relatively new to live SQL and complex queries, I have been using MS Access SQL for a while and never came across this error. But so far it seems the collation gets wonky if its not explicitly set from time to time, some queries run fine, some come with errors. What I understand is it auto derives depending on given inputs instead of checking the database entries and matching that. The way I found to deal with that is setting the collation as per my answer on the inputs, so they match the db collation. If it works, I would say experiment and see what breaks it then roll it back. – Wylie May 12 '21 at 14:39
1

I know it's a little bit late but if this could save someone half a day of swearing it's still worth putting it down.

So, my setup was like this: 10.1.22-MariaDB, utf8mb4_general_ci. All good, I restored a dump of my database, all went OK.

The database was originally in utf8_general_ci, but for some reasons was restored as utf8_unicode_ci. Changed that back to utf8_general_ci and checked there were no artifacts in the database like columns or table definitions collated as utf8_unicode_ci instead of utf8_general_ci

Trying to update a specific table resulted in an illegal mix of collation without any apparent reasons.

It boiled down to be in fact not the table itself but the associated trigger.

In fact the trigger called a procedure that had no collation info in my database, but that had a utf8_unicode_ci collation in information_schema.ROUTINES.DATABASE_COLLATION.

Recreating the procedure in the context of the new database collation solved my issue.

Radacina
  • 431
  • 5
  • 10
1

So after struggling with this error: 

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '='

I managed to fix this problem by changing the import database file from: 

CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci NO SQL

This is if you are importing functions/procedures/triggers, which my database had a ton of all of these features... I changed that to:

CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

I really hope this helps someone. I know the above was helpful, but it still took me a few hours to turn that into a solution. Thanks

Ryan Jarvis
  • 61
  • 9