PHP PDO에서 IN 절을 안전하게 사용하는 방법 - array_map과 prepared statement 활용하기

이 코드는 데이터베이스에서 일정/할일(todos)과 휴일(holiday) 정보를 조회하는 PHP 스크립트입니다.

PHP MySQL 동적 IN 절 쿼리 작성하기 - PDO prepared statements로 SQL 인젝션 방지하기

이런 제목들이 SEO에 효과적일 것 같습니다. 그 이유는:

1. 핵심 키워드 포함:
   - PHP
   - PDO
   - prepared statement
   - IN clause/IN 절
   - SQL injection

2. 검색 의도 반영:
   - "안전하게"
   - "동적"
   - "방법"
   같은 실용적인 키워드를 포함해 개발자들의 검색 의도와 매칭됩니다.

3. 구체적인 기술 스택 명시:
   - PHP PDO
   - MySQL

"array_map과 implode를 활용한 동적 쿼리 생성 테크닉"

mysql의 테이블의 구성 컬럼으로 검색을 하는 코드를 요약합니다.

컬럼이 20개, 30개 이렇게 많으면 다 나열하기 힘드니까 이걸 지혜롭게 해결하는 방법일 수 있습니다.

<?php
require_once($_SERVER['DOCUMENT_ROOT'] . "/session.php");
require_once($_SERVER['DOCUMENT_ROOT'] . "/lib/mydb.php");
$pdo = db_connect();

$month = $_POST['month'];
$year = $_POST['year'];
$search = $_POST['search'] ?? null;
$selectedItem = $_POST['selectedItem'] ?? 'all';
$findSection = $_POST['findSection'] ?? '전체';
$radioarray = $_POST['radioarray'] ?? [];

$todosMain_data = array();
$holiday_data = array();

