[SQL AntiPattern] 4부 애플리케이션 개발 안티패턴

SQL 을 효과적으로 활용하기 위해 SQL AntiPattern 의 내용을 정리한다.
4부에서는 애플리케이션에서 SQL 을 다루는 방법에 대해 알아본다.

20장 읽을 수 있는 패스워드: 패스워드를 복구하거나 재설정하기

대부분 사용자가 패스워드를 잊어버린 경우, 이메일 피드백을 통해 패스워드를 복구하거나 재설정하는 기능을 제공한다.

안티패턴: 패스워드를 평문으로 저장하기

패스워드를 평문으로 담은 이메일로 제공해준다면, 심각한 보안 결함이 되고 접근 권한을 제공해줄 수 있기 때문에 위험하다.

• 패스워드 저장
  • 패스워드를 평문으로 저장하거나 평문 상태로 전달하면 안전하지 않음
  • 해커가 네트워크 패킷을 가로채 SQL 문 확인 가능
  • 데이터베이스 서버에서 접근 권한을 제공해줄 수 있음
  • 백업 데이터를 읽을 수 있음
• 패스워드 인증
  • 패스워드가 평문으로 저장되어 있으면 평문으로 비교 수행
  • 공격자에게 패스워드가 노출될 위험이 있음
• 이메일로 패스워드 보내기
  • 이메일은 해커가 가로채거나, 로깅, 저장할 수 있음

사용이 합당한 경우

• 애플리케이션이 다른 서드파티 서비스에 접근해야 하는 경우
• 인트라넷 애플리케이션인 경우

해법: 패스워드의 소금 친 해시 값을 저장한다

• 해시 함수 이해하기
  • 일방향 해시 함수로 패스워드 부호화(encode)
    • ex) SHA-256 알고리즘을 사용하면 문자열 길이가 항상 같음
  • 역을 구하기 어려움
• SQL 에서 해시 사용하기
  • SHA2() 함수를 사용해 해시 값을 생성
  • 해시 함수가 리턴할 수 없는 값(16진수가 아닌 값)으로 변경하면 계정을 잠글 수 있음
    • ex) noaccess
• 해시에 소금 추가하기
  • 소금(salt): 일방향 함수의 입력값에 덧붙이는 랜덤비트
  • 해시 값을 구하기 전에 패스워드에 덧붙이는 무의미한 바이트열임 (8바이트면 충분)
  • 데이터베이스에서 해커가 해시 값과 비교하여 찾을 수 없도록 임의아 값을 추가
• SQL 에서 패스워드 숨기기
  • 네트워크 패킷 또는 SQL 로그 파일을 가로챈다면 패스워드가 노출될 수 있음
  • 애플리케이션 코드에서 해시 값을 계산해 SQL 에서 활용
  • 브라우저와 웹 애플리케이션 서버에서는 HTTPS 같은 보안 프로토콜 사용
• 패스워드 복구가 아닌 패스워드 재설정 사용하기
  • 해시 값이 저장되어 있으므로 복구 불가능
  • 이메일로 패스워드 대신 임시 패스워드를 보냄
    • 짧은 시간이 지나면 만료시킬 수 있음
  • 아이디에 유일한 토큰을 할당하여 이메일로 보내 패스워드 재설정
• 안전한 암호학 기술
  • PBKDF2: 키 강화 표준
  • Bcrypt: 적응성 해시 함수 구현

21장 SQL 인젝션: 동적 SQL 쿼리 작성하기

쿼리 문자열과 애플리케이션 변수를 섞어 SQL 문자열로 만드는 것을 동적 SQL(dynamic SQL) 이라고 한다.
안전하게 동적 SQL 을 만드는 방법에 대해 알아본다.

안티패턴: 검증되지 않은 입력을 코드로 실행하기

SQL 인젝션: 어떤 내용을 SQL 쿼리 문자열에 삽입하여 쿼리의 동작을 의도와 다르게 수정하는 것

