SQL 안티패턴, SQL Injection 공격

SQL 안티패턴

1. 무단횡단(제이워크)

1개의 컬럼에 컴마 구분의 값을 넣으면 안된다.

• 검색하기 어렵다.
• 문자열 칼럼의 문자 수 제한이라는 암묵적 제약을 받는다.
• validation을 걸기 어렵다.
• 해결방법은 교차 테이블을 생성하는 것

 

 

2. 순진한 트리

 트리형태의 계층 구조를 1개의 테이블로 표현하면 안된다. 그 이유는 다음과 같다.

• 계층 구조가 깊어지면 그만큼 SQL을 써야한다.
• 노드를 삭제하기 어려워진다.

해결방법은 대체 트리 모델을 사용하는 것이다.

 

 

3. 아이디가 필요해

 모든 테이블에 id를 만드는 것을 만들 필요없다. 그 이유는 다음과 같다.

• 장황해질 가능성이 있다.
• 아무 생각없이 id 칼럼을 만들면 논리적 일관성 문제가 생길 수 있다.
• 시퀀스로 생성된 id == 주키가 아니다.

 해결책은 다음과 같다.

• ORM에 의해서는 서로게이트 키의 이용을 전제하는 경우도 있기 때문에 그에 따라 만드는 것이 좋다.
• 주키는 명확한 이름을 붙인다.
• 규약에 얽매이지 않는다.
• 유연한 복합 키를 사용한다.

 

4. 키가 없는 엔트리

 외부키를 제약을 사용하지 않는 패턴도 좋지 않다. 그 이유는 다음과 같다.

• 참고 정합성을 어플리케이션에서 재구현할 필요가 있다.
• 손상된 참고 정합성을 어떻게 할지에 대한 문제가 발생한다.
• 애드 호크 SQL에 손상되기 쉽다. 

 해결책은 단순히 외부 키를 사용하면 된다.

 

 

5. 엔터티-속성-값

 가변 속성을 표현하기 위해 attr_name와 attr_value를 가진 테이블을 작성하는 것도 안티패턴이다. 그 이유는 다음과 같다.

(※ NULL 제약을 하지 않는다)

• 참조 정합성을 강제할 수 없다.
• OUTER JOIN을 사용하므로 느리다.

 해결책은 다음과 같다.

• 스키마리스(Schema-less) 데이터는 RDB 이외의 것을 사용한다.
• 싱글 테이블 상속, 구상 테이블 상속, 클래스 테이블 상속, 반구조화 데이터

 

6. 다형성 연관

 하나에 모델에 대해서 여러 개의 모델을 관련 짓기 위해 type과 id 항목을 가지는 패턴. 이유는 다음과 같다.

• 참조 정합성이 담보되지 않는다.
• 외부 결합이 발생한다.

해결책은 다음과 같다.

• 교차 테이블을 만든다.
• 공통의 부모 테이블을 만든다.

 

7. 다중 컬럼 속성

 하나의 엔티티에 여러 개의 값(예를 들어, 하나의 포스트에 대해 여러 개의 태그를 붙이는 등)을 저장하고 싶은 경우에 tag1, tag2, tag3과 같은 칼럼을 만들어 옆 방향으로 대응하는 경우도 안티패턴이다. 이유는 다음과 같다.

• 검색하기 어렵다.
• 갱신하기도 어렵다.
• 일관성을 보증을 할 수 없다.
• 사전에 정의했던 열의 숫자가 부족할 가능성이 있다.

 해결방법은 종속 테이블을 만드는 것이다.

 

 

8. 메타데이터 트리블

 테이블이나 열을 복사하거나, 데이터에 의존하여 테이블을 작성하는 패턴도 안 된다. 그 이유는 다음과 같다.

• 열의 수가 많은 테이블을 생성하거나, 열의 수가 적은 여러 개의 테이블을 만들어야한다.
• 새로운 데이터를 위해 새로운 테이블을 만들어야한다.
• 테이블 간의 정합성을 얻기 어렵다.
• 참고 정합성을 얻기 어렵다 (주키를 설정할 수 없다).

 해결법은 다음과 같다.

