MySQLi 준비된 명령문

MySQL 데이터베이스는 준비된 명령문을 지원합니다. 준비된 명령문 또는 매개변수화된 명령문은 동일한 명령문을 고효율로 반복적으로 실행하고 SQL 주입으로부터 보호하는 데 사용됩니다.

기본 워크플로

준비된 명령문 실행은 준비와 실행의 두 단계로 구성됩니다. 준비 단계에서 명령문 템플릿이 데이터베이스 서버로 전송됩니다. 서버는 구문 검사를 수행하고 나중에 사용할 수 있도록 서버 내부 리소스를 초기화합니다.

MySQL 서버는 ?가 있는 익명의 위치 지정자 사용을 지원합니다.

준비 후에 실행이 이어집니다. 실행하는 동안 클라이언트는 매개변수 값을 바인딩하고 서버로 보냅니다. 서버는 이전에 생성된 내부 리소스를 사용하여 바인딩된 값으로 문을 실행합니다.

예제 #1 준비된 명령문

                  
<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

/* Non-prepared statement */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");

/* Prepared statement, stage 1: prepare */
$stmt = $mysqli->prepare("INSERT INTO test(id, label) VALUES (?, ?)");

/* Prepared statement, stage 2: bind and execute */
$id = 1;
$label = 'PHP';
$stmt->bind_param("is", $id, $label); // "is" means that $id is bound as an integer and $label as a string

$stmt->execute();
                  
                

반복 실행

준비된 명령문은 반복적으로 실행할 수 있습니다. 실행할 때마다 바인딩된 변수의 현재 값이 평가되어 서버로 전송됩니다. 명령문이 다시 구문 분석되지 않습니다. 명령문 템플릿은 다시 서버로 전송되지 않습니다.

예제 #2 INSERT가 한 번 준비되고 여러 번 실행됨

                  
<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

/* Non-prepared statement */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");

/* Prepared statement, stage 1: prepare */
$stmt = $mysqli->prepare("INSERT INTO test(id, label) VALUES (?, ?)");

/* Prepared statement, stage 2: bind and execute */
$stmt->bind_param("is", $id, $label); // "is" means that $id is bound as an integer and $label as a string

$data = [
    1 => 'PHP',
    2 => 'Java',
    3 => 'C++'
];
foreach ($data as $id => $label) {
    $stmt->execute();
}

$result = $mysqli->query('SELECT id, label FROM test');
var_dump($result->fetch_all(MYSQLI_ASSOC));
                  
                

위의 예는 다음을 출력합니다.

array(3) {
  [0]=>
  array(2) {
    ["id"]=>
    string(1) "1"
    ["label"]=>
    string(3) "PHP"
  }
  [1]=>
  array(2) {
    ["id"]=>
    string(1) "2"
    ["label"]=>
    string(4) "Java"
  }
  [2]=>
  array(2) {
    ["id"]=>
    string(1) "3"
    ["label"]=>
    string(3) "C++"
  }
}
                

준비된 모든 문은 서버 리소스를 차지합니다. 문은 사용 직후에 명시적으로 닫아야 합니다. 명시적으로 수행하지 않으면 PHP에서 명령문 핸들을 해제할 때 명령문이 닫힙니다.

준비된 명령문을 사용하는 것이 항상 명령문을 실행하는 가장 효율적인 방법은 아닙니다. 한 번만 실행된 준비된 명령문은 준비되지 않은 명령문보다 더 많은 클라이언트-서버 왕복을 유발합니다. 이것이 SELECT가 위의 준비된 명령문으로 실행되지 않는 이유입니다.

또한 INSERT에 대한 MySQL 다중 INSERT SQL 구문의 사용을 고려하십시오. 예를 들어 다중 INSERT는 위에 표시된 준비된 명령문보다 서버와 클라이언트 간의 왕복이 더 적습니다.

예제 #3 다중 INSERT SQL을 사용하여 더 적은 왕복 이동

                  
<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");

$values = [1, 2, 3, 4];

$stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?), (?), (?), (?)");
$stmt->bind_param('iiii', ...$values);
$stmt->execute();
                  
                

결과 집합 값 데이터 유형

MySQL 클라이언트 서버 프로토콜은 준비된 명령문과 준비되지 않은 명령문에 대해 다른 데이터 전송 프로토콜을 정의합니다. 준비된 명령문은 소위 바이너리 프로토콜을 사용하고 있습니다. MySQL 서버는 결과 집합 데이터를 바이너리 형식으로 "as is" 보냅니다. 결과는 보내기 전에 문자열로 직렬화되지 않습니다. 클라이언트 라이브러리는 이진 데이터를 수신하고 값을 적절한 PHP 데이터 유형으로 변환하려고 시도합니다. 예를 들어 SQL INT 열의 결과는 PHP 정수 변수로 제공됩니다.

예제 #4 기본 데이터 유형

                  
<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

/* Non-prepared statement */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");

$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();

printf("id = %s (%s)\n", $row['id'], gettype($row['id']));
printf("label = %s (%s)\n", $row['label'], gettype($row['label']));
                  
                

위의 예는 다음을 출력합니다.

id = 1 (integer)
label = PHP (string)
                

