Explanation of the method to solve the case insensitive problem of MySQl query

Problem

Recently, when I completed a practical project with SSH framework, I encountered an inexplicable Bug that puzzled me for a long time and finally solved it. The record is as follows.

Question: When the classmate tested the system, he suddenly found that the account saved in the database should have been admin. As a result, the classmate actually logged in successfully with Admin account...

… … EXM? ? ? Is this okay? Well, I'd better find out why this Bug happened. Then there is the process of various screening procedures, and no problems have been found. Finally, I thought that I didn't need hql, and I wrote sql statements to query directly in the database. As a result, I found the problem:

select * from user where username = 'admin' and password = 'admin';
select * from user where username = 'Admin' and password = 'admin';

Use the above two sql statements to query the table, and the result is actually 1! .....! ! Go to search engine search keywords: MySQL query case, sure enough to find the problem! MySQL queries are case insensitive! This is really stunned me, although I know that under 1 circumstances, keywords are case-insensitive, but I didn't expect that even the parameters to be queried are case-insensitive! ! Try the following sql statement again, and it is still the same result.

select * from user where username = 'ADMIN' and password = 'admin';

Solutions

I found a related article on the Internet, which is quite good. Here, I will post the article directly to explain it:

Mysql default character retrieval policy: utf8_general_ci, which means case-insensitive; utf8_general_cs means case-sensitive, and utf8_bin means binary comparison, which is also case-sensitive. (Note: utf8_genral_cs!!! Is not supported in Mysql version 5.6. 10.)

When creating a table, directly set the collate attribute of the table to utf8_general_cs or utf8_bin; If the table has been created, modify the field's Collation property directly to utf8_general_cs or utf8_bin.

--  Create a table: 
CREATE TABLE testt(
id INT PRIMARY KEY,
name VARCHAR(32) NOT NULL
) ENGINE = INNODB COLLATE =utf8_bin;
--  That modifies the table structure Collation Attribute 
ALTER TABLE TABLENAME MODIFY COLUMN COLUMNNAME VARCHAR(50) BINARY CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL;

Modify the sql statement directly, and add before the field to be queried binary Keyword.

--  In every 1 Add before the condition binary Keyword 
select * from user where binary username = 'admin' and binary password = 'admin';
--  Parameter with the binary('') Surround 
select * from user where username like binary('admin') and password like binary('admin');

Note: In this project, I use the hibernate framework, not sql, but hql statement, using from User where binary username = ? and binary password = ?; The result is reported wrong, try again from User where username like binary(?) and password like binary(?); There was no error. The reason is unknown for the time being.

Summarize