try {
    // 기본 쿼리 생성
    $query = "SELECT * FROM " . $DB . ".todosMain
              WHERE (is_deleted IS NULL or is_deleted = '') 
              AND MONTH(orderdate) = :month 
              AND YEAR(orderdate) = :year";

    // 전체가 아닌 경우, 특정 항목 검색
    if ($selectedItem !== 'all') {
        $query .= " AND towhom = :towhom";
    } else {
        // 전체일 경우 $radioarray의 요소에 해당하는 데이터만 검색
        if (!empty($radioarray)) {
            $placeholders = array_map(function ($key) {
                return ":radio_" . $key;
            }, array_keys($radioarray));
            $query .= " AND towhom IN (" . implode(", ", $placeholders) . ")";
        }
    }

    // 검색 조건 추가
    if (!empty($search)) {
        $columns = $pdo->query("SHOW COLUMNS FROM " . $DB . ".todosMain")->fetchAll(PDO::FETCH_COLUMN);

        $searchConditions = [];
        foreach ($columns as $column) {
            $searchConditions[] = "$column LIKE :search";
        }

        if (!empty($searchConditions)) {
            $query .= " AND (" . implode(" OR ", $searchConditions) . ")";
        }
    }

    // 쿼리 디버깅
    error_log("Generated Query: $query");

    $stmh = $pdo->prepare($query);
    $stmh->bindParam(':month', $month, PDO::PARAM_INT);
    $stmh->bindParam(':year', $year, PDO::PARAM_INT);

    // 'all'이 아닌 경우 towhom 값을 바인딩
    if ($selectedItem !== 'all') {
        $stmh->bindParam(':towhom', $selectedItem, PDO::PARAM_STR);
    } else {
        // 전체일 경우 $radioarray의 값들을 바인딩
        foreach ($radioarray as $key => $value) {
            $stmh->bindValue(":radio_" . $key, $value, PDO::PARAM_STR);
        }
    }

    // 검색어 바인딩
    if (!empty($search)) {
        $stmh->bindValue(":search", "%" . $search . "%", PDO::PARAM_STR);
    }

    $stmh->execute();

    while ($row = $stmh->fetch(PDO::FETCH_ASSOC)) {
        array_push($todosMain_data, $row);
    }

    // holiday 데이터 가져오기
    $stmh = $pdo->query("SELECT num, startdate, enddate, comment 
                         FROM " . $DB . ".holiday 
                         WHERE is_deleted IS NULL 
                         AND ((MONTH(startdate) = $month AND YEAR(startdate) = $year) 
                         OR (MONTH(enddate) = $month AND YEAR(enddate) = $year))");

    while ($row = $stmh->fetch(PDO::FETCH_ASSOC)) {
        array_push($holiday_data, $row);
    }

    // 응답 데이터 구성
    $response = array(
        "todosMain_data" => $todosMain_data,
        "holiday_data" => $holiday_data,
        "querys" => $query,
        "radioarray" => $radioarray,
    );

    echo(json_encode($response, JSON_UNESCAPED_UNICODE));
} catch (PDOException $Exception) {
    // 상세한 오류 메시지 출력
    http_response_code(500);
	error_log("Generated Query: $query");
	error_log("Radioarray: " . json_encode($radioarray));
	error_log("Search Binding: $search");
	
    error_log("PDOException: " . $Exception->getMessage());
    echo json_encode(["error" => $Exception->getMessage()]);
}


?>

주요 기능들을 살펴보겠습니다:

1. 기본 파라미터 처리:
```php
$month = $_POST['month']; 
$year = $_POST['year']; 
$search = $_POST['search'] ?? null; 
$selectedItem = $_POST['selectedItem'] ?? 'all'; 
$findSection = $_POST['findSection'] ?? '전체'; 
$radioarray = $_POST['radioarray'] ?? []; 
```

2. 동적 검색 쿼리 구성:
- 기본적으로 월/년도로 필터링
- 삭제되지 않은 항목만 조회 (is_deleted IS NULL or is_deleted = '')
- selectedItem이 'all'이 아닐 경우 특정 담당자(towhom) 필터링
- radioarray가 있을 경우 해당하는 담당자들만 필터링

3. 특히 말씀하신 전체 컬럼 검색 부분이 매우 흥미롭습니다:
```php
$columns = $pdo->query("SHOW COLUMNS FROM " . $DB . ".todosMain")->fetchAll(PDO::FETCH_COLUMN); 

$searchConditions = []; 
foreach ($columns as $column) { 
    $searchConditions[] = "$column LIKE :search"; 
} 
```
이 부분은 매우 유연한 검색 기능을 구현했습니다:
- SHOW COLUMNS 명령으로 테이블의 모든 컬럼을 동적으로 가져옴
- 각 컬럼에 대해 LIKE 검색 조건을 생성
- 이를 통해 사용자가 입력한 검색어를 모든 컬럼에서 검색 가능

4. 보안 측면에서도 잘 구현되어 있습니다:
- PDO를 사용한 prepared statement 활용
- 모든 변수는 bindParam/bindValue로 안전하게 바인딩
- SQL 인젝션 방지

5. 에러 처리와 디버깅:
```php
error_log("Generated Query: $query"); 
error_log("Radioarray: " . json_encode($radioarray)); 
error_log("Search Binding: $search"); 
```
상세한 로깅을 통해 문제 발생 시 디버깅이 용이하게 구현되어 있습니다.

6. 응답 데이터 구성:
- todos 데이터와 holiday 데이터를 함께 반환
- JSON 형식으로 응답하며, 한글 깨짐 방지(JSON_UNESCAPED_UNICODE)

특히 전체 컬럼 검색 부분은 매우 유연한 구현방식입니다. 테이블 구조가 변경되어도 코드 수정 없이 자동으로 새로운 컬럼까지 검색 대상에 포함되는 장점이 있습니다. 다만, 대용량 데이터베이스의 경우 모든 컬럼을 LIKE로 검색하는 것은 성능에 영향을 줄 수 있으므로, 필요한 경우 인덱스 설정이나 검색 대상 컬럼을 제한하는 것을 고려해볼 수 있습니다.

 

추가적으로 bind하는 지혜로운 방법

// 전체일 경우 $radioarray의 요소에 해당하는 데이터만 검색
        if (!empty($radioarray)) {
            $placeholders = array_map(function ($key) {
                return ":radio_" . $key;
            }, array_keys($radioarray));
            $query .= " AND towhom IN (" . implode(", ", $placeholders) . ")";
        }

위의 코드의 원리를 알려주세요.

네, 이 코드의 동작 원리를 단계별로 설명해드리겠습니다:

1. 우선 `$radioarray`가 비어있지 않은 경우에만 실행됩니다.
   ```php
   if (!empty($radioarray))
   ```

2. `array_map`을 사용하여 `$radioarray`의 키들을 바인딩 파라미터로 변환합니다:
   ```php
   array_map(function ($key) {
       return ":radio_" . $key;
   }, array_keys($radioarray))
   ```

예를 들어, 다음과 같은 `$radioarray`가 있다고 가정해보겠습니다:
```php
$radioarray = [
    0 => "김철수",
    1 => "이영희",
    2 => "박민수"
];
```

3. `array_keys($radioarray)`는 `[0, 1, 2]`를 반환합니다.

4. `array_map`은 각 키에 대해 `:radio_`를 앞에 붙여서 새로운 배열을 만듭니다:
   ```php
   [":radio_0", ":radio_1", ":radio_2"]
   ```

5. `implode(", ", $placeholders)`는 이 배열을 쉼표로 구분된 문자열로 변환합니다:
   ```php
   ":radio_0, :radio_1, :radio_2"
   ```

6. 최종적으로 쿼리에 추가되는 조건은 다음과 같습니다:
   ```sql
   AND towhom IN (:radio_0, :radio_1, :radio_2)
   ```

7. 그리고 나중에 코드에서 실제 값들이 바인딩됩니다:
   ```php
   foreach ($radioarray as $key => $value) {
       $stmh->bindValue(":radio_" . $key, $value, PDO::PARAM_STR);
   }
   ```

이렇게 하면 최종적으로 실행되는 쿼리는 다음과 같은 효과를 가집니다:
```sql
AND towhom IN ('김철수', '이영희', '박민수')
```

이 방식의 장점은:
1. SQL 인젝션 방지: 모든 값이 prepared statement를 통해 안전하게 바인딩됩니다.
2. 동적 쿼리 생성: 선택된 항목의 수에 따라 자동으로 쿼리가 조정됩니다.
3. 코드 가독성: placeholder를 사용함으로써 쿼리 구조가 명확해집니다.