• 사고는 발생할 것이다
  • 이름에 ' 이 포함되면 의도하지 않은 형태로 바뀔 수 있음
  • 의도하지 않은 동작이 실행될 수 있음
• 최고의 웹 보안 위협
  • SQL 문이 파싱되기 전에 동적인 부분을 삽입하면 SQL 인젝션 위험이 있음
• 치료를 위한 탐구
  • 값을 이스케이프하기
    • 따옴표 문자를 두 개 또는 역슬래시를 추가하여 문자 리터럴로 변경
    • 문자열 삽입하기 전에 애플리케이션 데이터를 변환
  • 쿼리 파라미터
    • 동적 값 삽입 대신 쿼리를 만들 때 파라미터 자리를 미리 정의하여 실행할 때 값 제공
    • 이스케이프할 필요도 없고 이스케이프 함수의 결함을 고려하지 않아도 됨
    • 보편적인 해결 방법은 될 수 없음
      • 값의 목록을 하나의 파라미터로 전달 불가능
      • 테이블 이름은 파라미터로 전달 불가능
      • 컬럼 이름을 파라미터로 전달 불가능
      • SQL 키뤄드를 파라미터로 전달 불가능
  • 저장 프로시저
    • 보통 고정된 SQL 을 포함하며, 프로시저 정의할 때 파싱
    • 애플리케이션 코드에서 동적 SQL 사용하는 것처럼 위험함
  • 데이터 접근 프레임워크
    • 어떤 프레임워크든 안전한 SQL 코드만 작성하도록 강제 불가능

사용이 합당한 경우

• 있을 수 없음
• 코드를 방어적으로 작성해야 함

해법: 아무도 믿지 마라

• 입력 값 필터링
  • 입력에 대해 유효하지 않은 문자 모두 제거
    • 정수가 필요하다면, 정수에 맞는 내용만 사용
• 파라미터를 통한 값 전달
  • 쿼리의 동적 부분이 단순한 값이라면 SQL 표현과 파라미터 분리 필요
• 동적 값 인용하기
  • 쿼리 파라미터 사용으로 옵티마이저의 인덱스 사용이 잘못될 수 있음
  • 특별한 경우 SQL 문제 해당 값을 직접 삽입하는 것이 나을 수 있음
    • 성숙된 문자열 인용 함수를 사용하여 방어적으로 작성
• 사용자의 입력을 코드와 격리하기
  • 요청 파라미터로 미리 값을 정의하여 SQL 쿼리에 사용
    • ex) 정렬 목록 또는 방향의 리스트를 정의 (up => ASC, down => DESC)
  • 사용자의 입력을 SQL 쿼리와 직접 엮지 않아 안전
  • SQL의 어느 부분이든 동적으로 생성 가능
  • 사용자의 입력이 유효한지 검증 가능
  • 쿼리의 내용을 사용자 인터페이스에서 분리
• 코드 검토를 함께할 동료 구하기
  • SQL 인젝션 위험에 익숙한 동료에게 검사

22장 가상키 편집증: 데이터 정돈하기

수열 중간에 숫자가 몇 개 빠지면 불편한 것은 데이터베이스 기술에 대한 이해나 확인이 부족한 것이 보통이다.

안티패턴: 모든 틈 메우기

• 시퀀스에서 벗어난 번호 할당하기
  • 새로운 행에 키 값을 할당할 때 사용되지 않은 첫번째 값을 사용하고 싶을 수 있음
    • 동시성 문제 발생될 수 있음
  • 비효율적이며 에러가 발생될 수 있음
• 기존 행의 번호를 다시 매기기
  • 기존 행에 대한 키 값을 업데이트해 틈을 메우고 연속적으로 만들 수 있음
    • 사용되지 않은 키 값을 찾아야 함
    • PK 값을 재할당하기 위해 UPDATE 문 실행 필요
    • 동시성 문제 발생될 수 있음
    • 외래키가 있다면 제약조건을 비활성화하고 자식 행까지 업데이트 필요
  • 업데이트해도 디폴트 가상키 생성기를 사용하면 틈이 메워지지 않음
