Hướng dẫn like binary mysql
Hôm nay được chị tester thông báo 1 bug: Khi user login thì hệ thống không phân biệt username & password dạng chữ hoa, chữ thường 😐 Câu query chỉ đơn giản là: SELECT user_login FROM sys_user WHERE user_login = '$user_login' AND user_passwd = '$password' Hóa ra thằng MySQL nó không phân biệt chữ hoa, chữ thường nếu field type là varchar :-s Cách fix: Có nhiều cách nhưng đơn giản nhất là “ép” về dạng binary để so sánh theo byte SELECT user_login FROM sys_user WHERE BINARY user_login = '$user_login' AND BINARY user_passwd = '$password' Nguồn: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#operator_binary B.3.4.1 Case Sensitivity in String Searches For nonbinary strings ( A comparison between a nonbinary string and binary string is treated as a comparison of binary strings. Simple comparison operations ( The default character set and collation are
If you want a column always to be treated in case-sensitive fashion, declare it with a case-sensitive or binary collation. See Section 13.1.20, “CREATE TABLE Statement”. To cause a case-sensitive comparison of nonbinary strings to be case-insensitive, use
A binary string is case-sensitive in
comparisons. To compare the string as case-insensitive, convert it to a nonbinary string and use
To determine whether a value is compared as a nonbinary or binary string, use the
For binary strings, the collation value is
To check the sort value of a
string, the |