Spring Boot: How to find name by a case sensitive parameter in a MySQL database

I make simple database of names. There are only three names: Adam , Arnold and Matin. And I want to get all names that contains letter “a”. I have in my repository this method: Let String letter = “a”;

@Query(value = "SELECT * FROM person WHERE name LIKE %:letter%", nativeQuery = true)
    List<PersonEntity> findByNameLike( @Param("letter") String letter);

It returns all names(List of names), because it find some “a” or “A” in all of names. But I want to find only names that contains exactly lower case “a”. This query works in the Workbech wery well: SELECT * FROM person WHERE name LIKE BINARY '%a%'; But this code returns empty(null) List.

@Query(value = "SELECT * FROM person WHERE   name  LIKE BINARY '%:letter%'", nativeQuery = true)
    List<PersonEntity> findByNameLike( @Param("letter") String letter);

I dont know how to link the variable letter to the query. Thank you for any ideas. PS:How to make SQLinjection protection in this case if the variable letter is wrapped in “%” ?

Answer

With

@Query(value = "SELECT * FROM person WHERE   name  LIKE BINARY '%:letter%'", nativeQuery = true)
    List<PersonEntity> findByNameLike( @Param("letter") String letter);

you are looking for names that contain the String ‘:letter’, try

@Query(value = "SELECT * FROM person WHERE   name  LIKE BINARY CONCAT('%',:letter,'%')", nativeQuery = true)
    List<PersonEntity> findByNameLike( @Param("letter") String letter);

And using Query-annotation there is no chance of SQL-injection.

By using the right collation you decide on table creation wether queries are case sensitive or case insensitive, see Are UNIQUE indices case sensitive in MySQL?