-1

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.

Luk
  • 1,009
  • 2
  • 15
  • 33
  • You might want to share your `UserDetailsService` and database config. Case sensitivity in this case is mostly about db configuration. – Chaosfire Jan 25 '22 at 17:03
  • What database do you use? MySQL? – dur Jan 25 '22 at 17:27
  • I haven't found the database.config file, on windows it's suppossed to be located at `C:\Program Files\MySQL\MySQL Server 8.0`, but there's nothing there. But simply retrieving an entry from my database usually is case-sensitive, isn't it? – Luk Jan 26 '22 at 07:34
  • 1
    Does this answer your question? [MySQL: is a SELECT statement case sensitive?](https://stackoverflow.com/questions/3936967/mysql-is-a-select-statement-case-sensitive) – dur Jan 26 '22 at 21:55
  • @dur: thx for the link, this is really helpful! Unfortunately, it didn't solve my problem. For some reason, changing the database collation does not take effect. – Luk Jan 27 '22 at 10:56

1 Answers1

0

Use a collation with _cs or _bin. Apply that to the column in question.

A Database's collation is the default for new tables.
A Table's collation is the default for any columns in it for which the charset and collation are not specified.

To find all the available collations:

SHOW COLLATION;
Rick James
  • 135,179
  • 13
  • 127
  • 222