How to select an entry from a MySQL database by searching for a case-sensitive username

90
January 27, 2022, at 9:30 PM

I have set up a MySQL database and created a login table using MySQL Workbench. I am using a MySQL 8.0 server.

The login table consists of the simple entry:

id    username    password
1     MyUser      somePassword

When performing EITHER of the following SQL Queries, the single entry of my login table is retrieved:

SELECT * FROM my_database_schema.login WHERE username="MyUser";
SELECT * FROM my_database_schema.login WHERE username="myuser";

I only want the query to be successfull, however, if username="MyUser". In other words: I want my SQL query to be case-sensitive.

As has been pointed out in the comments, this issue appears to be related to the database collation. I have, therefore, tried out one of the answers to the question linked in the comments.

I have performed the following query to check my current collation:

show variables like '%collation%';

This query yields the following answer:

Variable_name                    Value
collation_connection             utf8mb4_0900_ai_ci
collation_database               utf8mb4_0900_ai_ci
collation_server                 utf8mb4_0900_ai_ci
default_collation_for_utf8mb4    utf8mb4_0900_ai_ci  

As this is not a case-sensitive collation, I know that I have to change this, for example to utf8mb4_0900_as_cs.

In order to achieve this, I have performed the next two queries:

-- Change database collation
ALTER DATABASE `my_database_schema` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;
-- Change table collation
ALTER TABLE `login` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;

MySQL workbench lets me know that these queries have been successfull. Still, when performing the first query, i.e. checking for the database collation again, I obtain the same result as before: I am using utf8mb4_0900_ai_ci.

Can somebody help me understand this?

=========================================================== Searching for more possible answers in the linked question, I have tried out the following query:

SELECT * FROM my_database_schema.login WHERE username= BINARY "MyUser";

And that's doing the trick! This query will only be successfull, if the username is given in the correct case-sensitive way.

However, I don't know how to convert this query into a HQL statement, i.e. a query that's understood by Hibernate. At the moment, I am using the following statement:

@Query("SELECT u FROM User u WHERE u.username=?1")

, where User is the class representing my login table.

READ ALSO
Content/map is undefined

Content/map is undefined

Let's say I have this:

86
springboot mvc log request body string(in Filter) before RestController

springboot mvc log request body string(in Filter) before RestController

I know basics of springboot mvc but don't well understand how the underlying classes work(such as Servlet, Intercepters, Filters)Searched but didn't find answer

66
How can I capture the error thrown by a python script in the bash script calling it?

How can I capture the error thrown by a python script in the bash script calling it?

I want to write a bash script that sends a message depending on whether a python script it called ran successfully or threw an error

68
Can I get original page be different than the AMP?

Can I get original page be different than the AMP?

So I am using IFRAME on my pagesThe code usually goes like this

73