Home>

java - cannot search by entering "%" or "_" in the search box

【problem】
We are creating a search function based on pagination in Spring.
When you put the characters "%" and "_" in the search box,
"%" and "_" are not searched, and all search results are acquired.

I think it's probably due to being identified as an escape character,
I'm not sure how to correct the code.
Please tell me to someone.

【progress】
・ JPA is used to obtain data from the DB, and it is possible to display without problems other than the escape character in question.
・ Pagination coding is also over, and I found [problem] at the final verification stage.

[Language/Tool]
・Java
・MYSQL
・Spring Boot
・STS (spring tools site)

【Source code】
Reference code
・TaskController(Controller)
・TaskRepository(Repository)
*Reference code will be added according to the answer

TaskController(Controller)

@Service
@Transactional
public class TaskService {
    @Autowired
    private TaskRepository taskrepository;
    @Autowired
    private TaskSpecification taskspecification;
      public Page<TaskEntity>searchmaterial(String title, String titleKana,Pageable pageable) {
        return (Page<TaskEntity>) taskrepository.findAll(Specification
                .where(taskspecification.titleContains(title))
                .and(taskspecification.titleKanaContains(titleKana))
                ,pageable
                );
    }
}


TaskRepository(Repository)

@Repository
public interface TaskRepository extends JpaRepository<TaskEntity,Long>, JpaSpecificationExecutor<TaskEntity>{
        Page<TaskEntity>findByTitleAndTitleKana(String title, String titleKana, Pageable pageable);

}
java mysql springboot charactercode search
  • Answer # 1

    ★★★ Note ★★★

    As pointed out by xebme, allowing "%" and "_" in the user input risks SQL injection. I haven't scrutinized that the links below are appropriate, but I hope you find them helpful.

    Complete SQL injection protection – yohgaki's blog

    "Let's use placeholders anyway" does not eliminate the vulnerability.

    Java to be worried about, but I can't end it. Spring Data JPA JPQL injection countermeasures

    Please understand the following as an answer to the need to use it in a limited place where you are aware of such risks and do not disclose it to the outside.

    [First answer]
    I feel that the method name is not for LIKE search.

    [Spring Data JPA] Naming rules for automatically implemented methods-Per Qiita, isn't it helpful?

    [Addition]

    Since TaskService is calling taskrepository.findAll(), it seems that it needs to be modified to use the method additionally defined in TaskRepository.

    [Additional Part 2]

    However, regarding the advice I received at the beginning "I need to modify it so that the method defined additionally in TaskRepository is used", I do not know how to change taskrepository.findAll().

    Below, I have not confirmed the operation. Please check the operation when corrected as follows.

    @Service
@Transactional
public class TaskService {
    @Autowired
    private TaskRepository taskrepository;
    @Autowired
    private TaskSpecification taskspecification;
      public Page<TaskEntity>searchmaterial(String title, String titleKana,Pageable pageable) {
        return taskrepository.findByTitleAndTitleKana(title, titleKana, pageable);
    }
}

    However, with this modification, the partial match search in the input that does not use "%" and "_" can be used.
    xebme

    In addition, I have to check the search conditions and use LIKE search if there is %_, and normal search if there is %_, and it seems that there is also a design defect here.

    As you can see, you will need a conditional branch.

  • Answer # 2

    Yasumichi
    xebme
    Thank you for your polite answer.
    I tried to correct it according to the contents you received, but it seems that it can be solved using org.apache.commons.lang3.StringUtils, so by replacing the escape characters "%" "_" with StringUtils, the problem is solved did.
    I'm really thankful to you.

Related articles