• 데이터 불일치 만들기
  • PK 를 재사용하는 것을 위험함
  • 가상키에 빈 값이 있다고 재할당하면 안됨

사용이 합당한 경우

• 가상키 값을 바꿔야할 이유는 없음
• PK 칼럼의 값이 의미를 가진다면 자연키임

해법: 극복하라

행을 식별하는 데 연속적인 숫자일 필요는 없음

• 행에 번호 매기기
  • 행 번호는 PK 와 대응되는 것이 아님
  • 페이지 처리하는데 유용
  • ROW_NUMBER() 윈도 함수를 사용하면 쿼리 결과 집합에 대한 연속된 번호 확인 가능
• GUID 사용하기
  • GUID(Global Unique Identifier): 128비트 의사난수로 32자리 16진수
  • 장점
    • 중복 걱정없이 동시에 가상키 생성 가능
    • 틈에 대한 불평이 없음
  • 단점
    • 키 값이 길어짐
    • 값이 랜덤하여 패턴 추론이 어렵고, 큰 값이 최근 행을 나타내지 않음
    • 공간도 많이 차지하고 속도도 느림
• 가장 중요한 문제
  • 데이터베이스를 정리를 해결하기 위해서는 커뮤티케이션도 중요
    • 기술 설명
    • 비용을 명확하게 함
    • 자연키 사용하기

23장 나쁜 것 안 보기: 코드를 적게 작성하기

간결한 코드를 작성해야 하는 이유

• 작동하는 애플리케이션 코딩을 빨리 끝낼 수 있음
• 테스트화, 문서화, 동료 검토할 코드 양 감소
• 코드양이 적으면 버그도 적음

안티패턴: 짚 없이 벽돌 만들기

• 진단 없는 진료
  • 함수로부터 리턴된 상태값 체크 필요
    • ex) db 커넥션, 잘못된 쿼리 등
• 읽기간 행
  • SQL 쿼리를 문자열로 생성하는 애플리케이션 코드를 보는 것은 매우 어려움
  • 디버깅할 때, SQL 자체를 보고 판단하는 것이 빠름

사용이 합당한 경우

• 에러에 대해 할 것이 없다면 에러 검사 생략 가능
• 예외를 던져 호출 스택의 위로 전달될 수 있도록 함

해법: 에러에서 우아하게 복구하기

• 리듬 유지하기
  • API 호출 후 리턴 상태나 예외 확인
  • 에러가 발생할 수 있는 모든 호출에 대해 상태 확인
• 스텝 되짚기
  • API 메서드의 인수에서 SQL 쿼리를 만들지 말고 변수를 사용해서 만들기
    • 변수 값 확인할 수 있음
  • SQL 을 따로 출력하도록 함
    • ORM 프레임워크를 사용하면 디버깅이 복잡해질 수 있음
  • SQL 쿼리를 HTML 주석으로 출력하면 안됨
    • 해커가 데이터베이스 구조를 파악할 수 있음

24장 외교적 면책특권: 관례 따르기

좋은 소프트웨어 공학적 습관

• Subversion 이나 Git 같은 버전 관리 도구로 코드 관리
• 자동화된 단위 테스트나 기능 테스트 개발하고 실행
• 요구사항과 구현 전략 기록하기 위해 명세서, 문서, 코드 주석 작성

안티패턴: SQL을 2등 시민으로 만들기

애플리케이션 코드에서 따르는 개발 관례를 데이터베이스 코드에는 면제된다고 생각한다.
이 책에서는 이러한 안티패턴을 ‘외교적 면책특권’이라 한다.
데이터베이스는 애플리케이션의 기초로 품질이 중요하므로 좋은 관례를 따라야 한다.

사용이 합당한 경우

• 한 번 이상 사용할 모든 코드에 대해 문서화, 테스트, 소드 관리를 해야 함
• 임시적인 코드
  • 사용 후에 바로 지우기
  • 최소한 코드의 목적이나 사용방법 정도의 간단한 기록 남기기

