Shulou(Shulou.com)06/01 Report--

problem

Recently, while completing a practical project with the SSH framework, I encountered an inexplicable Bug that perplexed me for a long time, and finally solved it, as recorded below.

Question: when testing the system, the student suddenly found that the account saved in the database should have been admin, but the student unexpectedly successfully logged in with the Admin account.

…… EXM??? Is that okay? All right, I'd better find out why this Bug happened. Then there is the process of various screening procedures, looking over and over without finding any problems. Finally, I thought that instead of hql, I would write a sql statement and try to query it 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 the same! Go to the search engine to search keywords: MySQL query case, sure enough found the problem! MySQL queries are case-insensitive! This is really amazing, although I know that in general, keywords are not case-sensitive, but I did not expect that even the parameters to be queried are case-insensitive! Try the following sql statement again, and sure enough, the result is the same.

Select * from user where username = 'ADMIN' and password =' admin'

Solution

A related article was found on the Internet, and it was well written. Please post it directly here to explain it:

Mysql's default character retrieval strategy: utf8_general_ci, which means case-insensitive; utf8_general_cs, case-sensitive, and utf8_bin, binary comparison, as well as case-sensitive. (note: in the Mysql5.6.10 version, utf8 genes are not supported! )

When creating a table, directly set the collate property of the table to utf8_general_cs or utf8_bin;. If you have already created the table, modify the Collation property of the field 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;-- modify the Collation attribute ALTER TABLE TABLENAME MODIFY COLUMN COLUMNNAME VARCHAR (50) BINARY CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL of the table structure

Modify the sql statement directly by adding the binary keyword to the field to be queried.

-add the binary keyword select * from user where binary username = 'admin' and binary password =' admin';-- before each condition to surround the parameter with binary ('') select * from user where username like binary ('admin') and password like binary (' admin')

Note: in this project, I used the hibernate framework, not sql, but the hql statement, using from User where binary username =? And binary password =?; the result reported an error, try from User where username like binary (?) again. And password like binary (?); did not report anything wrong. The reason is unknown for the time being.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.