[SPRING] ch02. SQL Injection - 방어

* 에러 페이지를 준비해서, DB 관련 오류 메세지가 브라우저에 노출되지 않도록 한다.a

/WEB-INF/web.xml (수정)

 <error-page>
        <error-code>500</error-code>
        <location>/error</location>
    </error-page>
</web-app>

 

 

MainController.java

 

package secure.ch02.ex01.controller;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

@Controller("ch02.ex01.mainController")
public class MainController {    
    @RequestMapping("/error")
    public String error(){
        return "ch02/ex01/error";
    }
}

 

 

/WEB-INF/views/ch02/ex01/error.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
에러가 발생했습니다. <br>
<p><a href="/secure">메인으로</a></p>

 

실험

로그인 화면 ID란에 ' 입력 -> 에러 화면 출력, 화면에 더 이상 에러 메세지를 노출하지 않는다.

--

 

* STATIC SQL을 사용한다.

/WEB-INF/spring-servlet.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:mvc="http://www.springframework.org/schema/mvc"
    xmlns:mybatis="http://mybatis.org/schema/mybatis-spring"    
    xsi:schemaLocation="http://www.springframework.org/schema/mvc 
        http://www.springframework.org/schema/mvc/spring-mvc.xsd        
        http://www.springframework.org/schema/beans 
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context 
        http://www.springframework.org/schema/context/spring-context.xsd
        http://mybatis.org/schema/mybatis-spring
        http://mybatis.org/schema/mybatis-spring.xsd">
    <!-- ch01 -->
    <context:component-scan base-package="secure" />
    <mvc:annotation-driven />
    <bean
        class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <property name="prefix" value="/WEB-INF/views/" />
        <property name="suffix" value=".jsp" />
    </bean>
    
    <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
        <property name="jndiName" value="java:comp/env/jdbc/secure" />
        <property name="resourceRef" value="true" />
    </bean>
    
    <!-- ch02 -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
    </bean> 
    <mybatis:scan base-package="secure.**.dao.mapper" />    
</beans>

 

 

User.java

 

package secure.ch02.ex01.domain;

public class User {
    private String userId;
    private String userPw;
    
    public String getUserId() {
        return userId;
    }
    public void setUserId(String userId) {
        this.userId = userId;
    }
    public String getUserPw() {
        return userPw;
    }
    public void setUserPw(String userPw) {
        this.userPw = userPw;
    }
}

 

LoginMapper.java

 

package secure.ch02.ex01.dao.mapper;

import org.apache.ibatis.annotations.Select;
import secure.ch02.ex01.domain.User;

public interface LoginMapper {
    @Select("select count(user_id) from users where user_id=#{userId} and user_pw=#{userPw}")
    int getUserCnt(User user);
}

 

LoginDao.java

 

package secure.ch02.ex01.dao;

import secure.ch02.ex01.domain.User;

public interface LoginDao {
    public int getUserCnt(User user) throws Exception;
}

 

 

 

LoginDaoImpl.java

 

package secure.ch02.ex01.dao;

import java.sql.SQLException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import secure.ch02.ex01.domain.User;
import secure.ch02.ex01.dao.mapper.LoginMapper;

@Repository("ch02.ex01.loginDao")
public class LoginDaoImpl implements LoginDao {
    @Autowired private LoginMapper loginMapper;
    
    public int getUserCnt(User user) throws SQLException{
        return loginMapper.getUserCnt(user);
    }
}

 

LoginService.java

 

package secure.ch02.ex01.service;

import secure.ch02.ex01.domain.User;

public interface LoginService {
    public boolean authUser(User user) throws Exception;
}

 

 

LoginServiceImpl.java

package secure.ch02.ex01.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import secure.ch02.ex01.dao.LoginDao;
import secure.ch02.ex01.domain.User;

@Service("ch02.ex01.loginService")
public class LoginServiceImpl implements LoginService{
    @Autowired private LoginDao loginDao;
    
    public boolean authUser(User user) throws Exception{
        return loginDao.getUserCnt(user)>0;
    }
}

 

 

LoginController.java

 

package secure.ch02.ex01.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import secure.ch02.ex01.domain.User;
import secure.ch02.ex01.service.LoginService;

@Controller("ch02.ex01.loginController")
@RequestMapping("/ch02/ex01/login")
public class LoginController {
    @Autowired private LoginService loginService;    
    
    @RequestMapping(method=RequestMethod.GET)
    public String login(){
        return "ch02/ex01/loginIn";
    }
    
    @RequestMapping(method=RequestMethod.POST)    
    public String authUser(User user, Model model) throws Exception{
        model.addAttribute("isLogin", loginService.authUser(user));
        return "ch02/ex01/loginOut";
    }
}

 

 

 

/WEB-INF/views/ch02/ex01/loginIn.jsp

 

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<title>로그인</title>
<form method="post">
    <input type="text" name="userId" placeholder="아이디"><br>
    <input type="text" name="userPw" placeholder="암호"><br><br>
    <button type="submit">제출</button>
</form>
<p><a href="/secure">메인으로</a></p>

 

 

/WEB-INF/views/ch02/ex01/loginOut.jsp

 

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<title>로그인 결과</title>
${isLogin?"로그인 성공":"로그인 실패"}
<p><a href="xxxxxxxxxxxxjavascript:history.back();">돌아가기</a></p>

 

 

실험

로그인 화면에서, 아이디에 ' or 1=1 -- 를 입력한 뒤 제출하면, 인증 우회되지 않고, 에러 화면으로 이동한다.

--

 

SQLFilter.java

 

package secure.ch02.ex02.controller;

import java.util.regex.Pattern;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

@Controller
@RequestMapping("/ch02/ex02/sql")
public class SQLFilter {
    @RequestMapping(method=RequestMethod.GET)
    public void main(){}
    
    @RequestMapping(method=RequestMethod.POST)
    public void main(String inStr, Model model){
        Pattern pattern = Pattern.compile("['\\-#();=*/+]");
        inStr = pattern.matcher(inStr).replaceAll("");        
        
        inStr = inStr.toLowerCase();
        inStr = inStr.replaceAll("union", "q-union")
                .replaceAll("select", "q-select")
                .replaceAll("insert", "q-insert")
                .replaceAll("update", "q-update")
                .replaceAll("delete", "q-delete")
                .replaceAll("drop", "q-drop")
                .replaceAll("and", "q-and")
                .replaceAll("or", "q-or")
                .replaceAll("join", "q-join")
                .replaceAll("substr", "q-substr")
                .replaceAll("where", "q-where")
                .replaceAll("declare", "q-declare")                
                .replaceAll("user_tables", "q-user_tables")
                .replaceAll("user_tab_columns", "q-user_tab_columns")
                .replaceAll("table_name", "q-table_name")
                .replaceAll("column_name", "q-column_name")
                .replaceAll("row_num", "q-row_num");
        model.addAttribute("inStr", inStr);
        System.out.println("ch02.ex02: " + inStr);
    }
}

 

 

/WEB-INF/views/ch02/ex02/sql.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<form method="post">
    <input type="text" size="50" name="inStr"><br><br>
    <button type="submit">제출</button>
</form>
<br>
${inStr}

 

 

실험

입력란에, select * from users where 1=1 -- 를 입력한 뒤, 제출한다.