10.2 Database programming for the web

Connections

이 섹션에서는 PHP와 MySQL을 사용한 웹 데이터베이스 프로그래밍을 설명한다. PHP는 동적 웹사이트를 생성하는 데 가장 널리 사용되는 프로그래밍 언어이다. PHP의 대안으로는 Python, Java, Node.js, ASP.NET 등이 있다. 이 섹션은 웹 페이지를 작성하는 언어인 HTML과 PHP에 대한 기본 지식을 전제로 한다.

PHP 애플리케이션은 MySQL을 포함한 다양한 데이터베이스와 상호작용하기 위해 PHP Data Objects (PDO) API를 사용한다. 특정 데이터베이스에 PDO를 사용하려면 해당 데이터베이스에 맞는 PDO 드라이버가 필요하다. 대부분의 PHP 설치에는 MySQL용 PDO 드라이버가 포함되어 있다.

PHP 스크립트는 쿼리를 실행하기 전에 데이터베이스에 연결해야 한다. PHP 스크립트는 PDO 객체를 생성하여 연결을 설정한다. PDO 생성자는 DSN, MySQL 사용자 이름, 비밀번호를 지정한다. DSN(Data Source Name)은 데이터베이스 연결 정보를 포함하는 문자열이다:

- DSN 접두사(prefix): MySQL의 경우 mysql:
- 호스트(host): 데이터베이스 서버의 호스트 이름 또는 주소
- 포트(port): 데이터베이스 서버의 포트 번호(기본 포트를 사용하지 않는 경우)
- 데이터베이스 이름(dbname): 데이터베이스 이름

PDO 생성자가 MySQL에 성공적으로 연결하지 못하면 PDOException을 던지며, 이는 치명적인 오류를 초래한다. PHP는 치명적인 오류를 처리하도록 구성할 수 있으며, 오류 메시지를 웹 페이지에 출력하는 방식 등을 포함한다.

1. 웹 브라우저는 db.php라는 PHP 스크립트를 요청하기 위해 웹 서버에 요청을 보낸다. 웹 서버는 PHP 스크립트를 실행한다.
2. DSN은 PDO가 로컬호스트(127.0.0.1)에서 실행 중인 MySQL 서버에 연결하고 "Reservation"이라는 데이터베이스를 사용할 것을 지정한다.
3. PDO 생성자는 DSN과 MySQL 사용자 이름 및 비밀번호를 사용하여 데이터베이스 연결을 생성한다.
4. 연결이 성공하면 PHP 스크립트는 PDO 객체를 사용하여 쿼리를 실행한다.
5. 연결이 실패하면 PDOException이 치명적인 오류를 발생시킨다.
6. PHP가 치명적인 오류를 표시하도록 구성된 경우, 웹 서버는 오류 세부 정보를 보여주는 웹 페이지로 응답하며, 이는 웹 브라우저에 표시된다.

---

Executing statements

PDO 객체의 query() 메서드는 SQL 문을 실행한다. query() 메서드는 PDOStatement 객체를 반환하거나, 문 실행 중 오류가 발생하면 FALSE를 반환한다.

 

SQL 오류 발생 시 query() 반환값을 FALSE로 확인하는 대신, setAttribute()를 호출하여 SQL 오류 발생 시 PDO 객체가 PDOException을 던지도록 설정하는 것이 좋다. PDOException은 SQL 오류에 대한 세부 정보를 제공한다.

PDO 객체의 setAttribute() 메서드는 PDO 속성을 특정 값으로 설정한다.
Ex: $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION)은 오류 모드를 설정하여 PDOException을 던지도록 한다.

 

PDOStatement 메서드 rowCount()는 삽입, 업데이트 또는 삭제된 행의 수를 반환한다.

1. PDO 객체는 MySQL에 연결을 생성한다.
2. setAttribute()는 SQL 쿼리를 실행하는 동안 오류가 발생하면 PDO가 PDOException을 던지도록 설정한다.
3. query() 메서드는 $sql 문자열에 있는 INSERT 문을 실행한다. 항공편이 Flight 테이블에 삽입된다.
4. MySQL 서버는 INSERT 문의 결과를 나타내는 정보를 웹 서버에 반환한다. 이 정보는 query() 메서드에서 PDOStatement로 반환된다.
5. rowCount() 메서드는 Flight 테이블에 한 행이 삽입되었기 때문에 1을 반환한다.

- setAttribute() 메서드는 SQL 문 실행 중 오류가 발생했을 때 어떤 동작을 수행해야 하는지 설정할 뿐이다. query() 메서드는 setAttribute()를 호출하지 않아도 SQL 문을 실행할 수 있다.

- setAttribute() 메서드는 잘못된 SQL 문을 실행할 때 PDOException이 던져져야 한다는 것을 지정한다. MySQL은 중복된 기본 키 값을 삽입하려는 시도 시 오류를 보고한다.

