How to solve the problem of case insensitivity in MySQL queries

question

Recently, when I was completing a practical project using the SSH framework, I encountered an inexplicable bug that bothered me for a long time. Finally, I solved it and recorded it as follows.

Question: When testing the system, a student suddenly discovered that the account saved in the database should have been admin, but the student successfully logged in with the Admin account...

...EXM? ? ? Is this ok? Well, I'd better find out the cause of this bug. Then I went through various troubleshooting procedures, but I couldn't find any problems. Finally, I thought of writing SQL statements to query the database directly instead of HQL, and I found the problem:

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

Using the above two SQL statements to query the table separately, the results are surprisingly the same! ……! ! Go to the search engine and search for the keywords: MySQL query case, and you will find the problem! MySQL queries are not case sensitive! This really shocked me. Although I knew that keywords are generally not case-sensitive, I didn't expect that even the parameters to be queried are not case-sensitive! ! Try the following SQL statement again, and the result is still the same.

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

Solution

I searched for a related article on the Internet, which is well written. I will paste the article explanation here:

The default character search strategy of Mysql is: utf8_general_ci, which means case-insensitive; utf8_general_cs means case-sensitive; utf8_bin means binary comparison, which is also case-sensitive. (Note: In MySQL 5.6.10, utf8_genral_cs is not supported! ! ! !)

When creating a table, directly set the collate property of the table to utf8_general_cs or utf8_bin; if the table has already been created, directly modify the Collation property of the field to utf8_general_cs or utf8_bin.

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

Simply modify the SQL statement and add the binary keyword before the field to be queried.

-- Add the binary keyword before each condition select * from user where binary username = 'admin' and binary password = 'admin';
-- Surround the parameters with binary('') select * from user where username like binary('admin') and password like binary('admin');

Note: In my project, the hibernate framework is used, and the hql statement is not sql. The from User where binary username = ? and binary password = ?; results in an error. Then try from User where username like binary(?) and password like binary(?); without an error. The reason is currently unknown.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links