[DB] Schema, Index, SQL Injection

Schema

---

Schema는 데이터베이스의 구조와 제약 조건을 정의하는 메타데이터의 집합이다. 스키마는 데이터베이스의 구성 요소들(예: 테이블, 열, 인덱스 등)에 대한 정의와 이들 간의 관계를 나타내며, DBMS이 데이터를 저장, 관리, 조회하는 방식을 규정한다. 스키마는 데이터베이스의 논리적 설계와 관련이 있으며, 데이터베이스 사용자가 데이터와 상호작용하는 방식을 결정하는 중요한 요소이다.

 

Schema의 3계층

스키마는 일반적으로 ANSI/SPARC 모델의 three-schema architecture에 따라 외부 스키마, 개념 스키마, 내부 스키마로 구분된다. 이 구조는 데이터베이스 시스템에서 데이터의 물리적 저장과 논리적 표현을 분리하고, 데이터의 관리를 효율적으로 수행할 수 있도록 돕는다.

 

1. External Schema(외부 스키마)

정의: 외부 스키마는 특정 사용자나 응용 프로그램이 데이터베이스를 바라보는 논리적 관점을 정의한다. 이 계층은 사용자가 필요로 하는 데이터와 그 데이터의 형식을 정의하며, 사용자마다 서로 다른 외부 스키마를 가질 수 있다.

특징:여러 개의 외부 스키마가 존재할 수 있으며, 이는 다양한 사용자나 애플리케이션의 요구를 반영한다.각 사용자에게 필요한 데이터와 그 데이터의 접근 방법만을 포함하며, 사용자가 데이터베이스의 물리적 구조나 내부 작동 방식에 대해 알 필요가 없다.

예: 고객 관리 시스템에서 판매 팀은 고객의 주문 정보에만 접근할 수 있지만, 마케팅 팀은 고객의 프로필 정보에만 접근할 수 있다. 각각의 팀은 자신들의 작업에 필요한 데이터에 대해서만 외부 스키마를 가진다.

 

 

2. Conceptual Schema(개념 스키마)

정의: 개념 스키마는 데이터베이스의 전체 구조를 논리적으로 표현한 것이다. 이는 데이터베이스 내 모든 데이터와 그들 간의 관계를 정의하며, 모든 외부 스키마를 통합한 전체적인 데이터베이스 구조를 나타낸다.

특징:데이터베이스의 논리적 설계를 담당하며, 데이터베이스의 조직, 제약 조건, 무결성 규칙 등을 정의한다.하나의 개념 스키마만 존재하며, 이는 데이터베이스 관리자가 관리하는 데이터베이스의 중앙 설계이다.사용자의 요구가 변하거나 새로운 요구 사항이 생겨도, 개념 스키마가 변화하면 외부 스키마에 영향을 주지 않고 데이터베이스 구조를 조정할 수 있다.

예: 개념 스키마에는 고객, 주문, 제품 등의 엔티티와 이들 간의 관계(예: 고객이 여러 주문을 할 수 있다) 등이 포함될 수 있다.

 

3. Internal Schema(내부 스키마)

정의: 내부 스키마는 데이터베이스의 물리적 저장 구조를 정의한다. 이는 개념 스키마를 실제 물리적 저장 장치에 어떻게 구현할지를 설명하며, 저장될 데이터의 형식, 인덱스, 물리적 순서 등을 나타낸다.

특징:데이터가 물리적으로 어떻게 저장되고, 인덱싱되며, 데이터베이스 시스템에서 어떻게 접근되는지를 규정한다.저장 장치의 물리적 속성과 관련된 모든 사항을 정의하며, 이를 통해 데이터베이스의 효율적인 저장과 접근을 보장한다.내부 스키마는 데이터베이스의 성능에 큰 영향을 미치는 중요한 요소이다.

예: 내부 스키마는 특정 테이블이 어떤 파일 형식으로 저장될지, 인덱스는 어떻게 구성될지, 데이터 접근 경로는 어떻게 설정될지 등을 정의한다.

 

Three-Schema Architecture의 주요 목적

