mysql binary keyword issue with latin characters

Question

Key in database:- !|%$&./()=?ì^

If I use below query:-

SELECT * FROM `user` WHERE key='!|%$&./()=?ì^' AND Status='1'

This works fine.

But I am checking for case sensitive key(some users using case sensitive keys) with below query:-

SELECT * FROM `user` WHERE BINARY key ='!|%$&./()=?ì^' AND Status='1'

This does not provide any data. On removing ì, this works.

key field collation: latin1_swedish_ci

Note:- I can not change collation because there are too much data in database.

What else I can use in place of BINARY.

Please dont store Plain Text Passwords on yor database. PHP provides [`password_hash()`](http://php.net/manual/en/function.password-hash.php) and [`password_verify()`](http://php.net/manual/en/function.password-verify.php) please use them for the safety of your users. — RiggsFolly, Apr 22 '21 at 08:30
_Small Note just for fun_ I am pretty sure there are no lower case versions of any of those characters — RiggsFolly, Apr 22 '21 at 08:32
yes there is no lower case but in database there is for other users. — Deepak Dholiyan, Apr 22 '21 at 08:33
"This does not provide any data" - please share more details about this. Also, how is tihs related to PHP? — Nico Haase, Apr 22 '21 at 08:45
@NicoHaase I am using php and mysql. What type of data is missing here? — Deepak Dholiyan, Apr 22 '21 at 08:53
You're exposed here, this is bad. You should switch over to Bcrypt via `password_hash`. Normally this is annoying as you have to migrate from one password hash to another, but in your case it's actually pretty straightforward: You have the plaintext passwords. — tadman, Apr 22 '21 at 09:23

score 0 · Accepted Answer · answered Apr 26 '21 at 06:28

0

I find a best answer on the below link:-

use convert as well as collate

New Query:-

SELECT * FROM `user` WHERE key = convert('!|%$&./()=?ì^' using latin1) COLLATE latin1_general_cs AND Status='1'

answered Apr 26 '21 at 06:28

Deepak Dholiyan

1 Answers1