SQL문
웹 페이지에서 데이터베이스를 조작하는 방법은 일반적으로 웹 페이지에서 PHP가 동작할 때 폼 컨트롤 버튼을 눌러 PHP 파일로 데이터를 송신하는 방법(POST)과 링크에 파라미터를 붙여 PHP 파일로 데이터를 송신하는 방법(GET)이 있다.
송신된 데이터는 PHP의 슈퍼 글로벌 변수 $_POST, $_GET으로 받을 수 있다. 데이터베이스 조작에 필요한 데이터를 받거나, 데이터를 가지고 SQL문의 결과를 받는다.
| 폼에서 송신 또는 링크에서 송신 |
| ↓ |
| 데이터의 수신 |
| ↓ |
| SQL문의 구성 |
| ↓ |
| SQL문의 발행 |
| ↓ |
| 결과 수신 |
| ↓ |
| 결과표시 |
데이터베이스를 조작하는 SQL문을 작성하려면 필요한 데이터를 넘겨 받아야 한다.
| 조작 | 필요한 데이터 |
| 입력 | 데이터와 칼럼 위치 |
| 검색 | 검색 조건에 사용할 데이터 |
| 수정 | 수정 후의 데이터와 수정 조건 |
| 삭제 | 삭제 조건에 사용할 데이터 |
데이터베이스 조작에는 입력, 검색, 수정, 삭제 이외에 집계(카운트, 합계, 평균, 최대, 최소)를 위한 조작이 존재한다. SQL에는 이를 처리할 수 있도록 group by와 같은 문으로 구성된 집계 함수를 제공하기도 한다.
히어 도큐먼트(Here Document) 구문
|
1
2
3
4
|
$sql = <<<EOS
INSERT INTO member(last_name, first_name, age)
VALUES('고', '길동', 21)
EOS;
|
cs |
SQL문은 변수에 할당한다. SQL문이 짧을 때는 간단하게 변수에 할당할 수 있지만, 처리에 따라서는 SQL문이 꽤 길 때도 있다. 이 경우에 히어 도큐먼트 구문을 사용해 긴 SQL문을 틀리지않고 보기 쉽게 기술할 수 있다. 히어 도큐먼트는 시작점에 <<<EOS를 기술하고 개행하여 SQL문을 기술하고 마지막으로 EOS;로 마친다.
EOS에는 임의의 문자열을 붙일 수 있지만 영문자나 _만으로 작성된 문자는 오류가 발생한다. <<<와 EOS 사이와 EOS; 앞뒤에 탭이나 공백과 같은 문자열이 있어도 오류가 발생한다.
마지막으로 PHP에서 발행하는 SQL문은 반드시 ;으로 마칠 필요는 없다.
변수가 있는 SQL의 구성
|
1
2
3
4
5
6
7
8
9
|
// 적절한 기술 방법, 값이 직접 기술되어 있는 SQL문
$sql = "INSERT INTO
member(last_name, first_name, age)
VALUES('고', '길동', 21)";
// 위험한 기술 방법, 값이 글로벌 변수에 기술되어 이ㅆ는 SQL문
$sql = "INSERT INTO
member(last_name, first_name, age)
VALUES('".$_POST['last_name']."', '".$_POST['first_name']."', ".$_POST['age'].")";
|
cs |
위의 코드는 데이터를 등록할 때 사용하는 INSERT문이다. 1번의 '값이 직접 기술되어 있는 SQL문'은 터미널이나 명령 프롬프트에서 입력할 때처럼 기술하면 된다. 또한 회원 등록 폼에서 사용자가 데이터를 송신하려면 외부에서 전달받은 데이터를 SQL에 포함해야 한다.
프리페어드 스테이트먼트
|
1
2
3
4
5
6
7
8
9
|
// [:이름]을 사용하는 경우
$sql = "INSERT INTO
member(last_name, first_name, age)
VALUES(:last_name, :first_name, :age)";
// [?]를 사용하는 경우
$sql = "INSERT INTO
member(last_name, first_name, age)
VALUES(?, ?, ?)";
|
cs |
플레이스 홀더(placeholder)를 이용한 구성, SQL을 안전하게 실행하려면 프리페어드 스테이트먼트라는 기능을 사용해야한다. 프리페어드 스테이트먼트는 SQL로 구성되는 값만 설정할 수 있어 안전하게 처리할 수 있다.
같은 SQL문을 반복할 때는 성능 최적화도 기대할 수 있다. 프리페어드 스테이트먼트는 SQL문의 템플릿을 먼저 준비하고 값과 SQL 부분을 명확하게 분리하는 것으로 안전을 보장한다. SQL문과 외부의 값을 구별하기 위해 값 부분에 플레이스 홀드라는 식별자를 :이름 또는 ?라는 두 종류의 형식을 선택하여 저장한다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
<?php
// ':이름' placeholder
$sql = "INSERT INTO member(last_name, first_name, age) VALUES(:last_name, :first_name, :age)";
$stmh = $pdo->prepare($sql);
$stmh->bindValue(':last_name', $_POST['last_name']);
$stmh->bindValue(':first_name', $_POST['first_name']);
$stmh->bindValue(':age', $_POST['age']);
$stmh->execute();
// '?' placeholder
$sql = "INSERT INTO member(last_name, first_name, age) VALUES(?, ?, ?)";
$stmh = $pdo->prepare($sql);
$stmh->bindValue(1, $_POST['last_name']);
$stmh->bindValue(2, $_POST['first_name']);
$stmh->bindValue(3, $_POST['age']);
$stmh->execute();
?>
|
cs |
해설을 위한 코드이기 때문에 작동은 하지 않는다. perpare 메서드로 작성한 SQL에 인수를 설정하여 실행하면 SQL을 해석해 캐싱한다. 같은 SQL을 두 번째로 실행할 때부터 이 캐시를 사용하기 때문에 속도가 개선이 된다.
SQL의 각 플레이스 홀더와 값은 바인드를 해야 하는데 이때 이용할 수 있는 메서드로 bindValue와 bindParam이 있다. $pdo에는 PDO 클래스의 객체가 할당되어 있고 perpare 메서드로 $sql에 할당된 SQL문을 해석한다.
스테이트먼트 핸들러를 반환 값으로 돌려받아 $stmh에 할당된다. PDOStatement 클래스의 bindValue 메서드를 이용해 첫 번째 인수에는 플레이스 홀더에 사용한 이름을 두 번째 인수에는 외부에서 전달받은 값을 지정한다. bindValue 메서드 안의 식별자는 '로 묶여있다.
마지막으로 execute();로 SQL문을 실행하면 된다. ? 플레이스 홀더의 경우는 대응하는 순서대로 숫자를 적으면 된다.
데이터 입력
송신 폼은 대상이 되는 테이블 구조에 맞춰야 하기 때문에 먼저 테이블의 구조를 확인해야 한다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
<!-- form.html -->
<!DOCTYPE html>
<html>
<head>
<title>PHP 테스트</title>
<meta http-equiv="Content-type" content="text/html; charset=UTF-8">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<font size="4">PHP 테스트</font>
<form name="form1" method="POST" action="view.php">
성:<br>
<input type="text" name="last_name"><br>
이름:<br>
<input type="text" name="first_name"><br>
연령:<br>
<input type="text" name="age"><br>
<input type="submit" value="송신">
</form>
</body>
</html>
|
cs |
테이블을 확인을 하고 위와 같은 입력받을 form.html 파일을 생성한다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
<!-- view.php -->
<html>
<head>
<title>PHP 테스트</title>
</head>
<body>
<?php
$db_user = "root"; // 사용자명
$db_pass = "password"; // 패스워드
$db_host = "localhost"; // 호스트명
$db_name = "phpdb"; // 데이터베이스명
$db_type = "mysql"; // 데이터베이스 종류
$dsn = "$db_type:host=$db_host; dbname=$db_name charset=UTF8";
try{
$pdo = new PDO($dsn, $db_user, $db_pass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
print "접속하였습니다.";
}catch(PDOExecption $Exception){
die('오류:'.$Exception->getMessage());
}
try{
$pdo->beginTransaction();
$sql = "INSERT INTO member(last_name, first_name, age) VALUES(:last_name, :first_name, :age)";
$stmh = $pdo->prepare($sql);
$stmh->bindValue(':last_name', $_POST['last_name'], PDO::PARAM_STR);
$stmh->bindValue(':first_name', $_POST['first_name'], PDO::PARAM_STR);
$stmh->bindValue(':age', $_POST['age'], PDO::PARAM_INT);
$stmh->execute();
$pdo->commit();
print "데이터를 ".$stmh->rowCount()."건 입력하였습니다.<br>";
}catch(PDOExecption $Exception){
$pdo->rollBack();
print "오류:".$Exception->getMesssage();
}
?>
</body>
</html>
|
cs |
접속은 PDO를 사용한다. 이전 접속방법과 비슷하지만 DSN(데이터 소스명: 접속 정보 문자열)을 변수에 넣어 구성한다. 변수는 "로 묶여 있어 실행시 문자열이 된다. try-catch를 이용할 수 있도록 오류 모드를 설정한다. 또한 프리페어드 스테이트먼트를 사용할 수 있도록 에뮬레이터 기능을 FALSE로 한다.
MySQL 서버에 접속 후, try 블록 안에서 데이터를 입력하는 처리를 실행한다. 트랙잭션 처리와 bindValue에 세 번째 인수를 설정하고 있는 부분이다. 트랜잭션은 여러 처리를 하나의 처리로 묶은 것을 의미한다. 위의 코드에서 두 번째 try-catch문 부분이 트랜잭션이다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
트랜잭션 시작
입력 처리1
입력 처리2
수정 처리
삭제 처리
트랜잭션 종료
<?php
try{
$pdo->beginTransaction();
$sql = "INSERT INTO member(last_name, first_name, age) VALUES(:last_name, :first_name, :age)";
$stmh = $pdo->prepare($sql);
$stmh->bindValue(':last_name', $_POST['last_name'], PDO::PARAM_STR);
$stmh->bindValue(':first_name', $_POST['first_name'], PDO::PARAM_STR);
$stmh->bindValue(':age', $_POST['age'], PDO::PARAM_INT);
$stmh->execute();
$pdo->commit();
print "데이터를 ".$stmh->rowCount()."건 입력하였습니다.<br>";
}catch(PDOExecption $Exception){
$pdo->rollBack();
print "오류:".$Exception->getMesssage();
}
?>
|
cs |
위의 코드가 앞서 설명한 트랜잭션 부분이다. beginTransaction(); 메서드를 사용해 트랜잭션을 시작하고 SQL을 실행한 후에 commit(); 메서드로 처리를 확정시킨다. 오류가 발생했다면 catch 블록 안에 rollback(); 메서드에 따라 원상태로 돌리고 종료를 시킨다.
bindValue 메서드의 세 번째 인수 PDO::PARAM_STR은 데이터형이라는 것이다. 바인드 하는 값이 문자열인지 수치인지를 명시하여 SQL 인젝션 공격에 대한 안정성을 높히고 있다.
| 정의된 상수 | |
| PDO::PARAM_BOOL | 불린(boolean) 값을 나타낸다. |
| PDO::PARAM_NULL | SQL의 NULL 값을 나타낸다. |
| PDO::PARAM_INT | SQL의 INTEGER(정수)형을 나타낸다. |
| PDO::PARAM_STR | SQL의 CHAR형, VARCHAR형 등의 문자열을 나타낸다. |
PDOStatement 클래스의 rowCount 메서드는 SQL을 실행한 결과에 대한 건수를 반환한다. 여기에서는 등록 건수를 반환한다.
'프로그래밍 공부 > PHP' 카테고리의 다른 글
| PHP - 음력과 양력을 구하는 소스 (0) | 2020.07.29 |
|---|---|
| PHP - PHP에서 데이터베이스 조작 下 (0) | 2020.02.13 |
| PHP - PHP에서 데이터베이스 조작 上 (0) | 2020.02.11 |
| PHP - 데이터베이스 조작 (0) | 2020.02.10 |
| PHP - 데이터베이스 (0) | 2020.02.07 |