•  다양한 사용자 요구 지원: 서로 다른 사용자나 애플리케이션이 동일한 데이터베이스를 사용하면서도 각자의 필요에 맞는 뷰를 제공받을 수 있도록 한다.
• 독립적인 구조 변경: 데이터베이스의 물리적 구조나 개념적 구조가 변경되더라도, 외부 스키마와 내부 스키마 간의 상호 독립성을 유지하여 사용자의 작업에 영향을 주지 않는다.
• 데이터베이스의 유연성 및 확장성: 데이터베이스의 논리적 설계를 물리적 구현과 분리함으로써, 데이터베이스의 구조적 변경이나 최적화 작업이 용이해진다.
• 데이터 일관성 유지: 여러 외부 스키마에서 동일한 데이터를 사용할 때, 데이터의 일관성을 유지하고 접근 방식을 표준화할 수 있다.

 

 

Table Full Scan과 Index Range Scan

---

1. Table Full Scan

• 정의: Table Full Scan은 데이터베이스에서 특정 테이블의 모든 행을 순차적으로 읽어들이는 방식이다. 이는 인덱스를 사용하지 않고, 테이블의 처음부터 끝까지 모든 데이터를 조회하는 방법이다.
• 특징:
• 테이블의 모든 행을 읽기 때문에, 특히 큰 테이블에서 성능이 저하될 수 있다.
• WHERE 조건이 없거나, 인덱스를 사용할 수 없는 경우 주로 발생한다.
• 테이블에 인덱스가 있더라도, 인덱스가 없는 컬럼에 대한 검색이 필요할 때 Table Full Scan이 발생할 수 있다.

 

2. Index Range Scan

• 정의: Index Range Scan은 인덱스를 사용하여 특정 범위의 데이터를 검색하는 방식이다. 인덱스를 통해 데이터를 효율적으로 조회할 수 있으며, 일반적으로 성능이 Table Full Scan보다 훨씬 뛰어나다.
• 특징 WHERE 조건에서 사용된 컬럼이 인덱스로 설정되어 있을 때, 그 인덱스를 타고 데이터베이스가 데이터를 검색한다.
  • 특정 범위의 데이터를 조회할 때 사용되며, 인덱스의 순서를 이용하여 필요한 데이터만 읽어들이므로 성능이 뛰어나다. 

 

인덱스를 타는 쿼리임에도 Table Full Scan 방식으로 동작하는 경우

인덱스를 사용하는 것이 예상되는 쿼리임에도 불구하고, 실제로는 Table Full Scan 방식으로 동작하는 경우가 있다. 이는 여러 가지 이유로 발생할 수 있다

• 인덱스 선택 기준: 옵티마이저는 인덱스를 사용할 때 예상되는 I/O 비용을 계산한다. 만약 인덱스를 사용하는 것보다 Table Full Scan이 더 효율적이라고 판단되면, 옵티마이저는 Table Full Scan을 선택한다.
  • 예를 들어, 쿼리가 반환해야 할 데이터의 비율이 전체 테이블의 20~30% 이상이라면, 인덱스를 사용하기보다 Table Full Scan이 더 빠를 수 있다.
• 인덱스 부적합: 인덱스가 설정된 컬럼이 WHERE 절에 사용되었으나, 함수나 연산이 적용된 경우 인덱스를 사용할 수 없다.
  • 예: WHERE UPPER(name) = 'JOHN'과 같이 인덱스가 적용된 name 컬럼에 함수가 적용되면, 인덱스를 사용하지 않고 Table Full Scan이 발생할 수 있다.
• 힌트(Hint) 사용: 개발자가 인덱스를 사용하도록 강제하거나, 반대로 인덱스를 무시하고 Table Full Scan을 강제하는 힌트를 사용했을 경우, 옵티마이저가 이를 따르게 된다.
  • 힌트는 옵티마이저의 기본 동작을 무시할 수 있다.

 

COUNT 쿼리의 동작 방식

COUNT 함수는 데이터베이스에서 특정 조건을 만족하는 행의 수를 계산하는 데 사용된다.

COUNT 함수의 동작 방식은 사용된 인수에 따라 다르다

 

COUNT(*)

