10.3 MySQLi (PHP)

Connecting to MySQL

MySQL Improved Extension(MySQLi)는 PHP 애플리케이션이 MySQL 데이터베이스와 상호작용할 수 있도록 한다. MySQLi는 절차적 및 객체 지향 구문을 지원하며, 여기서는 객체 지향 구문을 사용한다.
PHP 스크립트는 쿼리를 실행하기 전에 데이터베이스에 연결해야 한다. MySQL 서버에 연결하려면 PHP 스크립트가 다음 인수를 사용하여 mysqli 객체를 생성한다:

• MySQL 서버가 실행 중인 컴퓨터의 호스트 이름
• MySQL 사용자의 사용자 이름
• MySQL 사용자의 비밀번호
• 사용할 데이터베이스

MySQLi는 데이터베이스에 연결하거나 SQL 문을 실행하는 동안 오류가 발생하면 mysqli_sql_exception을 출력한다.

1. 웹 브라우저는 db.php라는 PHP 스크립트를 요청하기 위해 웹 서버에 요청을 보낸다. 웹 서버는 해당 PHP 스크립트를 실행한다.
2. $hostname은 MySQLi가 웹 서버와 동일한 컴퓨터에서 실행 중인 localhost의 MySQL 서버에 연결하도록 지정한다.
3. mysqli 생성자는 호스트 이름과 MySQL 사용자 이름, 비밀번호를 사용하여 MySQL 서버에 로그인하고 "test" 데이터베이스에 연결을 생성한다.
4. 연결이 성공적으로 생성되면 PHP 스크립트는 mysqli 객체를 사용하여 쿼리를 실행한다.
   예: SELECT 문으로 데이터베이스에서 학생 정보를 조회할 수 있다.

- mysqli 생성자가 올바른 사용자 이름과 비밀번호를 제공하지 않으면 mysqli_sql_exception이 발생한다.
이 예외는 치명적인 오류를 초래하며, 스크립트가 종료된다.
PHP가 오류 메시지를 표시하도록 구성된 경우, 예외의 오류 메시지가 웹 페이지에 표시된다.

오류 보고 PHP 8.1.0부터 MySQLi의 기본 오류 보고는 데이터베이스 연결 또는 SQL 문 실행 중 오류가 발생하면 예외를 출력하도록 설정되어 있다. 이전 PHP 버전에서는 기본적으로 예외 출력이 비활성화되어 있었다.

PDO PHP Data Objects(PDO)는 MySQL만 지원하는 MySQLi와 달리 여러 데이터베이스에서 작동할 수 있는 대안이다. PDO 구문은 MySQLi와 유사하며, PDO는 일부 개발자가 선호하는 이름 지정 매개변수를 지원한다.

 

---

SELECT statement

MySQL 서버와의 연결을 설정한 후, SQL 문은 mysqli 객체의 query() 메서드를 사용하여 실행할 수 있다.

• SQL이 SELECT 문인 경우, query()는 선택된 행을 포함하는 mysqli_result 객체를 반환한다.
• SQL 문이 잘못 지정되면 mysqli_sql_exception이 발생한다.

데이터 선택이 성공적으로 완료되면, mysqli_result 객체의 fetch_row() 메서드는 한 행의 값을 포함하는 문자열 배열을 반환한다.

• fetch_row()를 호출할 때마다 다음 행을 반환한다.
• 더 이상 행이 없으면 fetch_row()는 NULL을 반환한다.

1. 데이터베이스 연결을 생성한 후, query()는 SELECT 문을 실행하고 mysqli_result 객체를 반환한다.
2. $result는 선택된 모든 행을 포함하는 mysqli_result 객체로 할당된다.
3. fetch_row()는 첫 번째 행을 반환하는 배열을 제공하며, $row[0]은 첫 번째 열(stuid), $row[1]은 두 번째 열(name), $row[2]는 세 번째 열(gpa)에 해당한다.
4. fetch_row()를 호출할 때마다 $result에서 다음 행을 가져오며, 더 이상 행이 없을 때까지 반복된다.

mysqli_result의 fetch_assoc() 메서드는 fetch_row()와 유사하지만, fetch_assoc()은 열 이름을 키로 사용하는 연관 배열을 반환한다.

Figure 10.3.1: Using fetch_assoc() to retrieve values from the selected rows

---

SQL injection

SQL 문은 파일, 데이터베이스, 사용자 입력 등에서 가져온 값을 사용하여 동적으로 생성될 수 있다.
Ex: 아래 코드에서 URL 쿼리 문자열 매개변수가 SQL 문에 대체되어, GPA가 최소 및 최대 쿼리 문자열 매개변수 사이에 있는 학생만 표시된다.
악성 사용자는 개발자가 예상하지 못한 입력값을 웹 애플리케이션에 제공할 수 있다.
SQL 삽입 공격은 악성 사용자가 웹 애플리케이션에 SQL 문의 의도를 변경하는 입력값을 입력하는 경우를 말한다.
SQL 삽입 공격은 민감한 데이터가 유출되거나 중요한 데이터가 삭제되는 결과를 초래할 수 있다.

