Post 검색가능 사이트

package site.metacoding.dbtest;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;

@EnableJpaAuditing
@SpringBootApplication
public class DbtestApplication {

	public static void main(String[] args) {
		SpringApplication.run(DbtestApplication.class, args);
	}

}

package site.metacoding.dbtest.domain.boardTbl;

import java.time.LocalDateTime;

import javax.persistence.Entity;
import javax.persistence.EntityListeners;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@AllArgsConstructor
@NoArgsConstructor
@Data
@EntityListeners(AuditingEntityListener.class)
@Entity
public class BoardTbl {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String title;
    private String content;

    @CreatedDate
    private LocalDateTime createDate;
}

JPA 네이티브 쿼리에 변수를 바인딩 할 때

홑따옴표로 감싸져 있으면 변수를 인식하지 못한다.

홑따옴표가 들어가면 안 되는 이유를 알기 위해서는

SQL Injection 공격에 대해 공부해야 한다!

package site.metacoding.dbtest.domain.boardTbl;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

public interface BoardTblRepository extends JpaRepository<BoardTbl, Integer> {

    @Query(value = "SELECT * FROM boardtbl WHERE title LIKE %:keyword%", nativeQuery = true)
    List<BoardTbl> mSearch(@Param("keyword") String keyword);
}

package site.metacoding.dbtest.web;

import java.util.List;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;

import lombok.RequiredArgsConstructor;
import site.metacoding.dbtest.domain.boardTbl.BoardTbl;
import site.metacoding.dbtest.domain.boardTbl.BoardTblRepository;

@RequiredArgsConstructor
@Controller
public class BoardTblController {

    private final BoardTblRepository boardTblRepository;

    @GetMapping("/search")
    public String search(Model model, @RequestParam(defaultValue = "") String keyword) { // keyword=스프링

        List<BoardTbl> boards = boardTblRepository.mSearch(keyword);
        model.addAttribute("boards", boards);

        return "home";
    }
}

<!DOCTYPE html>
<html lang="en">

<head>
    <title>빅데이터 플랫폼 아키텍처 설계</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
</head>

<body>

    <div class="container mt-3">

        <form action="/search" class="d-flex justify-content-end" method="get">
            <input name="keyword" class="me-2" type="text" placeholder="Search">
            <button class="btn btn-primary" type="submit">검색</button>
        </form>

        <table class="table table-striped">
            <thead>
                <tr>
                    <th>번호</th>
                    <th>제목</th>
                    <th>내용</th>
                    <th>작성일</th>
                </tr>
            </thead>
            <tbody>
                {{#boards}}
                <tr>
                    <td>{{id}}</td>
                    <td>{{title}}</td>
                    <td>{{content}}</td>
                    <td>{{createDate}}</td>
                </tr>
                {{/boards}}
            </tbody>
        </table>
    </div>

</body>

</html>

저작자표시

티스토리툴바