해법: 초당적 품질 문화 확립

소프트웨어 공학의 전체 생명주기 수행

1. 프로젝트 요구사항을 문서로 명확하게 명세
2. 요구사항에 대한 솔루션을 설계하고 개발
3. 솔루션이 요구사항에 맞는지 검토하고 테스트

• 증거1: 문서화
  • ERD(Entity-Relationship Diagram)
    • 테이블과 테이블 사이의 관계를 나타내는 ERD
    • 데이터베이스가 복잡하고 많으면 여러 개의 다이어그램으로 분해
  • 테이블, 칼럼, 뷰
    • 색인 테이블, 교차 테이블, 종속 테이블 같은 테이블 정보
    • 컬럼의 값의 의미
    • 뷰를 생성해야 하는 이유, 어디에 사용되는지, 업데이트가 가능 여부
  • 관계
    • 참조 정합성 제약조건의 의도 설명 필요
    • 암묵적 관계인 경우 관계가 있는지조차 파악이 어려움
  • 트리거
    • 구현된 비즈니스 규칙 정보
      • ex) 데이터 검증, 데이터 변환, 데이터베이스 변경 로깅 등
  • 저장 프로시저
    • 해결하고자 하는 문제
    • 데이터 변경 여부
    • 입력과 출력 데이터 타입
  • SQL 보안
    • 애플리케이션에서 사용하기 위해 정의한 계정 정보
    • 각 계정이 가지고 있는 접근 권한, 역할
  • 데이터베이스 기반 구조
    • 사용하는 DBMS 제품 이름 및 버전
    • 데이터베이스 서버 호스트 네임
    • 데이터베이스 서버 개수
    • 리클리케이션, 클러스터, 프록시 사용 여부
    • 커넥션 옵션, 네트워크, 포트, 백업 정책
  • 객체-관계 매핑
    • 구현된 비즈니스 규칙 정보
    • 데이터 유효성 검증, 데이터 변환, 로깅, 캐싱, 프로파일링 기술 방식
• 증거의 흔적: 소스 코드 제어
  • 데이터 정의 스크립트
    • 데이터베이스 객체를 정의하는 SQL 문을 포함한 스크림트 실행 방법 제공
  • 트리거와 프로시저
    • 프로젝트 코드의 일부로 생각하고 관리
  • 부트스트랩 데이터
    • 색인 테이블에 초기 상태가 필요할 수 있으므로 데이터 보관
  • ERD와 문서
    • 데이터베이스 요구사항, 구현, 애플리케이션과의 통합 기술
  • DBA 스크립트
    • 애플리케이션 밖에서 실행되는 데이터 처리 작업 코드도 애플리케이션 코드와 같이 관리
• 입증 책임: 테스트
  • 테이블, 컬럼, 뷰
    • 테이블과 뷰가 실제 존재 여부 확인
  • 제약조건
    • 제약조건 때문에 에러가 발생할 INSERT, UPDATE, DELETE 문 실행 확인
  • 트리거
    • 강제 실행하여 시나리오 테스트
  • 저장 프로시저
    • 단위 테스트와 비슷하게 테스트 실행
  • 부트스트랩 데이터
    • 쿼리를 통해 초기 데이터가 존재하는지 검증
  • 쿼리
    • 테스트 환경에서 쿼리 실행
  • ORM 클래스
    • 애플리케이션과 비슷하게 테스트 필요
• 담당 건수: 여러 브랜치에서 작업하기
  • 이상적으로 애플리케이션 리비전마다 별도의 데이터베이스 인스턴스 생성하는 것이 좋음

25장 마법의 콩: MVC에서 모델 단순화하기

개발 기간을 줄일 수 있다면, 소프트웨어를 개발하는 비용도 줄어든다.
MVC 아키텍처를 사용하면 애플리케이션의 관심사를 분리할 수 있다.