이 동작은 준비되지 않은 명령문과 다릅니다. 기본적으로 준비되지 않은 문은 모든 결과를 문자열로 반환합니다. 이 기본값은 연결 옵션을 사용하여 변경할 수 있습니다. 연결 옵션을 사용하면 차이가 없습니다.

바운드 변수를 사용하여 결과 가져오기

준비된 명령문의 결과는 출력 변수를 바인딩하거나 mysqli_result 객체를 요청하여 검색할 수 있습니다.

출력 변수는 명령문 실행 후에 바인딩되어야 합니다. 명령문 결과 세트의 모든 열에 대해 하나의 변수가 바인드되어야 합니다.

예제 #5 출력 변수 바인딩

                  
<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

/* Non-prepared statement */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");

$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();

$stmt->bind_result($out_id, $out_label);

while ($stmt->fetch()) {
    printf("id = %s (%s), label = %s (%s)\n", $out_id, gettype($out_id), $out_label, gettype($out_label));
}
                  
                

위의 예는 다음을 출력합니다.

id = 1 (integer), label = PHP (string)
                

준비된 문은 기본적으로 버퍼링되지 않은 결과 집합을 반환합니다. 명령문의 결과는 암시적으로 페치되지 않고 클라이언트 측 버퍼링을 위해 서버에서 클라이언트로 전송됩니다. 결과 집합은 클라이언트가 모든 결과를 가져올 때까지 서버 리소스를 사용합니다. 따라서 적시에 결과를 소비하는 것이 좋습니다. 클라이언트가 모든 결과를 가져오는 데 실패하거나 모든 데이터를 가져오기 전에 클라이언트가 명령문을 닫는 경우 mysqli에서 암시적으로 데이터를 가져와야 합니다.

mysqli_stmt::store_result()를 사용하여 준비된 명령문의 결과를 버퍼링하는 것도 가능합니다.

mysqli_result 인터페이스를 사용하여 결과 가져오기

바인딩된 결과를 사용하는 대신 mysqli_result 인터페이스를 통해 결과를 검색할 수도 있습니다. mysqli_stmt::get_result()는 버퍼링된 결과 집합을 반환합니다.

예제 #6 mysqli_result를 사용하여 결과 가져오기

                  
<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

/* Non-prepared statement */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");

$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();

$result = $stmt->get_result();

var_dump($result->fetch_all(MYSQLI_ASSOC));
                  
                

위의 예는 다음을 출력합니다.

array(1) {
  [0]=>
  array(2) {
    ["id"]=>
    int(1)
    ["label"]=>
    string(3) "PHP"
  }
}
                

mysqli_result 인터페이스를 사용하면 유연한 클라이언트 측 결과 집합 탐색의 추가 이점을 제공합니다.

예제 #7 유연한 읽기를 위한 버퍼링된 결과 집합

                  
<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

/* Non-prepared statement */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP'), (2, 'Java'), (3, 'C++')");

$stmt = $mysqli->prepare("SELECT id, label FROM test");
$stmt->execute();

$result = $stmt->get_result();

for ($row_no = $result->num_rows - 1; $row_no >= 0; $row_no--) {
    $result->data_seek($row_no);
    var_dump($result->fetch_assoc());
}
                  
                

위의 예는 다음을 출력합니다.

array(2) {
  ["id"]=>
  int(3)
  ["label"]=>
  string(3) "C++"
}
array(2) {
  ["id"]=>
  int(2)
  ["label"]=>
  string(4) "Java"
}
array(2) {
  ["id"]=>
  int(1)
  ["label"]=>
  string(3) "PHP"
}
                

이스케이프 및 SQL 주입

바인딩된 변수는 쿼리와 별도로 서버로 전송되므로 간섭할 수 없습니다. 서버는 명령문 템플릿이 구문 분석된 후 실행 지점에서 이러한 값을 직접 사용합니다. 바인딩된 매개변수는 쿼리 문자열로 직접 대체되지 않으므로 이스케이프할 필요가 없습니다. 적절한 변환을 생성하려면 바인딩된 변수의 유형에 대한 힌트를 서버에 제공해야 합니다. 자세한 내용은 mysqli_stmt::bind_param() 함수를 참조하십시오.

이러한 분리는 때때로 SQL 주입을 방지하는 유일한 보안 기능으로 간주되지만 모든 값의 형식이 올바르게 지정되면 준비되지 않은 명령문에서도 동일한 수준의 보안을 달성할 수 있습니다. 올바른 형식 지정은 이스케이프와 동일하지 않으며 단순 이스케이프보다 더 많은 논리가 필요합니다. 따라서 준비된 명령문은 이 데이터베이스 보안 요소에 대해 더 편리하고 오류가 발생하기 쉬운 접근 방식입니다.

클라이언트 측 준비된 명령문 에뮬레이션

API는 클라이언트 측 준비된 명령문 에뮬레이션을 위한 에뮬레이션을 포함하지 않습니다.

준비된 진술과 준비되지 않은 진술의 빠른 비교

아래 표는 서버 측 준비된 명령문과 준비되지 않은 명령문을 비교합니다.

준비된 진술과 준비되지 않은 진술의 비교

기타

---

• mysqli::__construct()
• mysqli::query()
• mysqli::prepare()
• mysqli_stmt::prepare()
• mysqli_stmt::execute()
• mysqli_stmt::bind_param()
• mysqli_stmt::bind_result()