1. $minGpa와 $maxGpa는 URL 쿼리 문자열에서 "3"과 "4"로 값이 할당된다.
2. $sql은 $minGpa와 $maxGpa 값이 WHERE 절에 대체된 SELECT 문으로 할당된다.
3. query()는 SELECT 문을 실행하여 GPA가 3에서 4 사이인 두 학생을 반환하고, 스크립트는 학생 목록을 출력한다.
4. 쿼리 문자열에서 "3"을 "3+OR+1"로 대체하면 SELECT 문의 의도가 변경된다.
5. SQL에서 0이 아닌 피연산자는 true로 평가되므로 SELECT 문의 WHERE 절은 모든 행에 대해 true가 된다.
6. SQL 삽입 공격으로 인해 GPA가 3에서 4 사이인 학생이 아닌, 모든 학생의 이름과 GPA가 표시된다.

---

Prepared statements

Prepared statement 또는 parameterized statement는 query() 메서드를 사용하는 것보다 동일한 문장을 반복적으로 더 효율적으로 실행하기 위해 사용된다. 많은 개발자는 입력값을 SQL 문에 삽입해야 할 때 SQL 삽입 공격을 대부분 방지할 수 있기 때문에 prepared statement를 사용한다.
Prepared statement를 생성하는 3단계

1. Prepare
   • mysqli의 prepare() 메서드는 SQL 문에서 물음표(?)를 placeholder로 사용하는 parameterized SQL문을 준비한다.
   • 이 메서드는 statement 객체를 반환한다.
2. Bind
   • statement 객체의 bind_param() 메서드는 SQL 문의 물음표로 표시된 placeholder에 매개변수를 바인딩한다.
   • 첫 번째 인수는 바인딩할 매개변수의 타입을 지정하는 문자열로, "ids"는 정수("i"), 부동소수점("d"), 문자열("s") 매개변수를 나타낸다.
   • Ex: "ids"는 bind_param() 호출에서 지정된 인수와 SQL 문을 일치시킨다.
3. Execute
   • statement 객체의 execute() 메서드는 바인딩된 매개변수로 SQL 문을 실행한다.

SELECT 문의 경우

• SQL 문이 SELECT 문인 경우, statement 객체의 get_result() 메서드는 선택된 데이터를 포함하는 mysqli_result 객체를 반환한다.

1. $minGpa와 $maxGpa는 URL의 쿼리 문자열에서 "3"과 "4"로 할당된다.
2. prepare()는 SELECT 문에서 생성된 statement 객체를 반환한다.
3. bind_param()의 첫 번째 인수 "ii"는 두 매개변수가 두 개의 정수로 변환되어야 함을 나타낸다. $minGpa와 $maxGpa는 prepared statement의 물음표에 바인딩된다.
4. execute()는 GPA가 3에서 4 사이인 모든 학생을 검색하는 SELECT 문을 실행한다.
5. get_result()는 선택된 모든 행을 포함하는 mysqli_result를 반환한다.
6. 학생 이름과 GPA가 정렬된 목록으로 표시된다.

---

INSERT, UPDATE, and DELETE statements

mysqli의 query() 메서드는 INSERT, UPDATE, DELETE 문을 실행할 수 있다.
statement 객체의 execute() 메서드 또한 INSERT, UPDATE, DELETE 문을 실행할 수 있다.
mysqli 객체의 affected_rows 속성은 SQL 문에 의해 삽입, 수정, 또는 삭제된 행의 수를 반환한다.

Figure 10.3.2: query() executing an INSERT statement and displaying the number of inserted rows

사용자로부터 SQL 문에 사용할 데이터를 받을 때, 입력값을 먼저 정리(sanitize)하는 것이 좋은 습관이다.
Ex: 사용자가 숫자를 입력해야 하는 상황에서 단어를 입력하면, PHP 스크립트는 SQL 문에 잘못된 데이터를 삽입하기 전에 잘못된 입력을 감지해야 한다. Prepared statement는 모든 입력 데이터를 자동으로 정리한다.
real_escape_string() 메서드

• 대안으로, mysqli 객체의 real_escape_string() 메서드를 사용하여 문자열 입력을 정리할 수 있다.
• 이 메서드는 단일 인용부호, 이중 인용부호, 줄바꿈 문자와 같이 SQL 문에 삽입될 때 문제가 될 수 있는 특정 문자를 이스케이프 처리한다.
  예: real_escape_string("Tom O'Reily")는 단일 인용부호를 이스케이프 처리하여 문자열 "Tom O'Reily"를 반환한다.

Figure 10.3.3: PHP script inserts submitted student data

Auto-increment ID 자동 증가 ID 개발자는 각 삽입(insert)마다 고유 ID를 지정할 필요를 없애기 위해 자동 증가 primary key를 사용하는 테이블을 자주 생성한다. mysqli의 insert_id 속성은 마지막으로 삽입된 행의 자동 증가 ID를 반환한다. Ex: INSERT 문에서 song ID를 지정하지 않아도 되는 이유는, song ID가 자동 증가되기 때문이다. $sql = "INSERT INTO song (title, artist, year) VALUES ('Hey Jude', 'The Beatles', 1968)"; $mysqli->query($sql); echo "ID is " . $mysqli->insert_id;