• 컨트롤러
  • 사용자 입력 받기, 응답할 작업 정의, 모델로 작업 위임, 결과를 뷰
• 모델
  • 애플리케이션의 중심
  • 입력 값 검증, 비즈니스 로직, 데이터베이스와 상호작용
• 뷰
  • 사용자 인터페이스에 정보 표시

안티패턴: 액티브 레코드인 모델

액티브 레코드: 모델 객체의 필드를 데이터베이스에 있는 테이블 하나의 컬럼과 매핑하는 디자인 패턴

MVC 애플리케이션에서 모든 모델 클래스가 액티브 레코드 클래스를 상속하는 것은 좋지 않음

• 액티브 레코드는 모델을 스키마와 결합시킨다
  • 액티브 레코드 클래스는 데이터베이스의 테이블 하나 또는 뷰를 표현
  • 새로운 구조의 데이터를 표현하기 위해서는 모델 클래스, 애플리케이션 코드도 함께 수정 필요
• 액티브 레코드는 CRUD 함수를 노출시킨다
  • 의도치 않은 용도로 액티브 레코드 클래스의 CRUD 함수를 사용할 수 있음
• 액티브 레코드는 빈약한 도메인 모델을 조장한다
  • 빈약한 도메인 모델(anemic domain model)
    • 모델을 단순한 데이터 접근 객체로 취급하여 비즈니스 로직이 모델 외부에 존재, 동작 응집도 낮아짐
• 마법의 콩은 단위 테스트가 어렵다
  • 모델 테스트
    • 데이터 접근과 분리해 모델의 동작 테스트가 어려움
    • 데이터베이스 픽스처를 사용하면 데이터베이스가 필요해지고, 준비 작업고 정리 작접이 느려짐
  • 뷰 테스트
    • 렌더링한 후 HTML 을 파싱해 특정 요소를 찾는 코드 실행 필요
  • 컨트롤러 테스트
    • 가짜 HTTP 요청 생성 필요
    • HTTP 응답을 골라내야 함
    • 비즈니스 로직을 테스트하는 데 많은 설정 코드가 필요해지고, 테스트 실행이 느려짐

사용이 합당한 경우

• 액티브 레코드 디자인 패턴은 문제는 없음
• 모델을 DAO 와 동일하게 정의하여 단순화 가능
• 프로토타입(prototype) 만드는 경우
  • 코드를 빨리 작성하는 것이 유지보수 하는 것보다 중요한 경우
  • 기술적 부채를 탕감하기 위한 리팩토링 시간 확보 필요

해법: 액티브 레코드를 가지는 모델

• 모델 이해하기
  • 정보 전문가
    • 어떤 동작에 책임이 있는 객체는 동작을 수행하는 데 필요한 모든 데이터를 가지고 있어야 함
    • 모델과 액티브 레코드와 같은 DAO 사이의 관계는 HAS-A(집합연관) 여야 함
  • 창조자
    • 모델이 데이터를 데이터베이스에 저장하는 방법은 내부 구현 상세여야 함
    • 컨트롤러와 뷰는 데이터베이스 상호작용하는 방법을 알 필요 없음
  • 낮은 결합도
    • 논리적으로 독립적인 코드 블록 분리
    • 클래스의 구현을 변경할 수 있는 유연성
  • 높은 응집도
    • 도메인 모델 클래스의 인터페이스는 의도된 사용법을 반영해야 함
    • 데이터베이스의 물리적 구조나 CRUD 동작을 반영하면 안됨
  • 도메인 모델 동작하게 하기
    • 모델은 애플리케이션을 위한 비즈니스 로직 구현하는 곳
      • 애플리케이션에서 도메인의 객체지향적 표현
• 간단한 객체 테스트하기
  • 모델을 DAO 에서 분리하면 DAO 의 스텁(stub) 과 목(mock)을 생성해 단위 테스트 가능
• 땅으로 내려오기
  • 객체지향 설계 원칙을 공부하면 생산적으로 데이터 접근 객체 사용 가능