---

Prepared statements

SQL 문은 사용자 입력을 기반으로 동적으로 생성될 수 있다. Prepared statement는 사용자 입력에서 가져온 값으로 사용자 정의할 수 있는 SQL 문이다. Prepared statement는 대부분의 SQL 삽입 공격에 대해 면역성이 있다.

Prepared statement를 생성하고 실행하는 4단계

1. 정의(Define)
   • Prepared statement는 값의 자리 표시자로 매개변수 식별자를 사용하는 SQL 문을 사용한다.
   • 매개변수 식별자는 물음표(?) 또는 콜론 뒤에 오는 이름(:name)일 수 있다.
2. 준비(Prepare)
   • PDO의 prepare() 메서드는 매개변수가 포함된 SQL 문을 실행 준비 상태로 설정하고 PDOStatement를 반환한다.
3. 바인딩(Bind)
   • PDOStatement의 bindValue() 메서드는 SQL 문에서 매개변수 식별자에 값을 바인딩한다.
   • 예: $statement->bindValue(1, "Bob")은 "Bob"을 첫 번째 물음표에 바인딩하며, $statement->bindValue("name", "Bob")은 "Bob"을 :name 매개변수 식별자에 바인딩한다.
4. 실행(Execute)
   • PDOStatement의 execute() 메서드는 바인딩된 매개변수로 SQL 문을 실행하며, 오류가 발생하면 PDOException을 던진다.

 

1. 사용자는 항공편 정보를 양식(form)에 입력한다. 양식은 addflight.php에 제출되며, 제출된 값은 $_POST 배열에 저장된다.
2. INSERT 문은 세 개의 값에 대해 ?를 자리 표시자로 사용한다.
3. prepare()는 매개변수가 포함된 SQL 문을 준비하고 PDOStatement를 반환한다.
4. bindValue()는 $_POST의 값을 매개변수 식별자에 바인딩한다.
5. execute()는 INSERT 문을 실행한다. addflight.php는 확인 메시지를 표시한다.

---

1) 누락된 값은 무엇인가?

$sql = "UPDATE Flight " .
       "SET AirlineName = ? " .
       "WHERE FlightNumber = ?";
           
$statement = $pdo->prepare($sql);
$statement->bindValue(1, ____);
$statement->bindValue(2, ____);
$statement->execute();

▼View answer

"United Airlines" and 305

자리 표시자 값이 할당된 후, UPDATE 문은 다음과 같이 변환된다:
UPDATE Flight SET AirlineName = 'United Airlines' WHERE FlightNumber = 305.

2) 누락된 값은 무엇인가?

$sql = "UPDATE Flight " .
       "SET AirlineName = :airlineName " .
       "WHERE FlightNumber = :flightNum";
           
$statement = $pdo->prepare($sql);
$statement->bindValue(____, "Southwest Airlines");
$statement->bindValue(____, 445);
$statement->execute();

▼View answer

"airlineName" and "flightNum"

"Southwest Airlines"는 :airlineName 매개변수에 바인딩되고, 445는 :flightNum 매개변수에 바인딩된다.

3) 제출된 양식에서 항공사 및 flightNum 값을 얻었다고 가정하자. 다음 코드에 어떤 문제가 있나?

$sql = "UPDATE Flight " .
       "SET AirlineName = '$_POST[airline]'" .
       "WHERE FlightNumber = $_POST[flighNum]";
           
$statement = $pdo->query($sql);

▼View solution

The code is susceptible to an SQL injection attack

 

- 제출된 폼에서 얻은 데이터를 SQL 문에 직접 삽입하면, 공격자가 SQL 문을 변경하는 데이터를 입력할 수 있는 취약점이 생긴다.
예: 항공편 번호 '101 OR TRUE'는 WHERE 절이 Flight 테이블의 모든 행과 일치하도록 만든다.
Prepared statement는 항공편 번호 '101 OR TRUE'에 대해 PDOException을 던지므로, SQL 삽입 공격을 방지한다.

---

Fetching values 값 가져오기

SELECT 문으로 PDOStatement를 실행하면 execute() 메서드는 커서 객체를 생성한다. 쿼리 결과는 fetch() 메서드를 통해 커서에서 가져온다.

• PDOStatement의 fetch() 메서드는 한 행의 데이터를 포함한 배열을 반환하거나, 선택된 행이 없으면 FALSE를 반환한다.
• 행의 값은 두 가지 방식으로 인덱싱된다:
  1. 열 이름으로 인덱싱
     Ex: ["FlightNumber"] => 350, ["DepartureTime"] => "08:15:00", ["AirportCode"] => "PEK".
  2. 열 번호로 인덱싱
     Ex: [0] => 350, [1] => "08:15:00", [2] => "PEK".

여러 행 반환 시 처리

