Oracle과 PHP의 변수 바인딩
저자 Larry Ullman
변수 바인딩을 통해 오라클 기반 PHP 애플리케이션의 성능과 보안 수준을 개선할 수 있습니다.
아티클 관련 다운로드:
Oracle Database 10g
Oracle Instant Client
Oracle JDeveloper PHP Extension
Zend Core for Oracle
여러분들도 이미 잘 알고 계시다시피, 오늘날 개발되는 대부분의 웹 사이트는 어떤 형태로든 데이터베이스에 의존하고 있습니다. 구축 대상 사이트가 엄청난 수의 문서와 정보로 구성된 포럼 또는 전자상거래 웹 사이트든, 또는 단순히 방문자의 피드백을 받기 위한 페이지든, 데이터베이스는 반드시 필요합니다. 데이터베이스는 매우 유용한 테크놀로지이지만, 웹 애플리케이션 환경에서 성능 또는 보안에 관련한 문제를 야기하는 원인으로 작용하기도 합니다. PHP에서 변수를 바인딩하는 테크닉을 미리 알아두신다면, 성능, 보안 측면에서 큰 효과를 보실 수도 있습니다.
웹 프로젝트에 대한 벤치마크를 수행해 본 경험이 있는 사용자라면, 데이터베이스 작업이 매우 많은 리소스를 요구하는 프로세스라는 사실을 잘 이해하고 계실 것입니다. 데이터베이스에 대해 쿼리를 실행한 경우, 오라클 데이터베이스는 먼저 쿼리에 대한 파싱(parsing) 작업을 통해 문법적인 오류를 체크합니다. 아래와 같이 서로 유사한 구문을 실행하는 경우에도 파싱은 반복적으로 수행됩니다:
SELECT * FROM movies WHERE movie_id=1
SELECT * FROM movies WHERE movie_id=26
SELECT * FROM movies WHERE movie_id=5689
위 세 가지 쿼리는 조회하고자 하는 기록의 ID를 제외하고는 동일한 구조를 갖습니다. 하지만 오라클은 각각의 구문을 별개로 취급하고, 실행 이전에 파싱 작업을 수행합니다. 변수 바인딩을 이용하면 조회되는 값에 관계없이 쿼리에 대한 파싱이 단 한 차례만 수행된다는 이점이 있으며, 이를 이용하여 극적인 성능 개선 효과를 확인할 수도 있습니다.
두 번째로 사이트의 보안 문제를 고려하지 않을 수 없습니다. 보안은 매우 다양한 측면에서 고려가 필요하고 완벽한 해결도 불가능하지만, 그렇다고 결코 방관할 수는 없는 까다로운 문제입니다. 데이터베이스를 기반으로 하는 사이트에서 사용되는 쿼리의 상당 수는 사용자 입력 폼 등을 통해 생성되는 외부 데이터에 의존합니다. 이러한 쿼리는 SQL injection 공격에 특히 취약합니다. (SQL injection 공격이란 악의를 가진 사용자가 PHP 스크립트에 엉뚱한 데이터를 입력하는 방법으로 보안 취약점을 이용하는 공격 방법을 의미합니다.) 데이터베이스 쿼리가 올바른 방법으로 처리되지 않은 경우, 악의를 가진 사용자는 에러 메시지로부터 스크립트, 데이터베이스, 또는 서버에 대한 정보를 알아낼 수 있게 됩니다. 아래와 같은 쿼리가 그 예입니다:
SELECT * FROM movies WHERE movie_id=$_GET['id']
위의 쿼리가 초래할 수 있는 심각한 문제를 벌써 눈치챈 분들도 있을 것입니다. 사용자 입장에서는 URL을 “http://www.example.com/movie.php?id=23”에서 “http://www.example.com/movie.php?id=HaHa!”로 바꿔 치기 하는 방법만으로도 문제를 발생시킬 수 있습니다. 물론 쿼리에 사용되는 모든 데이터는 사전에 검증되어야 합니다. 하지만 쿼리에 변수가 사용되는 경우에는, 기대하지 않은 변수값으로 인해 에러가 발생할 가능성이 상존함을 인정하지 않을 수는 없습니다. 반면 바인딩된 변수는 실제 쿼리와 별도로 존재하므로, SQL injection 공격의 가능성을 극적으로 줄일 수 있습니다.
이번 "Oracle+PHP Cookbook" 시리즈 연재에서는, PHP 스크립트에서 오라클 쿼리를 실행하는 과정에서 변수를 바인딩하는 방법을 설명합니다. 아래에 설명되는 테크닉과 코드 예제를 이용하여 Oracle/PHP 환경의 성능과 보안 수준을 개선하실 수 있을 것입니다.
배경정보/개요
변수 바인딩을 이해하기 위해서는 실제 적용 사례를 살펴보는 것이 가장 도움이 될 것입니다. 필자는 몇 년 전, 전문 골퍼들이 티 타임(tee time) 별로 골프 코스를 도는 비용을 계산하기 위한 애플리케이션을 개발한 일이 있었습니다. 예를 들어, 어떤 토요일에는 오전 7시부터 오후 4시까지 10 분 간격으로 티 타임 예약이 가능하며, 오후 2시 이전에는 50 달러, 그 이후에는 40 달러의 사용료가 적용된다고 합시다. 이러한 값은 HTML 폼을 통해 얻어집니다. PHP 스크립트는 각각의 예약 시간 별로 데이터베이스에 레코드를 생성하고, 골퍼들이 레코드의 목록을 온라인으로 조회할 수 있게 합니다. 골퍼가 하루치의 데이터를 얻기 위해서는 50개 또는 그 이상의 매우 유사한 INSERT 쿼리를 사용해야 합니다. 이는 변수 바인딩을 사용하기에 매우 이상적인 상황으로 볼 수 있습니다.
아래 SQL 구문을 실행하면 예제를 위한 단순화된 형태의 테이블 구조를 생성할 수 있을 것입니다. (예제의 단순화를 위해, 프라이머리 키의 지정과 같은 절차는 생략하였습니다):
CREATE TABLE teetimes (
teetime DATE,
rate NUMBER(5,2)
)
물론 테이블을 여러 가지 형태로 확장할 수도 있을 것입니다. 하지만 여기에서는 테이블이 생성되었고, PHP 스크립트를 통해 테이블에 데이터를 입력해 넣을 수 있다는 사실이 중요합니다.
이제부터 변수 바인딩을 위해 필요한 단계별 절차를 설명하려 합니다. 최종적인 코드는 여러 단계를 거쳐 만들어지며, 각 단계별로 프로세스에 대한 분석을 통해 여러분의 이해를 돕게 될 것입니다. PHP 스크립트에서 변수를 바인딩하기 위한 기본적인 절차가 아래와 같습니다:
사용할 쿼리의 정의.
변수 바인딩을 위해 쿼리를 재작성.
오라클에서 기본 쿼리의 파싱을 수행.
PHP에서 변수 값을 할당.
쿼리를 실행.
1 단계: 쿼리의 정의
Listing 1은 teetimes 테이블에 다수의 레코드를 INSERT하기 위한 PHP 스크립트를 개략적으로 보여주고 있습니다. 오라클 관련 코드는 (OCI 함수에 관련하여, PHP4에 비해 일관적인 명명법 및 문법이 적용되는) PHP 5 환경을 가정하여 작성되었습니다. 이전 버전의 PHP를 사용하는 경우라면, PHP Manual에서 정확한 함수명 및 문법을 참고하시기 바랍니다. 또, PHP와 오라클 간의 커뮤니케이션 환경을 구현하는 것이 까다로운 경우 트러블슈팅 가이드에서 환경 변수 설정을 위한 또 다른 방법을 참고하실 수도 있습니다 .
Listing 1
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-15" />
<title>Binding Variables with PHP</title>
</head>
<body>
<h3>Entering Tee Times</h3>
<?php // bind1.php - listing 1
// Establish the environmental variables.
$sid = 'test10g';
$home = '/Users/oracle/10gEAR2/orahome';
putenv("ORACLE_HOME=$home");
putenv("ORACLE_SID=$sid");
putenv("TNS_ADMIN=$home/network/admin");
// Create the array of data to be inserted.
// This data represents what __should__ come from an HTML form.
$teetimes = array();
$date = '2005-08-20';
// Loop through each available hour in the day.
for ($hour = 7; $hour <! 16; $hour++) {
// Loop through each hour in 10 minute increments.
for ($minute = 0; $minute <! 60; $minute += 10) {
// Create the date and time value.
$this_time = "$date $hour:$minute";
// Add a 0 if necessary.
if ($minute <! 10) $this_time .= '0';
// Determine the rate to use.
$rate = ($hour <! 14) ? 50.00 : 40.00;
// Add this teetime and rate to the array.
$teetimes[$this_time] = $rate;
}
}
//echo '<!pre>' . print_r ($teetimes, 1) . '<!/pre>'; // For debugging
// Connect to Oracle.
$c = oci_pconnect ('scott', 'tiger', $sid) OR die
('Unable to connect to the database. Error: <!pre>' . print_r(oci_error(),1) . '<!/pre><!/body><!/html>');
// Insert each record into the table.
foreach ($teetimes as $time => $rate) {
// Make the query, for example:
/* INSERT INTO teetimes (teetime, rate) VALUES (TO_DATE('2005-08-21 15:00', 'yyyy-mm-dd hh24:mi'), 40.00); */
$q = "INSERT INTO teetimes (teetime, rate) VALUES (TO_DATE('$time', 'yyyy-mm-dd hh24:mi'), $rate)";
// Run the query.
$s = oci_parse($c, $q);
oci_execute ($s);
}
// Close the connection.
oci_close($c);
// Query to confirm the results:
/* SELECT TO_CHAR(teetime, 'MONTH DD, YYYY HH:MI AM') AS "Tee Time", rate FROM teetimes ORDER BY teetime ASC */
?>
</body>
</html>
위 스크립트의 목적은 단순합니다. 폼으로부터 날짜, 시간, 증분값, 요금을 가져오는 것입니다. 이렇게 가져온 데이터는 적절한 구조로 조합된 다음, 데이터베이스에 INSERT 됩니다. HTML 폼이 생성되지 않았기 때문에, 여기에서는 스크립트가 데이터의 어레이를 자동으로 생성하는 방식을 사용하기로 합니다. 각각의 티 타임(tee time)은 루프를 통해 오라클에 INSERT 됩니다. 이 스크립트는 이미 효율적인 것처럼 보일 수 있겠지만, 변수 바인딩을 이용함으로써 한층 더 개선할 수 있는 여지가 남아 있습니다.
2 단계: 마커(Marker)를 이용한 쿼리의 재정의
Listing 1에서 쿼리는 다음과 같이 정의되었습니다:
INSERT INTO teetimes (teetime, rate) VALUES (TO_DATE('$time', 'yyyy-mm-dd hh24:mi'), $rate)
$time과 $rate 는 미리 생성해 놓은 데이터 어레이로부터 가져옵니다. 이제 이 쿼리에서 사용된 변수를 대체할 위치지정자(placeholder)를 정의합니다. (여기서 ":marker"는 임의의 식별자를 의미합니다.) 예제의 경우 쿼리는 다음과 같이 변환이 가능합니다.
INSERT INTO teetimes (teetime, rate) VALUES (TO_DATE(:t, 'yyyy-mm-dd hh24:mi'), :r)
아래 Listing 2에서는 Listing 1의 쿼리 위치가 이동되었습니다. 이제 쿼리는 단 한 번만 실행되는 것으로 충분하므로, (루프 내부에서 각 레코드 별로 한 차례씩 실행되는 대신) 루프 외부에서 정의됩니다. 또 변수를 바인딩하면서 인용 부호를 표시하지 않고 있음을 주목하시기 바랍니다. (다시 말해 TO_DATE() 함수의 첫 번째 매개변수는 '$time'이 아닌 :t로 표시됩니다.) 인용 부호가 필요하지 않은 것은, 변수가 쿼리의 신택스로부터 분리되어 있기 때문입니다.
Listing 2
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-15" />
<title>Binding Variables with PHP</title>
</head>
<body>
<h3>Entering Tee Times</h3>
<?php
// Establish the environmental variables.
$sid = 'test10g';
$home = '/Users/oracle/10gEAR2/orahome';
putenv("ORACLE_HOME=$home");
putenv("ORACLE_SID=$sid");
putenv("TNS_ADMIN=$home/network/admin");
// Create the array of data to be inserted.
// This data represents what __should__ come from an HTML form.
$teetimes = array();
$date = '2005-08-21';
// Loop through each available hour in the day.
for ($hour = 7; $hour < 16; $hour++) {
// Loop through each hour in 10 minute increments.
for ($minute = 0; $minute < 60; $minute += 10) {
// Create the date and time value.
$this_time = "$date $hour:$minute";
// Add a 0 if necessary.
if ($minute < 10) $this_time .= '0';
// Determine the rate to use.
$rate = ($hour < 14) ? 50.00 : 40.00;
// Add this teetime and rate to the array.
$teetimes[$this_time] = $rate;
}
}
//echo '<pre>' . print_r ($teetimes, 1) . '</pre>'; // For debugging
// Connect to Oracle.
$c = oci_pconnect ('scott', 'tiger', $sid) OR die
('Unable to connect to the database. Error: <pre>' . print_r(oci_error(),1) . '</pre></body></html>');
// Define the query.
$q = "INSERT INTO teetimes (teetime, rate) VALUES (TO_DATE(:t, 'yyyy-mm-dd hh24:mi'), :r)";
// Parse the query.
$s = oci_parse($c, $q);
// Bind the values.
oci_bind_by_name($s, ':t', $time, 16);
oci_bind_by_name($s, ':r', $rate, 5);
// Insert each record into the table.
foreach ($teetimes as $time => $rate) {
// Execute the query.
oci_execute ($s);
}
// Close the connection.
oci_close($c);
// Query to confirm the results:
/* SELECT TO_CHAR(teetime, 'MONTH DD, YYYY HH:MI AM') AS "Tee Time", rate FROM teetimes ORDER BY teetime ASC */
?>
</body>
</html>
3 단계: 쿼리를 오라클에서 파싱
마커를 이용하여 쿼리를 정의했다면, 이제 오라클에서 파싱을 수행할 차례입니다. 오라클은 다른 쿼리와 마찬가지로, 정의된 쿼리의 신택스 상에 오류가 있는지를 점검합니다. PHP 스크립트에서는 데이터베이스 연결과 쿼리를 매개변수로 하는 oci_parse() 함수를 (또는 PHP 4의 경우 OCIParse()) 이용하여 파싱 작업을 수행합니다:
$s = oci_parse ($c, $q);
파싱 결과는 SQL 구문 저장을 위한 변수 ($s)에 할당되며, 이 변수는 변수 바인딩이 사용되지 않는 쿼리를 위해 활용됩니다.
Step 4: Associate PHP Variables with the Markers
호기심이 많은 개발자라면, PHP로부터 제공되는 값이 쿼리의 일부로서 실행되는 과정에 대해 궁금해 하실 수도 있을 것입니다. 이 과정에서 사용되는 함수가 oci_bind_by_name()()입니다. (PHP 4의 경우 OCIBindByName()). 이 함수는 SQL 구문을 첫 번째 매개변수로, 마커(marker)의 이름을 두 번째 매개변수로, PHP 변수 이름(또는 문자열 값)을 세 번째 매개변수로 사용합니다. 그 예가 아래와 같습니다:
oci_bind_by_name($s, ':t, $time);
oci_bind_by_name($s, ':r, $rate);
옵션으로 제공되는 4번째 매개변수는 INSERT 되는 데이터의 최대 길이를 정의하며, 보안을 보장하고 오라클 에러의 가능성을 최소화하기 위한 목적에서 활용됩니다:
oci_bind_by_name($s, ':t, $time, 16);
oci_bind_by_name($s, ':r, $rate, 5);
T위에서 사용된 16, 5의 숫자는 각각 $time 및 $rate의 최대 길이를 정의합니다 (4번째 매개변수를 -1로 지정하는 경우, PHP는 변수의 현재 길이를 최대 길이로 정의합니다.) Listing 2에서 위의 두 라인이 foreach 루프 이전에 정의되고 있음을 확인하실 수 있습니다. 이러한 사실이 의아하게 여겨질 수도 있습니다 $time과 $rate가 이 시점에 값을 할당 받지 못한 상황이기 때문입니다. 그 이유는 다음과 같습니다: 이 두 라인은 실제 쿼리가 실행될 때 $time에 저장된 값을 :t로, $rate 에 저장된 값을:r로 사용할 것을 오라클에게 통보하고 있습니다. 실제로 쿼리가 실행될 때에는 두 변수에 값이 할당된 상태이므로 아무런 문제가 없습니다.
5 단계 5: 바인딩된 쿼리의 실행
마지막으로 INSERT되는 각각의 값에 대하여 쿼리가 실행됩니다. 루프가 실행되면, 모든 어레이 값이 순서대로 $time과 $rate에 할당된 후 쿼리가 실행됩니다. 쿼리의 실행에는 oci_execute() (PHP 4의 경우 OCIExecute()) 함수가 사용되며, 사용되는 신택스는 변수 바인딩이 사용되지 않는 구문과 동일합니다:
oci_execute ($s);
위 작업 역시 foreach 루프 내부에서 실행됩니다. 자세한 코드는 Listing 2를 참고하시기 바랍니다.
이와 같은 방법이 제공하는 몇 가지 장점을 지적할 필요가 있어 보입니다. 먼저, INSERT 되는 값의 뒤에 붙은 스페이스는 자동으로 제거됩니다. 또 (변수 값이 쿼리와 별개로 처리되므로) 문제가 될 수 있는 문자의 에스케이프(escape) 처리를 위해 addslashes() 또는 Magic Quotes 등을 사용할 필요가 없습니다.
결론
본 문서에서는 변수 바인딩을 이용하여 데이터베이스 기반 웹 애플리케이션의 보안 및 성능 수준을 개선하는 방법을 알아 보았습니다. 이 테크닉을 적용하기 위해서는 단 몇 줄의 코드만을 추가하는 것으로 충분하며, 별도의 PHP 익스텐션이나 라이브러리를 사용할 필요도 없습니다.
하지만 변수 바인딩에 관련해서 두 가지 사항을 명심할 필요가 있습니다:
변수 바인딩은, 반복적으로 실행되는 쿼리, 그리고 계속적으로 다른 값이 적용되는 동일한 구조의 쿼리에 대해서만 성능 효과를 기대할 수 있습니다. 단 한 차례만 실행되는 쿼리라면 아무런 성능 효과를 얻을 수 없을 것입니다. 웹 애플리케이션 환경에 변수 바인딩을 적용할 것인지 결정하기 전에, 벤치마크 테스트를 통해 효과를 확인할 필요가 있습니다.
2. 변수 바인딩이 제공하는 보안 상의 효과는, 기존의 표준 보안 정책을 대체하는 용도로 활용될 수는 없습니다. 쿼리에 사용되는 데이터에 대한 검증 작업은 반드시 수행되어야 합니다. 특히 $_POST, $_GET, $_COOKIE 등을 통해 값이 전달되는 경우에는 절대적으로 검증 작업이 필요합.
PHP와 오라클 간에 변수 바인딩을 수행하는 방법은 두 가지가 존재합니다. 본 문서에서 설명된 첫 번째 방법은 매개변수 바인딩(“bound parameter”)이라 불리며, 쿼리의 매개변수가 변수와 바인딩됩니다 (이를 “prepared statement”라 부르기도 합니다). 또 결과 바인딩(“bound result")은 (SELECT 쿼리를 수행한 이후) 데이터베이스로부터 값을 가져오기 위한 또 다른 방법으로 활용됩니다.
Oracle 기반 PHP 웹 애플리케이션의 성능 개선에 관련한 정보가 필요한 경우, 스토어드 프로시저에 관련된 문서들을 함께 참조하시기 바랍니다. 또 성능 개선을 위해서는 테이블의 인덱스를 이해하고 활용할 수 있어야 합니다. 마지막으로 PHP 스크립트에서 할당된 리소스를 해제하기 위해 oci_free_statement() (PHP4의 경우 OCIFreeStatement()) 함수를 호출하는 방법을 숙지할 필요가 있습니다.