• 정의: 테이블의 모든 행을 카운트하며, NULL을 포함한 모든 행이 계산된다.
• 특징
  • 가장 일반적으로 사용되며, 테이블의 전체 행 수를 계산할 때 사용된다.
  • NULL 값을 포함한 모든 행을 계산하므로 성능이 매우 효율적이다.
  • 내부적으로 인덱스를 사용하지 않고 Table Full Scan을 통해 전체 행을 카운트할 수 있다.

COUNT(1)

• 정의: 테이블의 모든 행을 카운트하지만, 인수로 전달된 상수 1이 모든 행에 대해 평가되므로 사실상 COUNT(*)와 동일하게 동작한다.
• 특징:
  • 일부에서는 COUNT(1)이 COUNT(*)보다 빠르다고 잘못 이해하는 경우가 있으나, 실제로는 거의 동일하게 동작한다.
  • 일반적으로 COUNT(*)와 동일한 방법으로 처리되며, 최적화 측면에서도 차이가 없다.

COUNT(column)

• 정의: 특정 컬럼의 값이 NULL이 아닌 행의 수를 카운트한다.
• 특징
  • 해당 컬럼이 NULL 값을 가진 행은 제외되며, 실제로 값이 존재하는 행만 계산된다.
  • 인덱스가 설정된 컬럼에 대해 COUNT(column)을 실행하면, 인덱스만을 사용하여 더 빠르게 결과를 반환할 수 있다.
  • 만약 해당 컬럼에 인덱스가 없을 경우, 전체 테이블을 스캔해야 할 수도 있다.

COUNT 쿼리의 동작 과정 차이

COUNT(*)는 테이블의 모든 행을 포함하여 NULL 값도 카운트하며, 주로 전체 행의 개수를 빠르게 계산할 때 사용된다.

COUNT(1)은 COUNT(*)와 기능적으로 동일하며, 테이블의 전체 행을 카운트하는데 사용된다. 성능상의 차이는 없다.

COUNT(column)은 특정 컬럼의 값이 NULL이 아닌 행만 카운트하며, NULL 값을 제외한 실제 데이터를 기준으로 결과를 반환한다. 컬럼에 인덱스가 있다면 인덱스를 사용하여 빠르게 카운트할 수 있다.

 

 

---

 

SQL Injection

SQL Injection은 데이터베이스를 사용하는 웹 애플리케이션을 공격하는 데 사용되는 코드 인젝션 기법이다. 공격자는 사용자 입력 필드에 악의적인 SQL 구문을 삽입하여, 데이터베이스 명령어를 조작하고 불법적으로 데이터를 열람하거나 변경할 수 있는 취약점을 악용한다. SQL Injection은 데이터베이스 보안에 있어 심각한 위협으로, 사용자 신원 도용, 데이터 변조, 관리자 권한 탈취, 데이터 삭제, 시스템 데이터 노출 등의 치명적인 공격을 야기할 수 있다.

 

SQL Injection의 공격 유형

에러 기반 SQL 인젝션 (Error-based SQL Injection)

• 정의: 에러 기반 SQL 인젝션은 데이터베이스 서버에서 발생한 오류 메시지를 통해 공격자가 데이터베이스 구조나 쿼리 실행 결과에 대한 정보를 얻어내는 기법이다.
• 설명: 공격자는 잘못된 SQL 구문을 의도적으로 삽입하여 데이터베이스에서 오류 메시지를 반환하도록 유도하고, 이를 통해 데이터베이스 구조를 추측하거나 중요한 정보를 탈취한다.
• 예시: 예를 들어, 로그인 폼에 OR 1=1 --와 같은 악성 입력을 삽입하여 항상 참이 되는 조건을 만들고, 뒤따르는 SQL 명령어를 주석 처리하여 무단으로 시스템에 접근할 수 있게 한다.

UNION 기반 SQL 인젝션 (Union-based SQL Injection)

• 정의: UNION 기반 SQL 인젝션은 SQL의 UNION 연산자를 이용해 원래의 쿼리 결과에 추가적인 데이터를 합쳐서 반환하도록 하는 공격 기법이다.
• 설명: 공격자는 두 개 이상의 SELECT 쿼리를 결합하여, 원래 의도된 결과에 추가적인 데이터베이스 정보를 덧붙여 서버가 이를 반환하도록 만든다. 이를 통해 데이터베이스의 구조나 데이터를 탈취할 수 있다.
• 예시: 상품 ID를 입력받는 곳에 -99 UNION ALL SELECT version(), database(), 2--와 같은 쿼리를 삽입하여, 상품 정보 대신 데이터베이스 버전 및 이름 등의 민감한 정보를 획득한다.

