[WebGoat🐐] SQL Injection - mitigation 1

Immutable Queries

Immutable Queries는 SQL 인젝션의 가장 좋은 방어 방법이다. 이번시간엔 Immutable Query에 대해 자세히 알아볼 것이다!

Static Queries

Immutable query에는 static query가 있다! 근데 static query가 무엇인지 정의하는 많은 글들마다 뜻이 다른 것같아서 혼란스러웠다. 보통은 크게 두 가지 개념을 부를 때 사용하는 것 같다.

1. String형 변수에 담지 않고, 코드 사이에 직접 기술한 SQL문을 말함
2. 애플리케이션이 실행될 때는 변하지 않는 쿼리를 말함

예시를 보면 이해가 쉽다. static query의 첫 번째 정의를 이해할 수 있는 예시를 가져와봤다. 애초에 String형 변수에 담지 않고 소스 코드 내에서 SQL을 다룰 수 있는 언어가 많지 않다. 나는 이번에 처음 본 언어인 pro*C 코드 내에서 SQL을 직접 기술한 예시이다.

int main()
{
   printf("사번을 입력하십시오 : ");
   scanf("%d", &empno);
   EXEC SQL WHENAVER NOT FOUND GOTO notfound;
   EXEC SQL SELECT ENAME INTO :ename
            FROM   EMP
            WHERE  EMPNO = :empno;
   printf("사원명 : %s.\n", ename);

notfound:
   printf("%d는 존재하지 않는 사번입니다. \n", empno);
}

말 그대로 소스코드 내에 SQL 구문을 바로 기술 하는 것이다. static 'SQL' 인 것이다.

---

그렇다면 또 다른 사람들이 정의한 static query, 애플리케이션이 실행될 때는 변하지 않는 쿼리는 무엇일까?

What is static SQL? Static SQL is SQL statements in an application that do not change at runtime and, therefore, can be hard-coded into the application.

위의 static sql의 정의 중 hard-coded 될 수 있다는 말이 중요하다. 말그대로 애플리케이션이 만들어질 때 고정된다는 뜻이다. 상수같은 느낌으로 애플리케이션이 실행될 때는 변하지 않는 쿼리이다. 아래 코드는 JAVA에서 hard-coded query를 작성한 예시이다.

 public static void viewTable(Connection con) throws SQLException {
    // hard-coded query statement
    String query = "SELECT last_name, id, hashedPassword, department, salary from EMPOYEES";
    try (Statement stmt = con.createStatement()) {
      ResultSet rs = stmt.executeQuery(query);
      while (rs.next()) { // Get rows from table (EMPLOYEES)
      
        String last_name = rs.getString("last_name");
        String id = rs.getString("id");
        String hasedPassword = rs.getString("hashedPassword");
        String department = rs.getString("department");
        int salary = rs.getInt("salary");
        
        //Print row
        System.out.println(last_name + ", " + id + ", " + hasedPassword + ", " + department + ", " + salary);
      }
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    }
  }

static SQL의 두번째 의미는 그 반대 개념도 중요한데, 그건 바로 dynamic SQL이라고 한다. dynamic SQL은 컴파일 시점에서 static과 달리 실행 중에 쿼리 내용이 바뀔 수 있다. 그렇게 재구성된 쿼리가 실행되는 것이다.

대다수의 프로그램을 만들 때 쿼리문이 사용자의 입력에 따라 달라지는 때가 많기 때문에, dynamic SQL은 정말 많이 사용된다고 보면 된다.

이 때 String 변수를 그냥 더해서(append) 쓰면 SQL Injection의 가능성이 생기므로 Prepared Statement를 사용하는 것을 권고하고 있다.

Prepared Statement (Parameterized Queries)

SQL Injection 취약점에 대한 대응 방안으로 권고되는 유명한 방법 중 하나인 Prepared Statement와 binding 변수에 대해 알아보자.

Prepared Statement는 재사용 시 효율성을 높이기 위해 고안된 query문의 형태라고 한다. 그 원리를 이해하기 위해서는 prepared statement의 workflow를 알아야 한다.

Typical workflow of Using a Prepared Statement

1. Prepare: 처음에 애플리케이션이 statement 'template'을 만들어서 DBMS에 보낸다. statement에 포함되는 어떤 value들은 값을 정해놓지 않고 parameters, placeholder 또는 bind 변수 등으로(아래의 "?" 가 그 예시) 대신 채워넣어 보내는 것이다.
   INSERT INTO products (name, price) VALUES (?, ?);

2. 그 후에 DBMS가 statement template을 컴파일(parsing, optimization, translaiton)을 한다. 그리고 컴파일 결과를 실행은 하지 않고, 나중에 바로 돌릴 수 있게 미리 저장해둔다.

3. Execute: 애플리케이션이 정해지지 않았던 value들의 값을 DBMS에 제공하면! 그때서야 DBMS가 이전에 자신이 저장해두었던 statement의 binding 변수 자리에 value를 넣어 쿼리를 실행한다. 그리고 나서 그 결과를 애플리케이션에게 반환해주는 것이다.

아래 소스코드는 JAVA에서 PreparedStatement 객체를 사용하여 prepared statement를 구현한 예시이다.

