PDO 준비된 명령문 및 저장 프로시저

보다 성숙한 데이터베이스의 대부분은 준비된 명령문의 개념을 지원합니다. 그들은 무엇인가? 응용 프로그램이 실행하려는 SQL에 대한 일종의 컴파일된 템플릿으로 생각할 수 있으며 가변 매개 변수를 사용하여 사용자 지정할 수 있습니다. 준비된 진술은 두 가지 주요 이점을 제공합니다.

  • 쿼리는 한 번만 구문 분석(또는 준비)해야 하지만 동일하거나 다른 매개변수를 사용하여 여러 번 실행할 수 있습니다. 쿼리가 준비되면 데이터베이스는 쿼리 실행 계획을 분석, 컴파일 및 최적화합니다. 복잡한 쿼리의 경우 이 프로세스는 충분한 시간이 걸릴 수 있으므로 다른 매개변수를 사용하여 동일한 쿼리를 여러 번 반복해야 하는 경우 애플리케이션 속도가 현저히 느려질 수 있습니다. 응용 프로그램은 준비된 명령문을 사용하여 분석/컴파일/최적화 주기를 반복하지 않습니다. 이는 준비된 명령문이 더 적은 리소스를 사용하므로 더 빠르게 실행됨을 의미합니다.
  • 준비된 명령문에 대한 매개변수는 따옴표로 묶을 필요가 없습니다. 드라이버가 이를 자동으로 처리합니다. 응용 프로그램이 준비된 명령문을 독점적으로 사용하는 경우 개발자는 SQL 주입이 발생하지 않음을 확신할 수 있습니다(그러나 쿼리의 다른 부분이 이스케이프 처리되지 않은 입력으로 구축되는 경우 SQL 주입은 여전히 ​​가능합니다).

준비된 명령문은 매우 유용하여 PDO가 이를 지원하지 않는 드라이버에 대해 에뮬레이트할 유일한 기능입니다. 이를 통해 애플리케이션은 데이터베이스의 기능에 관계없이 동일한 데이터 액세스 패러다임을 사용할 수 있습니다.

예제 #1 준비된 명령문을 사용한 반복 삽입

이 예에서는 명명된 자리 표시자의 이름과 값을 대체하여 INSERT 쿼리를 수행합니다.

                  
<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

// insert another row with different values
$name = 'two';
$value = 2;
$stmt->execute();
?>
                  
                

예제 #2 준비된 문을 사용한 반복 삽입

This example performs an INSERT query by substituting a name and a value for the positional ? placeholders.

                  
<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

// insert another row with different values
$name = 'two';
$value = 2;
$stmt->execute();
?>
                  
                

예제 #3 준비된 명령문을 사용하여 데이터 가져오기

이 예는 양식에서 제공한 키 값을 기반으로 데이터를 가져옵니다. 사용자 입력은 자동으로 인용되므로 SQL 주입 공격의 위험이 없습니다.

                  
<?php
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?");
$stmt->execute([$_GET['name']]);
foreach ($stmt as $row) {
  print_r($row);
}
?>
                  
                

예제 #4 출력 매개변수를 사용하여 저장 프로시저 호출

데이터베이스 드라이버가 지원하는 경우 애플리케이션은 입력뿐만 아니라 출력에 대한 매개변수도 바인딩할 수 있습니다. 출력 매개변수는 일반적으로 저장 프로시저에서 값을 검색하는 데 사용됩니다. 출력 매개변수는 입력 매개변수보다 사용하기가 약간 더 복잡합니다. 개발자는 바인딩할 때 주어진 매개변수가 얼마나 클 수 있는지 알아야 합니다. 값이 제안한 크기보다 큰 것으로 판명되면 오류가 발생합니다.

                  
<?php
$stmt = $dbh->prepare("CALL sp_returns_string(?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000);

// call the stored procedure
$stmt->execute();

print "procedure returned $return_value\n";
?>
                  
                

예제 #5 입력/출력 매개변수를 사용하여 저장 프로시저 호출

개발자는 입력과 출력 모두 값을 보유하는 매개변수를 지정할 수도 있습니다. 구문은 출력 매개변수와 유사합니다. 다음 예에서는 'hello' 문자열이 저장 프로시저에 전달되고 반환될 때 hello가 프로시저의 반환 값으로 바뀝니다.

                  
<?php
$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
$value = 'hello';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);

// call the stored procedure
$stmt->execute();

print "procedure returned $value\n";
?>
                  
                

예제 #6 Invalid use of placeholder

                  
<?php
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE '%?%'");
$stmt->execute([$_GET['name']]);

// placeholder must be used in the place of the whole value
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE ?");
$stmt->execute(["%$_GET[name]%"]);
?>