• SELECT 문이 여러 행을 반환하면, fetch()를 루프 내에서 호출할 수 있다.
• fetch()를 호출할 때마다 결과 테이블의 다음 행을 반환한다.
• 더 이상 행이 없으면 fetch()는 FALSE를 반환한다.

fetch()의 선택적 스타일 매개변수

• fetch() 메서드는 선택적으로 스타일 매개변수를 사용하여 가져온 행을 다른 형식으로 반환할 수 있다.
• 일반적인 스타일 값은 다음과 같다:
  1. PDO::FETCH_NUM
     • 열 번호로만 인덱싱된 배열을 반환한다.
     • Ex: $row[0]는 첫 번째 열의 데이터이다.
  2. PDO::FETCH_ASSOC
     • 열 이름으로만 인덱싱된 연관 배열을 반환한다.
     • Ex: $row["colName"]는 colName 열의 데이터이다.
  3. PDO::FETCH_OBJ
     • 열 이름과 일치하는 속성 이름을 가진 객체를 반환한다.
     • Ex: $row->colName는 colName 열의 데이터이다.

1. Prepared statement가 execute()로 실행되어, PEK에서 출발하는 China Airlines 항공편을 선택한다.
2. fetch()는 결과 테이블에서 단일 행을 포함하는 배열을 반환한다.
3. while 루프는 모든 행이 처리될 때까지 각 행을 처리한다. 결과 값은 $row에서 열 이름을 사용하여 접근한다.

---

$flightNum = 140;
$sql = "SELECT AirlineName, DepartureTime, AirportCode FROM Flight " .
       "WHERE FlightNumber = ?";
$statement = $pdo->prepare($sql);
$statement->bindValue(___A___, $flightNum);
$statement->execute();

$row = $statement->___B___();
if ($row) {
   echo "$row[AirlineName] $flightNum departs from $row[___C___]";
}
else {
   echo "Flight not found";
}

1) What is identifier A?

2) What is identifier B?

3) What is identifier C?

1) 1

2) fetch

3) AirportCode or 2

$row의 각 키는 열 이름으로 지정되므로, AirportCode 열의 값은 $row[AirportCode]로 접근할 수 있다. 또한 $row[2]는 결과 테이블의 세 번째 열인 AirportCode에 해당한다.

---

Stored procedure calls 저장 프로시저 호출

저장 프로시저는 MySQL 절차적 SQL의 일부로, 데이터베이스에 저장되며 PHP 스크립트에서 호출할 수 있다.

1. 호출 방식
   • 저장 프로시저는 prepared Statement로 호출된다.
   • IN 매개변수에는 값을 바인딩한다.
2. OUT 및 INOUT 매개변수 처리
   • OUT 또는 INOUT 매개변수를 가진 저장 프로시저는 SQL 사용자 정의 변수로 호출해야 한다.
   • 출력 매개변수 값을 저장하는 사용자 정의 변수의 값을 가져오려면 두 번째 쿼리가 필요하다.

1. FlightCount는 특정 항공사의 항공편 수를 계산한다.
2. Prepared statement는 CALL 문을 사용하여 FlightCount를 "China Airlines" 항공사와 함께 호출한다.
3. execute()는 FlightCount를 호출하며, OUT 매개변수인 quantity가 @count 변수에 할당된다.
4. 두 번째 쿼리를 통해 @count 변수의 값을 가져온다.

---

공항과 항공사를 입력받아 마지막 항공편의 항공편 번호를 찾는 저장 프로시저를 참조하라. PHP 코드를 올바른 순서로 배치하라.

CREATE PROCEDURE LastFlight(IN airport CHAR(3), IN airline VARCHAR(45), OUT flightNum INT)
BEGIN
    SELECT FlightNumber
    INTO flightNum
    FROM Flight
    WHERE AirportCode = airport AND AirlineName = airline
    ORDER BY DepartureTime DESC
    LIMIT 1;
END;

1번 코드

$statement->execute();

2번 코드

$statement = $pdo->query ("SELECT @flightNum AS flightNum");
$row = $statement->fetch();

3번 코드

$statement = $pdo->prepare("CALL LastFlight(?, ?, @flightNum)");

4번 코드

echo "Flight number = $row[flightNum]";

5번 코드

$statement->bindValue(1, "DEN");
$statement->bindValue(2, "United Airlines");

▼View answer

3, 5, 1, 2, 4

Step1)

준비된 문장은 CALL 문을 사용하며, @flightNum은 LastFlight의 OUT 매개변수와 매칭된다.
Step2)
두 값이 준비된 문장에 바인딩된다.
Step3)
execute() 메서드는 저장 프로시저를 호출한다. 프로시저 실행이 완료되면 @flightNum에 마지막 항공편 번호가 할당된다.
Step4)
@flightNum의 값을 가져오기 위해 쿼리가 필요하다.
Step5)
$row 배열은 @flightNum의 값을 포함한다.