[SQL] CIDR 목록 검색 예제(IPv4)

위키 엔진을 만들던 중 CIDR 차단 기능을 구현해야 하는데, 이걸 어찌할지 몰라 한참 고민하고 있었다. PHP 상에서 처리를 해야 하나 고민했지만 해답은 SQL에서 찾을 수 있었다.

문제 상황

나는 MariaDB를 운용하고 있었고, 테이블의 각 행에는 차단 대상으로 지정된 CIDR들이 등록되어 있었다.
| id | target_ip |
|—-|——————|
| 1 | 1.2.3.4/32 |
| 2 | 123.45.67.0/24 |
| 3 | 123.45.0.0/16 |
| 4 | 0.0.0.0/1 |

그리고 쿼리를 통해 접속한 IP가 차단 대상에 해당하는지 확인해야 한다. 그래서 특정 IP 주소를 포함하는 CIDR이 테이블 안에 있는지를 살펴야 한다.

검색 끝에 아래와 같은 쿼리를 찾았다. 원래는 CIDR로부터 범위를 추출하는 쿼리였지만 잘 개조해서 아래처럼 ‘대상 IP’를 포함하는 CIDR들을 추출할 수 있는 쿼리를 만들었다.

대상 IP를 123.45.67.8로 지정한 쿼리 결과:
| id |
| 2 |
| 3 |
| 4 |

대상 IP를 123.45.6.7로 지정한 쿼리 결과:
| id |
| 3 |
| 4 |


복붙해다 쓸거면 그냥 바로 아래 있는 걸 건져가서 적절히 수정하면 된다.

SELECT id FROM 테이블이름 WHERE INET_NTOA(INET_ATON( SUBSTRING_INDEX(target_ip, '/', 1)) & 0xffffffff ^ ((0x1 << ( 32 - SUBSTRING_INDEX(target_ip, '/', -1)) ) -1 )) <= '대상 IP' AND INET_NTOA(INET_ATON( SUBSTRING_INDEX(target_ip, '/', 1)) | ((0x100000000 >> SUBSTRING_INDEX(target_ip, '/', -1) ) -1 )) >= '대상 IP';

수정하기 편하라고 하이라이팅해뒀다.
SELECT id FROM 테이블이름 WHERE INET_NTOA(INET_ATON( SUBSTRING_INDEX(target_ip, '/', 1)) & 0xffffffff ^ ((0x1 << ( 32 - SUBSTRING_INDEX(target_ip, '/', -1)) ) -1 )) <= '대상 IP' AND INET_NTOA(INET_ATON( SUBSTRING_INDEX(target_ip, '/', 1)) | ((0x100000000 >> SUBSTRING_INDEX(target_ip, '/', -1) ) -1 )) >= '대상 IP';

참고 사이트: https://stackoverflow.com/questions/35562416/mysql-query-to-convert-cidr-into-ip-range

MySQL query to convert CIDR into IP range