블라인드 SQL 인젝션 (Blind SQL Injection)

• 정의: 블라인드 SQL 인젝션은 웹 애플리케이션이 에러 메시지나 데이터를 직접 반환하지 않는 경우에도 공격자가 SQL 쿼리의 참/거짓 조건을 이용하여 데이터베이스 정보를 유추하는 기법이다.
• 설명: 공격자는 조건이 참인지 거짓인지에 따라 서버의 응답 시간이 달라지거나, HTTP 응답 내용이 달라지는 점을 이용하여 정보를 알아낸다. 이러한 방식은 다시 불린 기반과 시간 기반으로 나뉜다.
• 예시: 데이터베이스 이름의 길이를 알아내기 위해 AND LENGTH(database())=3과 같은 쿼리를 삽입한 뒤, 응답의 변화를 관찰하여 정보를 유추한다.

DB 라이브러리가 이를 해결하는 방법

 

• Prepared Statements (준비된 구문) 사용
  • 정의: Prepared Statement는 SQL 쿼리와 입력 데이터를 분리하여 처리하는 방법이다. SQL 쿼리의 구조를 미리 정의하고, 사용자 입력은 별도의 매개변수로 전달되어 쿼리 실행 시 동적으로 바인딩된다.
  • 설명: Prepared Statement를 사용하면 데이터베이스가 쿼리의 코드와 데이터를 구분하여 처리하므로, 악성 SQL 구문이 포함되더라도 쿼리의 구조가 변경되지 않는다. 이로 인해 SQL Injection 공격이 불가능해진다.
  • 예시: Java의 PreparedStatement, Python의 cursor.execute()와 같은 함수들이 Prepared Statement를 구현한 예이다.
• Stored Procedures (저장 프로시저) 사용
  • 정의: Stored Procedure는 미리 데이터베이스에 저장된 SQL 쿼리 집합으로, 재사용 및 공유가 가능한 SQL 코드 블록이다. 이를 통해 SQL 쿼리의 실행과정을 캡슐화할 수 있다.
  • 설명: 저장 프로시저는 SQL 쿼리와 입력 매개변수를 명확하게 분리하여 실행하므로, SQL Injection 공격에 비교적 안전하다. 다만, 모든 Stored Procedure가 자동으로 안전한 것은 아니므로, 사용 시 주의가 필요하다.
  • 예시: CALL sp_getAccountBalance(?)와 같이, 매개변수에 대해 명확하게 정의된 Stored Procedure를 호출하여 데이터베이스와 상호작용한다.
• 입력 유효성 검사 및 허용 목록(Whitelist) 적용
  • 정의: 사용자가 입력한 데이터가 예상된 형식과 범위 내에 있는지 확인하고, 예상된 범위를 벗어난 값은 거부하거나 수정하는 방법이다.
  • 설명: 입력값을 검증하여 SQL 구문을 삽입할 수 없도록 하며, 허용된 값만을 입력받도록 제한함으로써 SQL Injection을 방지한다. 특히, 특정한 형식의 입력만 받아야 할 경우 유효성 검사를 통해 악의적인 입력을 사전에 차단할 수 있다.
  • 예시: 사용자가 입력한 값이 숫자인지, 특정 길이를 초과하지 않는지, 예상된 값인지 등을 검증하는 코드 작성.
• ORM (Object-Relational Mapping) 사용
  • 정의: ORM은 객체지향 프로그래밍 언어의 객체를 데이터베이스의 테이블과 매핑하여, 객체를 통해 데이터베이스 작업을 수행할 수 있게 하는 기법이다.
  • 설명: ORM을 사용하면 직접 SQL 쿼리를 작성하지 않고, 객체를 통해 데이터베이스에 접근하게 된다. ORM이 내부적으로 SQL 쿼리를 생성할 때 SQL Injection 방지 기능이 내장되어 있다.
  • 예시: Django ORM, Hibernate, SQLAlchemy 등 다양한 ORM 라이브러리가 SQL Injection 방지 기능을 제공한다.