• 행으로 분리하는 수평 파티셔닝(SQL 표준에서는 없이만 각 종 DB 각각 독자적인 방법으로 서포트하고 있다)
• 열로 분리하는 수직 파티셔닝(특히 BLOB나 TEXT가 유효.  BLOB나 TEXT으로 SELECT * 로 데이터를 획득하는 방법은 느리다)
• 종속 테이블의 도입

 

9. 반올림 오류

 데이터 베이스에서 소수점을 다루고 싶은 때 FLOAT형을 사용하는 패턴은 안티패턴이다. 그 이유는 다음과 같다.

• 부동소수점의 반올림으로 인해 오차가 발생하여 계산이 맞지 않게 된다.

해결책은 다음과 같다.

• NUMERIC형, 혹은 DECIMAL형을 사용한다.
• FLOAT는 어림수로 다뤄야한다.

 

10. 31가지 맛

 열에 대입할 값을 한정하기 위해 CHECK 제약을 이용하고 싶지만, 한정할 값을 열 정의로 작성하면 좋지 않다. 그 이유는 다음과 같다.

• 값의 폐지가 어렵다.
• CHECK 제약, 도메인, 유저형은 각 종 데이터 베이스 제품에서 사양상 통일되어 있지 않다.

 해결책은 다음과 같다.

• 참조 테이블을 만들어 외부 키 제약을 하는 것으로 열에 넣을 값을 제약한다. 단순한 SQL로 표현할 수 있으므로 관리가 편하다.

 

11. 유령 파일

 이미지와 같이 대용량 파일을 저장할 때 링크만을 데이터 베이스에 저장하여 파일 실체는 외부 스토리지에 저장하는 패턴도 안티패턴이다. 그 이유는 다음과 같다.

• 레코드가 삭제된다고 해도 실제 파일까지 삭제된다고 보증할 수 없다 (외부 스토리지에 쓰레기 데이터가 남는다).
• 롤백으로 원래대로 돌아오지 않는다.
• 백업 툴이 서포트되지 않으므로 복원한 데이터와 이미지를 엮는 처리를 자기가 써야할 필요가 있다.
• 외부 파일은 SQL의 액세스 권한 영향을 받지 않는다. 

 해결법은 다음과 같다.

• 필요에 따라 BLOB 형을 검토한다.

 

12. 인덱스 샷건

 인덱스에 대한 지식 부족으로 틀린 방법으로 인덱스를 사용하는 것으로 예를 들면 다음과 같은 경우다.

• 인덱스를 완전히 사용하지 않는다.
• 테이블의 모든 열을 합쳐서 인덱스를 붙인다.
• 인덱스가 듣지 않는 쿼리를 발행한다.

 안되는 이유는 다음과 같다.

• 퍼포먼스가 안 좋아진다.

 해결책은 다음과 같다.

MENTOR 체크 리스트를 바탕으로 고찰(검증)한다.

• Meature
• Explain
• Nominate
• Test
• Optimize
• Rebuild

 

13. 모르는 것에 대한 두려움

 NULL을 포함한 열에 대해서 쿼리를 쓸 때에, NULL을 일반값으로 다루거나 혹은 일반 값을 NULL로 다루는 패턴. 이유는 다음과 같다.

• NULL을 포함한 연산은 모두 결과가 NULL이 된다.
• NULL은 비교할 수 없다.
• NULL공포증에 빠져 NULL로 다뤄야할 값을 실체값으로 다룬다고 해도 사태는 나아지지 않는다.

 해결방안은 다음과 같다.

• NULL을 임의의 값으로 다룬다.
• 스칼라 값, 논리식으로 NULL의 연산 결과를 이해한다(직관과 다른 경우가 있다).
• 검색은 IS NULL을 사용한다.
• 열에 NOT NULL 제약을 부여한다. 어플리케이션 코드에 의존하지 않고, 데이터 베이스에서 일관된 제약을 강제한다.

 

