흑역사
2020년 초에 개발자 커리어를 시작하게 해주었던 첫 직장은 여전히 php 5.x 버전을 쓰고 있었다. 그때는 개발업무도 다 처음이고, php도 처음 해봤었다. 그러므로 php 버전에 따른 차이를 지금보다도 훨씬 잘 몰랐고 그게 중요한지도 몰라서 크게 관심도 없었으나 심지어 4.x 버전인 서버도 있었던 것 같다.
당연히, MySQL DB연결에는 mysql 함수를 썼다.
mysql_connect(), mysql_query(), mysql_fetch_array()...
SQL인젝션이란 단어도 들어보기 전 시절
mysql로 시작하는 이 함수들은 PHP 5.5.0 버전에서 deprecated, PHP 7에서는 is removed! 삭제(!)됐다. 물론 여전히 레거시에서(우리 회사 한 서버에도) 흔히 볼 수 있지만 이제 와서 굳이 이걸 공부할 필요는 없다 잊어버리자.(이미 옛날 자료들도 많이 있을 것이다)
여담: 레거시를 보다보면 mysql함수 앞에 @가 붙어있는 것을 많이 보게 되는데, 함수 앞에 @를 쓰면 해당 함수 실행시 발생하는 오류/경고 메시지를 표시하지 않는다. mysql 함수를 쓰면 거의 무조건 경고가 출력되기 때문에
mysqli (vs PDO)
지금은 그래도 php버전 7.3.16(서버마다 조금씩 상이하지만 메인서버 기준)을 쓰는 곳에 와서 일하고 있는데, 여기서도 DB가 MySQL과 MariaDB로만 이루어져있다보니 mysqli를 주로 사용했다.
이름에서 알 수 있듯이 mysqli는 MySQL/MariaDB 에서만 사용가능하다. 반면 PDO는 이 둘을 포함하여 다른 DB들에도 적용할 수 있다. 여러 종류의 DB를 사용하거나 그렇게 변경될 가능성이 있는 프로젝트라면 말할 것도 없이 PDO를 쓰는 것이 나을 것이다.
이 외에도 SQL인젝션 등을 방지하기 위해 사용하는 prepared statement(준비된 쿼리) 작성시 PDO가 더 편한 면이 있다.(관련 설명 참조)(면접 가면 PDO 써봤냐는 질문 많이 받는다.)
mysqli의 장점이 없는 것은 아닌데, MySQL DB만 특화되어있어서 다른 DB와는 다른 MySQL DB만의 기능같은 것을 활용할 때 유리하다고 한다. 그리고 PDO는 절차지향 스타일을 제공하지 않는다.(이름부터 PHP Data Objects다)
prepared statement란? 참조: https://iksflow.tistory.com/127
DB 연결하기
new mysqli 라는 것은 mysqli 객체를 생성하는 코드이고, 객체 생성시 생성자(public mysqli::__construct)에 의해 전달한 인자parameter들이 db와 연결된다.
php.net 의 매뉴얼을 읽어보면 mysqli는 Object-oriented style(OOP, 객체지향 스타일)과 Procedual style(절차지향 스타일)로 나누어 설명되어있다.
1. 객체지향 스타일 new mysqli
$host = "localhost";
$username = "root";
$password = "1234";
$databaseName = "mydb";
$db = new mysqli($host, $username, $password, $databaseName);
if ($db->connect_errno) {
echo "Failed to connect to MySQL: " . $db->connect_error;
exit();
}
2. 절차지향 스타일 mysqli_connect
$host = "localhost";
$username = "root";
$password = "1234";
$databaseName = "mydb";
$db = mysqli_connect($host, $username, $password, $databaseName);
if (!$db) {
$error = mysqli_connect_error();
$errno = mysqli_connect_errno();
echo "$errno: $error\n";
exit();
}
이외에 MySQL 기본 포트 3306 을 다른 포트로 변경했다면 다섯번째 인자에 새 포트번호를 넣어주면 된다.
SELECT 및 결과 반환하기
예시 테이블
| id | code | state |
|---|---|---|
| 49 | ABC | Y |
| 50 | ABD | Y |
| 51 | ABE | N |
결과 객체로 반환 - fetch_object
1. 객체지향 스타일
$qry = "SELECT * FROM mytable WHERE state = 'Y'";
$res = $db->query($qry);
//객체로 받기, 검색결과가 1개일 경우(혹은 맨 첫번째 결과만 사용할 경우)
$row = $res->fetch_object();
print_r($row);
/* 결과 예시
stdClass Object
(
[id] => 49
[code] => ABC
[state] => Y
)
*/
//객체로 받기, 검색결과가 여러 row일 경우
while($row = $res->fetch_object()){
print_r($row);
}
/* 결과 예시
stdClass Object
(
[id] => 49
[code] => ABC
[state] => Y
)
stdClass Object
(
[id] => 50
[code] => ABD
[state] => Y
)
*/2. 절차지향 스타일
$qry = "SELECT * FROM mytable WHERE state = 'Y'";
$res = mysqli_query($db, $qry);
// 객체로 받기, 결과가 한 줄일 경우
$row = mysqli_fetch_object($res)
// 여러 줄일 경우
while($row = mysqli_fetch_object($res)){
print_r($row);
}결과 배열로 반환 - fetch_array, fetch_all
배열로 저장할 때는 fetch_array()를 쓰면 컬럼 순서에 따라 숫자 인덱스값을 가지는 배열과 테이블 컬럼명(필드명)을 키로 하는 연관배열을 동시에 갖고 있게 된다. 숫자 인덱스를 갖는 배열로만 저장하고 싶으면 fetch_row(), 연관배열로만 저장하고 싶으면 fetch_assoc()를 쓰면 된다.
1. 객체지향 스타일
//배열로 받기 (여러 row를 받는 방법은 위와 동일)
$row = $res->fetch_array();
print_r($row);
/* 결과 예시
Array
(
[0] => 49
[id] => 49
[1] => ABC
[code] => ABC
[2] => Y
[state] => Y
)
*/
//2차원 배열로 받기 (여러 row를 배열 속의 배열로 저장한 데이터로 받는 방법)
$rows = $res->fetch_all();
print_r($rows)
/* 결과 예시
Array
(
[0] => Array
(
[0] => 49
[1] => ABC
[2] => Y
)
[1] => Array
(
[0] => 50
[1] => ABD
[2] => Y
)
)
*/
2. 절차지향 스타일
// 배열로 받기, 결과가 한 줄일 경우
$row = mysqli_fetch_array($res);
// 여러 줄일 경우
while($row = mysqli_fetch_array($res)){
print_r($row);
}
// 2차원 배열로 받기
$rows = mysqli_fetch_all($res);
* 주의: 다음처럼 하면 무한루프를 돌게 되니 절대 하지 말 것.
$row = $res->fetch_object();
while($row){
print_r($row);
//아무도 날 멈출 수 없다
}* 한글이 깨져서 나올 경우: mysql 인코딩 문제인 경우가 많다. 다음과 같은 코드를 db연결 후, 쿼리 날리기 전에 추가한다.
//객체지향 스타일
$db->query('set names utf8');
//절차지향 스타일
mysqli_query($db, 'set names utf8');num_rows SELECT한 row의 수
SELECT 후 얻은 $res (mysqli_result)엔 뭐가 저장돼있을까?
var_dump($res)
/* 결과 예시
object(mysqli_result)#3 (5) {
["current_field"]=>
int(0)
["field_count"]=>
int(3)
["lengths"]=>
NULL
["num_rows"]=>
int(2)
["type"]=>
int(0)
}
*/
print_r($res->num_rows);
/* 결과 예시
2
*/
쿼리 후 반환된 mysqli_result 객체의 num_rows 속성을 이용해 SELECT 결과가 몇 개나 나왔는지만 간단하게 확인이 가능하다.
간단한 INSERT/UPDATE 쿼리
쿼리 날리는 과정 자체는 SELECT 쿼리랑 다를 게 없다.
1. 객체지향 스타일
$qry = "INSERT test SET id = '49', code = 'ABC', state = 'Y'";
$res = $db->query($qry);2. 절차지향 스타일
$qry = "UPDATE test SET state = 'N' WHERE id = '49'";
$res = mysqli_query($db, $qry);INSERT/UPDATE/DELETE 후 얻은 $res (mysqli_result)
var_dump($res);
/* 결과 예시
bool(true)
*/해당 쿼리의 성공시 true, 실패시 false를 반환한다.
이스케이핑 mysqli_real_escape_string()
SQL인젝션
Secure Coding, 보안은 언제나 중요한 문제다. 특히 DB에 INSERT나 UPDATE할 때는 SQL인젝션 공격에 주의해야한다. SQL인젝션이란 쿼리문으로 인식될 수 있는 문자열을 넣어 의도하지 않은 쿼리가 작동하게 만드는 공격이다. 예를 들어 아래와 같은 코드를 보자.
$id = $_GET['id']; // GET 요청시 URL 쿼리스트링의 id 파라미터의 인자를 받아오는 코드
$qry = "UPDATE test SET state = 'N' WHERE id = '{$id}'";
$res = $db->query($qry);URL 파라미터에 ?id=49 를 넣어 보내면 $id에 49가 저장되어 쿼리문은 $qry = "UPDATE test SET state = 'N' WHERE id = '49'";가 된다. 49라는 id값을 가지고 있는 데이터의 state값을 N으로 바꿔주는 쿼리다.
이용자는 state를 N으로 바꾸고 싶은 id값을 보내줄 거고, 없는 id를 보내면 UPDATE문이 작동하지 않을테니 별 문제가 없어보인다.
하지만 이용자가 ?id=49'%20OR%20state='Y 과 같이 보낸다면 어떻게 될까?(%20 은 띄어쓰기를 의미한다.) $id에는 id=49' OR state='Y 라는 문자열이 저장될 것이고 이게 쿼리문 안에 들어가면 $qry = "UPDATE test SET state = 'N' WHERE id = '49' OR state = 'Y'"; 라는 쿼리문이 완성된다.
이게 실행되면 state가 Y인 모든 데이터의 state가 N으로 바뀐다.
조금만 상상력을 발휘해보면 이게 얼마나 위험한 일인지 알 수 있다. 회원가입 등 유저로부터 데이터를 입력 받아 DB에 넣을 일은 항상 있다. 유저가 우리 DB 테이블의 컬럼명을 어떻게 알겠냐고 하겠지만 뻔한 컬럼명도 많다. password, userid, address 등 컬럼명도 굉장히 특이하고 이상하고 창의적인 걸 쓰지는 않기 때문이다.(도대체 뭐하는 컬럼인지 알기 어려워지기 때문에 써도 곤란하다.)
쿼리문에 들어갈 문자열의 이스케이핑
이제 우리는 들어오는 문자열을 그대로 변수에 넣어 쿼리문 안에 넣었다간 얼마나 끔찍한 일이 벌어질 수 있는지 안다. 그럼 어떻게 해야하느냐? 쿼리문의 일부가 아닌 단순한 문자열로 만들어야 한다. 특수한 의미(기능)를 가지는 ' " % 등의 기호에서 기능을 제거하고 단순한 문자로 만드는 것을 이스케이핑이라고 한다. 이를 위해 보통 해당 기호 앞에 역슬래시를 붙인다.
php mysqli 에서는 이스케이핑을 위해 real_escape_string() 을 제공하고 있다. 다음과 같이 사용한다.
$id = $_GET['id']; // GET 요청시 URL 쿼리스트링의 id 파라미터의 인자를 받아오는 코드
// 객체지향형 스타일
$qry = sprintf("UPDATE test SET state = 'Y' WHERE id = '%s'",
$db->real_escape_string($id));
$res = $db->query($qry);
// 절차지향형 스타일
$qry = sprintf("UPDATE test SET state = 'Y' WHERE id = '%s'",
mysqli_real_escape_string($db, $id));
$res = mysqli_query($db, $qry);
// 또는 아래처럼 이스케이핑한 문자열을 별도의 변수에 넣은 뒤 사용해도 된다.
$escapedId = $db->real_escape_string($id);
$escapedId = mysqli_real_escape_string($db,$id);
$qry = "UPDATE test SET state = 'N' WHERE id = '{$escapedId}'";Prepared Statement(준비된 쿼리)
Prepared Statement를 이용할 경우 bind_param을 통해 변수 내 문자열의 (쿼리로 섞여서 해석되지 않게) 이스케이핑도 해주고, 이름처럼 미리 쿼리를 준비해놓고 변수 값만 새로 대입하는 방식으로 실행이 가능하다. 전체 구조는 같고 특정한 값만 달라지는 쿼리를 계속해서 실행해야할 때 첫번째 실행시 쿼리를 캐싱함으로써, 쿼리를 분석하여 실행계획을 세우는 단계를 건너뛰어 효율을 높이는 것이다.
1. 객체지향 스타일 mysqli_stmt->bind_param
$qry = "UPDATE test SET state = 'N' WHERE id = ? ";
$stmt = $db->stmt_init();
$stmt->prepare($qry);
//위 2줄 또는 아래 1줄처럼 해서 statement(mysqli_stmt)객체를 만든다.
$stmt = $db->prepare($qry);
$stmt->bind_param("s",$id);
// 여기서 s의 의미는 string으로 $id가 문자열 타입이라는 의미다.($qry에 물음표 하나, bind 하나!)
// 만약 bind할 param이 2개이고 두번째 param의 값은 int 타입이라면 ("si", $id, $secondVar)
$result = $stmt->execute(); // SELECT 외의 다른 쿼리는 여기 execute()에서 성공시 true, 실패시 false 리턴
$res = $stmt->get_result(); // SELECT일 경우 get_result() 가능
//SELECT문일 경우 위 SELECT 예시의 $res(mysqli_result)에 대해 하던 작업을 하면 된다.
$row = $res->fetch_object();2. 절차지향 스타일 mysqli_stmt_bind_param
$qry = "UPDATE test SET state = 'N' WHERE id = ? ";
$stmt = mysqli_stmt_init($db);
mysqli_stmt_prepare($stmt,$qry);
//위 2줄 또는 아래 1줄처럼 해서 statement(mysqli_stmt)객체를 만든다.
$stmt = mysqli_prepare($db,$qry);
mysqli_stmt_bind_param($stmt,"s",$id);
$result = mysqli_stmt_execute($stmt); // SELECT 외의 다른 쿼리는 여기 execute()에서 성공시 true, 실패시 false 리턴
$res = $stmt->get_result(); // SELECT 쿼리일 경우 get_result() 가능
1. 객체지향 스타일 mysqli_stmt->bind_result
$state = 'Y';
$qry = "SELECT id, code FROM cmsdb.test WHERE state = ? ";
$stmt = $db->prepare($qry);
$stmt->bind_param("s",$state);
$stmt->execute();
$stmt->bind_result($id, $codeOfId); // 순서대로 모든 SELECT한 컬럼수만큼 변수를 할당
while($stmt->fetch()){
echo $id;
echo $codeOfId;
}
/* 결과예시
49ABC50ABD
*/2. 절차지향 스타일 mysqli_stmt_bind_result
mysqli_stmt_bind_result($stmt, $idd, $code);
while(mysqli_stmt_fetch($stmt)){
echo $idd;
echo $code;
}
/* 결과예시
49ABC50ABD
*/??? (추가 탐구 과제)
예시 쿼리를 실행해보다 알게 된 사실.
이스케이핑을 한 쿼리문 UPDATE test SET state = 'N' WHERE id = '49\' OR state=\'Y'를 실행했을 때 이스케이핑하기 전처럼 state='Y'인 데이터 전체가 다 N으로 바뀌진 않지만 id='49'에 대하여서는 state가 N으로 UPDATE된다!
근데 이스케이핑을 mysqli_real_escape_string을 이용해서 하고 query()로 실행시켰을 때는 id='49'에 대해서만 UPDATE가 됐는데 실행결과가 true로 나오고, mysqli_stmt_bind_param을 이용한 뒤 $res = mysqli_stmt_execute($stmt);했을 때도 같은데, $res = $stmt->get_result();했을 때는 똑같이 id='49'에 대해서만 UPDATE되면서 실행결과가 false로 나온다.
그리고 위의 true와 false 결과는 쿼리문을 SET state = 'Y'로 변경하여 실제로는 아무 값도 UPDATE가 되지 않는 상황으로 만들어도 두 경우에선 true가, 마지막 케이스(객체지향 스타일로 실행)에서만 false가 리턴됐다.
이게 대체 무슨 일이지..? (HeidiSQL 에서 쿼리를 실행해보면 역시 실행은 되되, 다음과 같은 에러메시지가 뜬다.)
Warning: Truncated incorrect DOUBLE value: '49' OR state='Y'
