[SPRING] ch01.SQL Injection - 공격

* oracle DB 에 secure/secure 계정 생성.

* 테이블 및 데이터 생성

create table users(
user_id varchar2(10) primary key,
user_pw varchar2(10),
reg_date date);

insert into users values('admin','admin',sysdate);
insert into users values('user','user',sysdate);

commit;

* eclipse에 eGovFrame project 생성.

* app 개발

pom.xml

...    
    <repositories>
        <!-- 추가 -->
        <repository>
            <id>oracle</id>
            <name>Oracle JDBC repository</name>
            <url>http://maven.jahia.org/maven2</url>
        </repository> 
...
    <dependencies>
        <!-- 추가 -->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>12.1.0.1</version>
        </dependency>  
...

src/main/resources/log4j2.xml

src/main/resources/log4j2.xml
<?xml version="1.0" encoding="UTF-8"?>
<Configuration>
    <Appenders>
        <Console name="console" target="SYSTEM_OUT">
            <PatternLayout />
        </Console>        
        <RollingFile name="file" fileName="c:/DEV/secure.log"
            filePattern="c:/DEV/secure.%d{yyyy-MM-dd-HH-mm-ss}.log"
            append="true">
            <PatternLayout pattern="[%-5p: %d{yyyy/MM/dd HH:mm:ss}] %m%n" />
            <Policies>                
                <TimeBasedTriggeringPolicy interval="86400"/>
            </Policies>
        </RollingFile>
    </Appenders>
    
    <Loggers>        
        <Root level="info">
            <AppenderRef ref="console" />
            <AppenderRef ref="file" />                                    
        </Root>
    </Loggers>
</Configuration>

/META-INF/context.xml

<?xml version="1.0" encoding="UTF-8"?>
<Context>
    <WatchedResource>WEB-INF/web.xml</WatchedResource>    
    <Resource name="jdbc/secure"  
           auth="Container"             
           type="javax.sql.DataSource"  
           username="secure"              
           password="secure"              
           driverClassName="oracle.jdbc.driver.OracleDriver"
           factory="org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory"
           url="jdbc:oracle:thin:@127.0.0.1:1521:xe" 
           maxActive="2" 
           minIdle="2"/>       
</Context>

 

/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>
</beans>

/WEB-INF/web.xml

 

 

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 
        http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
    <servlet>
        <servlet-name>spring</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>        
    </servlet>
        
    <servlet-mapping>
        <servlet-name>spring</servlet-name>
        <url-pattern>/</url-pattern>
    </servlet-mapping>

    <filter>
        <filter-name>encodingFilter</filter-name>
        <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
        <init-param>
            <param-name>encoding</param-name>   
            <param-value>UTF-8</param-value>
        </init-param>
        <init-param>
            <param-name>forceEncoding</param-name>  
            <param-value>true</param-value>
        </init-param>
    </filter>    

    <filter-mapping>
        <filter-name>encodingFilter</filter-name>
        <url-pattern>/*</url-pattern>                 
    </filter-mapping>
</web-app>

 

User.java

package secure.ch01.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;
    }
}

DBConn.java

package secure.ch01.ex01.dao;

import java.sql.Connection;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.springframework.stereotype.Component;

@Component
public class DBConn {
    private DataSource dataSource;
    
    public DBConn() throws NamingException{
        Context initContext = new InitialContext();
        Context envContext  = (Context) initContext.lookup("java:/comp/env");
        dataSource = (DataSource) envContext.lookup("jdbc/secure");
    }
    
    public Connection getConn(){
        Connection conn = null;
        try{
            conn = dataSource.getConnection();
        }catch(Exception e){
            e.printStackTrace();
        }
        return conn;
    }
}

 

LoginDao.java

package secure.ch01.ex01.dao;

import secure.ch01.ex01.domain.User;

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

 

LoginDaoImpl.java

package secure.ch01.ex01.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import secure.ch01.ex01.domain.User;

@Repository
public class LoginDaoImpl implements LoginDao {
    @Autowired private DBConn dbConn;
    
    public int getUserCnt(User user) throws SQLException{
        String sql = "select count(user_id) from users ";
        sql += "where user_id='"+user.getUserId()+"' ";
        sql += "and user_pw='"+user.getUserPw()+"' ";
        System.out.println(sql);
        
        Connection conn = dbConn.getConn();
        Statement stmt = null;            
        ResultSet rs = null;
        int userCnt = 0;        
        stmt = conn.createStatement();            
        rs = stmt.executeQuery(sql);
        if(rs.next()){
            userCnt = rs.getInt(1);
        }
        if(rs != null) try{rs.close();}catch(SQLException e){}
        if(stmt != null) try{stmt.close();}catch(SQLException e){}
        if(conn != null) try{conn.close();}catch(SQLException e){}
        
        return userCnt;        
    }
}

 

LoginService.java

package secure.ch01.ex01.service;

import secure.ch01.ex01.domain.User;

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

 

LoginServiceImpl.java

package secure.ch01.ex01.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import secure.ch01.ex01.dao.LoginDao;
import secure.ch01.ex01.domain.User;

@Service
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.ch01.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.ch01.ex01.domain.User;
import secure.ch01.ex01.service.LoginService;

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

 

MainController.java

package secure.ch01.ex01.controller;

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

@Controller
public class MainController {
    @RequestMapping("/")
    public String main(){
        return "ch01/ex01/main";
    }
}

 

/WEB-INF/views/ch01/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/ch01/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>

 

/WEB-INF/views/ch01/ex01/main.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<title>보안</title>
<a href="ch01/ex01/login">SQL Injection - 공격</a>

 

* 실험
user/user 로 정상 로그인한다.
user id에 ' 를 입력해서 제출 했더니, 에러 메세지가 그대로 노출된다. 보안 취약 사이트로 판명한다.

인증 우회
1. 아이디/암호에 ' or 'a'='a / ' or 'a'='a 를 입력한다.
2. 아이디에 ' or 1=1 -- 를 입력한다.
3. 아이디에 admin' -- 를 입력한다. 이 아이디는 회원가입 시 중복 아이디로 찾은 것이다.