부록: 정규화 규칙

관계형의 뜻

관계형(relational) 이라는 용어는 테이블 사이의 관계를 뜻하는 게 아니라,
테이블 자체 또는 테이블과 컬럼 사이의 관계를 뜻한다.

테이블 정규화를 하기 전에 적절한 관계인지 몇 가지 조건을 만족하는지 확인해야 한다.

• 행은 위아래 순서를 가지지 않는다
  • 순서를 고려하지 않는다면 행의 집합은 동일해야 함
• 컬럼은 좌우 순서를 가지지 않는다
  • 컬럼에 대한 위치 의미를 두면 안됨 (19장 암묵적 컬럼과 연관)
• 중복 행은 허용되지 않는다
  • 다른 행과 구분할 수 있어야 하고 개별 행에 접근할 수 있어야 함 (PK 제약 조건)
  • 모든 컬럼은 하나의 타입과 값만을 가져야 함 안티 패턴
    • 안티 패턴 ex) EAV 테이블
• 행은 숨은 컴포넌트를 갖지 않는다
  • 컬럼은 데이터 값을 포함하며, 행 ID나 객체 ID 같은 스토리지 지시자를 갖지 않음
    • 안티 패턴 ex) 가상키 편집증

정규화에 대한 미신

• 정규화는 데이터베이스를 느리게 만듦
  • 쿼리에 대해 엄청 큰 비용이 발생될 수 있음
• 정규화에서는 데이터를 자식 테이블로 분리하고 참조
  • 편의, 성능, 효율성을 위해 사용할 수 있지만 정규화와 관련된 내용은 아님
• 정규화는 EAV 설계에서와 같이 속성을 최대한 분리하는 것
  • 위와 다르게 정규화는 읽기 편하고 쿼리하기 편하게 만들어 줌
• 제 3정규형 이상으로 정규화할 필요는 없음
  • 제 4정규형부터 위배되면 잠재적으로 데이터 손실을 초래할 수 있음

정규화란?

정규화의 목표

• 이해할 수 있는 방법으로 실셰계 표현
• 중복 저장, 데이터 이상 또는 모순 생기는 것을 방지
• 정합성 지원

정규화의 단계

• 제1 정규형
  • 테이블이 관계여야 함
  • 테이블이 반복 그룹을 가지지 않아야 함
    • 한 행이 여러 개의 값을 가지지 않음
  • 안티패턴
    • 무단횡단: 여러 값이 들어가는 경우
    • 다중 칼럼 속성: 같은 도메인의 여러 값이 여러 컬럼에 걸치는 경우
• 제2 정규형
  • 특정 컬럼이 복합 PK 의 일부에 의해 결정되지 않아야 함
    • 완전 함수 종속 만족
• 제3 정규형
  • 복합 PK 일부와 관계 없이 일부 컬럼이 특정 컬럼에 결정되지 않아야 함
    • 이행적 종속 제거
• 보이스-코드 정규형(BCNF, Boyce-Codd Normal Form)
  • 제3 정규형보다 조금 강력한 버전
  • 키 칼럼을 포함한 모든 속성은 키에 종속이어야 함
• 제4 정규형
  • 다치 종속 제거
    • 관계 없는 독립적인 값들이 같은 컬럼에 종속되는 경우 다치 종속
    • ex) 여러 개의 다대다 관계를 하나의 교차 테이블로 나타내려할 때 위반
• 제5 정규형
  • 보이스-코드 정규형의 조건 만족
  • 조인 종속 제거
    • 조인 종속: 분해했다가 다시 조인했을 때 분해하기 전과 동일한 경우
• 도메인-키 정규형(DKNF, Domain-Key Normal Form)
  • 테이블의 모든 제약조건은 테이블 도메인 제약조건과 키 제약조건의 논리적 결과
• 제6 정규형
  • 모든 조인 종속성 제거 추구

관련 코드는 깃허브 참고

출처

• SQL AntiPatterns