14. 애매한 그룹

 그룹 내의 최대값이 발견된 레코드의 다른 속성도 가져오려고 하는 경우. 안되는 이유는 다음과 같다.

• GROUP BY로 열거되지 않은 열에 값이 일관되게 정해진다고 확정할 수 없다.

해결법

• 함수 종속성이 있는 열에만 쿼리를 실행한다.
• 상관 서브 쿼리를 사용한다.
• 도출 테이블를 사용한다.
• JOIN
• 다른 열에 대해서도 집계함수를 이용한다.
• 그룹마다 모든 값을 연계

 

15. 임의의 선택

 rand()를 이용하여 랜덤 정렬을 하여 맨처음의 행을 fetch하는 것 또한 안티패턴이다. 이유는 다음과 같다.

• 인덱스가 듣지 않는다.
• 테이블 전체를 스캔했지만 맨 처음 몇 개의 행만 사용하므로 퍼포먼스가 낮아진다.

 해결책은 다음과 같다.

• id가 1부터 최대값까지의 사이 값을 랜덤으로 선택한다 (id가 연속된 경우에 한정)
• 결번 뒤에 있는 키 값을 선택한다.
• id의 리스트를 한번 전체 획득하여, 어플리케이션 쪽에서 랜덤 선택하여 한 번 더 SELECT 쿼리를 발행한다.
• 오프셋을 사용하여 랜덤 선택지로 한다.
• 벤더 의존 sample 함수를 사용한다.

 

16. 가난한 자의 검색 엔진

LIKE나 정규표현을 이용한 패턴 매치는 안티패턴이다. 그 이유는 다음과 같다.

• 인덱스의 효과가 없어지고 테이블 스캔이 된다.
• 단순한 패턴 매치로 인해 의도하지 않은 매치가 발생한다.

 해결 방법은 다음과 같다.

• 전문 검색 엔진을 이용한다.
• 벤더 확장으로 전문 검색 엔진을 이용한다. MySQL이라면 풀 텍스트 인덱스를 사용하면 된다.
• Sphinx나 Lucene(ElasticSearch)등, SQL과 독립하여 움직이는 검색 엔진을 활용한다.
• 전치 인덱스를 자체 제작한다.

 

17. 스파게티 쿼리

 복잡한 문제를 하나의 SQL로 해결하려고 하는 것도 좋지 않다. 이유는 다음과 같다.

• 메인터넌스가 어렵다.
• 결합이나 상관 서브 쿼리등과 같은 까다로운 SQL 쿼리는 일반적으로 간단한 쿼리에 비해 느리다. 

 해결 방법은 다음과 같다.

• 분할 통치
• 쿼리를 여러 개로 분할하여 원스텝씩 실행한다.
• 각각 사양에 따른 레벨로 태스크를 분할하여 처리한다.

 

18. 암묵적 컬럼

 SQL를 짧게 하기 위해서 SELECT *를 이용하는 것은 좋지 않다. 좋지 않은 이유는 다음과 같다.

• 열의 추가, 삭제, 이름 변경등을 하면 쿼리 결과에 의해 발생한 변화를 코드쪽에서 제대로 다루지 않게 된다(칼럼에는 실제로는 순서가 있어, 첨자가 어긋나게 된다).
• 모든 열 fetch하므로 데이터 양이 많아진다.

 해결법은 간단하다.

• 열 이름을 명시적으로 지정한다.

 

19. 읽을 수 있는 패스워드

 패드워드를 평문으로 저장하는 것은 보안상 위험하다. 해결법은 다음과 같다.

• 패스워드 해시를 저장한다.
• 패스워드는 복구가 아닌 리셋을 한다.

 

20. SQL 인젝션

 동적으로 SQL을 구축할 때에는 검증하지 않은 입력을 코드로써 실행하는 것은 좋지 않다. 그 이유는 다음과 같다.

• SQL 스테이트먼트 인수, 테이블 삭제, 사용자 행세 등 모든 것을 할 수 있기 때문이다.

 대책은 다음과 같다.