// 1. last_name 을 binding 변수로 대신 해놨다.
String query = "SELECT * FROM users WHERE last_name = ?"; 
// 2. DBMS에 넘겨 precompile
PreparedStatement statement = connection.prepareStatement(query); 
// 3. 자료형이 String인 변수이므로 setString()을 이용해 value 전달
statement.setString(1, accoutName); 
// 4. 쿼리 실행 결과 얻기
ResultSet results = statement.executeQuery(); 

PreparedStatement stmt;
ResultSet rs;
StrongBuffer SQLStmt = new StringBuffer();
SQLStmt.append("SELECT ENAME, SAL FROM EMP");
SQLStmt.append("WHERE EMPNO = ?");

stmt = conn.prepareStatement(SQLStmt.toString());
stmt.setLong(1, txtEmpno.value); // 자료형이 Long인 변수이므로 setLong()을 이용해 value 전달
rs = stmt.executeQuery(); // 쿼리 실행 결과 얻기

---

Stored Procedures

Stored Procedures는 DBMS를 다룰 때 사용하는 복잡한 쿼리문을 함수처럼 등록해두고 간편하게 사용할 수 있는 명령이다. 실제 함수처럼 매개변수도 반영할 수 있기 때문에 알아두면 편리한 기능이다.

Safe stored Procedure (Microsoft SQL Server)

공격으로부터 안전한 저장 프로시저의 예시이다.

CREATE PROCEDURE ListCustomers(@Country nvarchar(30))
AS
	SELECT city, COUNT(*)
	FROM customers
	WHERE country LIKE @Country GROUP BY city

EXEC ListCustomers 'USA'

Injectable Stored Procedure (same environment)

인젝션에 취약한 저장 프로시저의 예시다.

CREATE PROCEDURE getUser(@lastName nvarchar(25))
AS
	DECLARE @sql nvarchar(255)
	SET @sql = 'SELECT * FROM users WHERe lastname = + @LastName + '
EXEC sp_executesql @sql

---

이번에는 알아둬야하는 개념들이 많았다!! 공부를 마쳤으니 문제를 풀어보자.

Try it! Writing safe code

You can see some code down below, but the code is incomplte. Complete the code, so that is no longer vulnerable for an SQL injection! Use the classes and methods you have learned before.
The code has to retrieve the status of the user based on the name and the mail address of the user. Both the name and the mail are in the String format.

코드를 보면 conn이라는 참조변수가 DriverManager 클래스의 static 메서드의 반환객체를 참조하는 것 같다. Connection 객체를 반환하고 있음을 이용해 메서드를 찾아보자.

JAVA 도큐먼트를 찾아보니 금방 나왔다.

역시 DriverManager 클래스에 정의된 static 메서드인 getConnection() 이었다. 그래서 첫 번째 빈칸의 답은 getConnection이 되겠다.

다음 코드는 많이 봤던 거다. 위의 PreparedStatement 예제에서 해봤던 대로, Connection 객체의 prepareStatement()라는 메서드를 사용하여 DBMS가 쿼리를 precompile하게 하는 코드이다. 그래서 두번째 빈칸의 정답은 PreparedStatement [참조변수 이름 아무거나] 가 된다. (나는 변수 이름을 ps로 했다.) 그리고 세번째 빈칸은 prepareStatement가 되겠다.

prepareStatement() 메서드의 매개변수로는 우리가 만들고싶은 prepared statement를 binding 변수('?')를 사용해 만든 String 변수이기 때문에 네번째, 다섯번째 빈칸은 물음표를 넣어주면 된다.

문제에서 name과 mail은 String 변수로 저장되어있다고 했으니, binding 변수에 값을 넣어주는 메서드인 getString() 사용해 parameter에 변수명을 넣어주는 코드를 여섯번째, 일곱번째에 작성하면 끝!

---

Try it! Writing Safe Code 2

Now it is time to write your own code! Your task is to use JDBC to connect to a database and request data from it.

Requirements:
1. Connect to a database
2. Perform a query on the datatbase which is immune to SQL injection attacks
3. your query needs to contain at least one String parameter.

두 번째 문제도 비슷한데, 이번에는 빈칸이 아니라 아예 코드를 쌩으로 작성하여 제출하는 문제였다.

그래서 문제에서 시키는 대로 코드를 짜봤다.

try {
    // Connect to a database
    Connection conn = DriverManager.getConnection(DBURL, DBUSER, DBPW);
    // Make a query which is immune to SQL injection attacks
    PreparedStatement pstmt;
    pstmt = conn.prepareStatement("SELECT * FROM users WHERE user_name=?");
    pstmt.setString(1, user_name);
    // Perform a query
    pstmt.execute();
} catch (Exception e) {
    System.out.println("Error");
}

근데 에러가 뜬다. 수업을 들어보니 웹고트 상 문제라서 하드코딩을 해줘야 문제가 풀린다고 했다. 그래서 setString()의 파라미터를 바꿔주니 바로 문제가 풀렸다🤔

끝!

---

이번에는 개념이 되게 어려웠는데 포스팅하면서 제대로 공부할 수 있었던 것 같다! 그에 비해 문제는 너무 쉬웠어서 조금 아쉽기도 하다🙂 피곤하다 피곤해