• 프리퍼어드 스테이트먼트를 이용한다.
• 유저의 입력을 코드로 부터 격리한다(프리페어드 스테이트먼틑를 사용할 수 없는 상황에서 유효하다).
• 코드 리뷰

 

21. 가상키 편집증

 id가 연속되지 않은 것을 어떻게든 메꾸려고 하는 것은 좋지 않다. 이유는 다음과 같다.

• 결번을 특정하기 위해 자가 결합 쿼리를 발행할 필요가 있다.
• 배타제어의 문제로 인해 에러가 발생할 수 있다.
• 결번을 메꾸려고 한다고 해도 시퀀스는 마지막에 생성한 값을 바탕으로 만들어지므로 새로운 결번이 만들어진다.
• 결번은 타당한 이유로 인해 예를 들어 행이 삭제 혹은 롤백의 결과이다. 

 해결법은 다음과 같다.

• 유사한 키를 사용하지 않는다.
• 주키열의 값에 어떠한 의미를 갖게 해서는 안 된다.
• GUID의 이용을 검토한다. 
• 상사와의 대화를 통해 관리 방식을 결정하거나, 자연 키를 이용한다.

 

22. 나쁜 것 안 보기

 데이터 베이스 API의 반환값을 무시하거나, 어플리케이션 코드 안에서 존재하는 SQL만 읽거나, 예외 처리를 하지 않는 등과 같은 것은 좋지 않다. 그 이유는 다음과 같다.

• 반환값을 무시하는 것이므로 문제를 발견할 수 없다.
• 구축된 SQL을 보지 않고 SQL을 구축하는 코드를 쫓으므로 인해 시간을 소비한다.

대책은 다음과 값다.

• 반환값과 예외의 체크
• 실제로 구축된 SQL사용한다.

 

23. 외교적 면책특권

어플리케이션 개발의 베스트 사항을 실천하고 있는 팀이라도 SQL이라면 피하는 패턴. 결국 어떤 기술적 부담을 남기게 된다.

 해결법은 다음과 같다.

• 데이터 베이스 개발에 있어서도 문서화, 버전 관리, 테스팅의 베스트 사항을 지킨다.
• DDL, seed data, 데이터 베이스 관리 스크립트, 트리거와 프로시저, ER도와 문서 버전관리에 작성한다.
• 개발, 테스트, 스테이징, 디플로이를 행하는 어플리케이션 각각에 데이터 베이스 인스턴스를 만든다. 각 개발자의 데이터 베이스를 준비한다.

 

24. 마법의 콩

 안티 패턴은 다음과 같다.

• Model을 단순화하려고 한 결과, Model이 Active Record 그 자체가 되어버린 패턴
• 컨트롤러에 비즈니스 로직이 유출되어, 코드가 덩굴처럼 얽히게 된 패턴

 좋지 않은 이유는 다음과 같다.

• 모델이 데이터 베이스 스키마에 의존하게 된다.
• 모델이 외부로 CRUD를 공개해버리므로 예상치 못한 방법으로 사용될 가능성이 있다.
• 모델의 응집도를 떨어트린다.
• 유닛 테스트가 어려워진다.

 대책은 다음과 같다.

• 모델이 액티브 레코드를 가지도록 한다.
• DAO를 이용하여 데이터 베이스 액세스를 분리하여, 데이터 베이스와 독립하여 유닛 테스트를 할 수 있게 된다.

참고자료

https://zenn.dev/yukito0616/articles/00ccc30b58e458

https://qiita.com/taiteam/items/33aebded2842808e0391

https://engineer-mole.tistory.com/380

[SQL] SQL AntiPatterns

 

 

SQL Injection 공격

SQL 인젝션(SQL 삽입, SQL 주입으로도 불린다)은 코드 인젝션의 한 기법으로 클라이언트의 입력값을 조작하여 서버의 데이터베이스를 공격할 수 있는 공격방식을 말한다. 주로 사용자가 입력한 데이터를 제대로 필터링, 이스케이핑하지 못했을 경우에 발생한다.

 

참고자료

 

https://namu.wiki/w/SQL%20injection