Prepared Statement หรือ Parameterized Statement เป็นรูปแบบที่ใช้สำหรับทำงานข้อความคำสั่งที่มีลักษณะ
คล้ายกันซ้ำๆ อย่างมีประสิทธิภาพ
ผังการทำงานเบื้องต้น
Prepared Statement มีการทำงานที่ประกอบไปด้วย 2 ขั้นตอน(Stages) คือในขั้นตอนการเตรียมการ(Prepare) และขั้นตอน
การทำงาน(Execute) ในขั้นตอนการเตรียมการ รูปแบบข้อความคำสั่งจะถูกส่งไปยัง server ของฐานข้อมูล โดย server จะทำ
การตรวจสอบรูปแบบคำสั่งและกำหนดค่าเริ่มต้นการใช้งานทรัพยากรต่างๆ ภายใน
สำหรับ MySQL server นั่นจะใช้เครื่อง ? สำหรับแทนตำแหน่งข้อมูลที่ไม่ต้องการระบุชื่อเฉพาะ
เพื่อประกอบเนื้อหา เราจะใช้ช้อมูลตารางจังหวัดในปะรเทศไทยประกอบ http://niik.in/que_2398_6277
สามารถดูแนวทางการใช้งาน MySQL กรณีที่ไม่ใช้งาน Prepared Statement ได้ที่ http://niik.in/que_2398_5642
ตัวอย่าง Non-prepared statement
<?php
// โค้ดไฟล์ dbconnect.php ดูได้ที่ http://niik.in/que_2398_5642
require_once("dbconnect.php");
/* Non-prepared statement */
$sql = "
SELECT province_name FROM tbl_provinces WHERE province_id=2
";
$result = $mysqli->query($sql);
if($result && $result->num_rows>0){ // คิวรี่ข้อมูลสำเร็จหรือไม่ และมีรายการข้อมูลหรือไม่
}
?>
ตัวอย่าง Prepared statement, stage 1: prepare
<?php
// โค้ดไฟล์ dbconnect.php ดูได้ที่ http://niik.in/que_2398_5642
require_once("dbconnect.php");
/* Prepared statement, stage 1: prepare */
$sql = "
SELECT province_name FROM tbl_provinces WHERE province_id=?
";
/* create a prepared statement */
$stmt = $mysqli->stmt_init();
if($stmt->prepare($sql)){
}
?>
จากตัวอย่างทั้ง 2 ข้างต้น ปกติเราจะคุ้นเคยอยู่แล้วกับรูปแบบในตัวอย่างแรก ส่วนในตัวอย่างที่ 2 เป็นการใช้งงาน Prepared Statement
ข้อความคำสั่ง SQL ที่กำหนดในตัวแปร $sql จะถูกส่งไปตรวจสอบและเตรียมการ ซึ่งอยู่ใน stage แรกขั้นเตรียมการ
จะเห็นว่ามีการใช้ ? แทนตำแหน่งข้อมูลที่จะมีการส่งค่าไปภายหลัง ใน stage ที่สอง
ตัวอย่าง Prepared statement, stage 2: bind and execute
<?php
// โค้ดไฟล์ dbconnect.php ดูได้ที่ http://niik.in/que_2398_5642
require_once("dbconnect.php");
/* Prepared statement, stage 1: prepare */
$sql = "
SELECT province_name FROM tbl_provinces WHERE province_id=?
";
/* create a prepared statement */
$stmt = $mysqli->stmt_init();
if($stmt->prepare($sql)){
/* Prepared statement, stage 2: bind and execute */
/* bind parameters */
$provinceID = 2;
$stmt->bind_param('i', $provinceID);
/* execute query */
$stmt->execute();
$stmt->close();
}
?>
ใน stage ที่ 2 ขั้นตอนการ bind parameter ตัวแปรที่ต้องการส่งค่าไปใช้ในคำสั่ง SQL ที่ได้เตรียมไว้แล้ว
อย่างข้างต้น จะเห็นว่า คำสั่ง SQL ใช้เครื่องหมาย ? แทนตำแหน่งค่าของตัวแปรข้อมูล $provinceID
SELECT province_name FROM tbl_provinces WHERE province_id=?
ในการ bind parameter จะใช้คำสั่ง
$stmt->bind_param('i', $provinceID);
ในค่าแรกที่กำหนดจะเป็น string type ประกอบด้วย
- i (Interger) จำนวนเต็ม
- d (double) เลขทศนิยม
- s (string) ข้อความ
- b (Blob / Binary Large OBject ) ข้อมูล Binary
ในค่าที่สองจะกำหนดเป็นตัวแปร ที่สัมพันธ์กับค่าแรกตามลำดับ อย่างข้างต้น ตัวแปร $provinceID จะต้องเป็น
เลขจำนวนเต็ม ( i ) เป็นต้น
เราสามารถทำการ bind parameter ได้หลายค่าตามต้องการ เช่น สมมติคำสั่ง SQL ที่เรากำหนดใน Prepared เป็น
SELECT province_name FROM tbl_provinces WHERE province_id=? AND province_name LIKE ?
เราก็สามารถกำหนด bind parameter เป็นดังนี้ (สมมติตัวแปรสำหรับค่าที่สองเป็น $provinceName)
$provinceID = 2;
$provinceName = "%สมุทร%";
$stmt->bind_param('is', $provinceID,$provinceName);
นั่นคือ i ตัวแรกเป็น type ของ $provinceID
และ s เป็น type ของ $provinceName
จำนวนของ type จะต้องเท่ากับจำนวนของตัวแปร เช่น type i กับ s สำหรับตัวแปร $provinceID กับ $provinceName
ลำดับชองตัวแปร ต้องสัมพันธ์กับตำแหน่ง ? ในคำสั่ง SQL เช่น $provinceID สำหรับ ? แรก และ $provinceName สำหรับ ? ตัวที่สอง
คำสั่ง SQL ที่กำหนดใน Prepared Statement ไม่ต้องมี ( ; ) semicolon
หลังจาก bind parameter แล้วใน stage ที่ 2 จะต่อด้วยการให้ทำงาน หรือ execute ด้วยคำสั่ง
$stmt->execute()
การทำงานซ้ำ (Repeated execution)
ใน Prepared Statement เราสามารถทำงานซ้ำโด้อย่างมีประสิทธิภาพ โดยเฉพาะในกรณีการเพิ่มข้อมูล เช่น เริ่มต้นเราเตรียมรูปแบบคำสั่ง
SQL สำหรับเพิ่มข้อมูล ที่จะกำหนดใน prepare stage จากนั้น เราก็สามารถส่งแค่ข้อมูลเข้าไปโดยใช้รูปแบบคำสั่ง SQL เดิมเปลี่ยนแค่ค่าตัวแปร
ของข้อมูล ตัวอย่างเช่น
<?php
/* Prepared statement, stage 1: prepare */
$sql = "
INSERT INTO test(id) VALUES (?)
";
$stmt = $mysqli->stmt_init();
if($stmt->prepare($sql)){
/* Prepared statement, stage 2: bind and execute */
$id = 1;
$stmt->bind_param("i", $id);
/* execute query */
$stmt->execute();
/* Prepared statement: repeated execution, only data transferred from client to server */
for ($id = 2; $id < 5; $id++) {
/* repeated execute query */
$stmt->execute();
}
/* close statement */
$stmt->close();
}
?>
คำสั่ง execute ในลูป for จะเป็นการส่งค่าตัวแปร $id ไปยัง server โดยใช้งาน คำสั่ง SQL ที่กำหนดครั้งเดียวใน prepared statement
ซึ่งก็คือคำสั่ง
INSERT INTO test(id) VALUES (?)
จะเห็นว่าเราทำการกำหนด prepared แค่ครั้งเดียว แต่สามารถ execute ได้หลายครั้ง ซึ่งจะมีประโยชน์มากในกรณีทำการวนลูปเพิ่มข้อมูล
ทุกๆ prepared statement ที่เกิดขึ้นจะมีการใช้งานทรัพยากรในฝั่ง server ดังนั้นเราจึงควรทำการปิด statement หลังจากใช้งานแล้ว
อย่างไรก็ตาม หากไม่ได้กำหนดอย่างชัดเจนโดยคำสั่ง $stmt->close() ดังตัวอย่างข้างต้น statement จะปิดอัตโนมัติเมื่อสิ้นสุดการทำงาน
ของคำสั่ง PHP
การใช้งาน prepared statement ไม่ได้เป็นวิธีการที่มีประสิทธิภาพทุกครั้งไปในการใช้งานคำสั่ง SQL เนื่องจากในการทำงานของ prepared
statement หนึ่งครั้ง จะเกิดการทำส่งข้อมูลระหว่าง client-server มากกว่ากรณีที่เป็น non-prepared statement อย่างเช่น ในกรณีการแสดงข้อ
มูลทั้งหมดด้วยคำสั่ง SQL
/* Non-prepared statement */
$sql = "
SELECT * FROM tbl_provinces
";
$result = $mysqli->query($sql);
if($result && $result->num_rows>0){ // คิวรี่ข้อมูลสำเร็จหรือไม่ และมีรายการข้อมูลหรือไม่
}
การใช้งานคำสั่ง SQL ในรูปแบบข้างต้น เราควรใช้งานในรูปแบบ non-prepared statement จะได้ประสิทธิภาพมากกว่า
หรือแม้แต่กรณีการใช้งาน multi-INSERT SQL ในลักษณะที่แทบจะไม่มีการส่งข้อมูลไปกลับระหว่าง server ตามตัวอย่างด้านล่าง
$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3), (4)");
การเพิ่มข้อมูลในลักษณะไม่ซับซ้อนข้างต้น ก็ไม่จำเป็นต้องใช้งาน prepared statement
ผลลัพธ์และประเภทของข้อมูล
หลังจากที่เราทำคำสั่ง SQL ใน prepared statement ด้วยคำสั่ง $stmt->execute() แล้ว เราสามารถแสดงข้อมูลในกรณีเป็นการ
ใช้คำสั่ง SELECT เพื่อแสดงข้อมูลหลังจากทำงานคำสั่ง SQL ได้ดังนี้
<?php
/* Prepared statement, stage 1: prepare */
$sql = "
SELECT province_id,province_name FROM tbl_provinces WHERE province_id=?
";
/* create a prepared statement */
$stmt = $mysqli->stmt_init();
if($stmt->prepare($sql)){
/* Prepared statement, stage 2: bind and execute */
/* bind parameters */
$provinceID = 2;
$stmt->bind_param('i', $provinceID);
/* execute query */
$stmt->execute();
/* get result */
$result = $stmt->get_result();
if($result && $result->num_rows>0){ // คิวรี่ข้อมูลสำเร็จหรือไม่ และมีรายการข้อมูลหรือไม่
$row = $result->fetch_assoc(); // กรณีรายการเดียว
// หรือ กรณีวนลูปแสดงหลายรายการ
/* while($row = $result->fetch_assoc()){
echo $row['province_name']."<br>";
} */
}
/* close statement */
$stmt->close();
}
?>
การใช้งาน MySQL ระหว่าง client กับ server จะมีการรับส่งข้อมูลในรูปแบบที่แตกต่างกัน ระหว่างการใช้งาน prepared statement
และ non-prepared statement โดยการใช้งาน prepared statement นั้น MySQL server จะทำการส่งค่าผลลัพธ์ของข้อมูลมาในรูปแบบ
binary โดยไม่มีการแปลงเป็นชุดข้อมูลที่เป็น string ก่อนส่ง ดังนั้นฝั่ง client จะได้รับข้อมูลมาในรูปแบบ binary แล้วทำการแปลงค่า
ข้อมูลเป็นประเภทที่สอดคล้องสัมพันธ์กับชนิดตัวแปรใน PHP ยกตัวอย่างเช่น ข้อมูลในคอล้มน์ที่เป็น INT จะถูกแปลงเป็นชนิดข้อมูล Integer
หรือจำนวนเต็มในตัวแปร PHP เป็นต้น
ดังนั้นในกรณี prepared statement
echo gettype($row['province_name']); // จะได้ค่าเป็น string echo gettype($row['province_id']); // จะได้ค่าเป็น integer
ซึ่งแตกต่างจากกรณีที่เป็น non-prepared statement ชนิดของข้อมูลค่าเริ่มต้นทั้งหมดจะเป็น string
นำผลลัพธ์มากำหนดไว้ในตัวแปร
ใน preapared statement นอกจากที่เราจะแสดงผลลัพธ์ผ่าน mysqli_result object ตามตัวอย่างด้านบนแล้ว เรายังสามารถรับค่าผลลัพธ์ข้อมูล
มาไว้ในตัวแปรสำหรับนำไปแสดงผลได้
ตัวแปรสำหรับแสดงผล (output) ต้องกำหนดไว้หลังจากทำคำสั่ง execute และต้องกำหนดจำนวนตัวแปรให้ครบตามจำนวนคอลัมน์
ฟิลด์รายการที่ต้องการแสดง
มาดูตัวอย่างการใช้งาน
<?php
/* Prepared statement, stage 1: prepare */
$sql = "
SELECT province_id,province_name FROM tbl_provinces WHERE province_id>? LIMIT 5
";
/* create a prepared statement */
$stmt = $mysqli->stmt_init();
if($stmt->prepare($sql)){
/* Prepared statement, stage 2: bind and execute */
/* bind parameters */
$provinceID = 2;
$stmt->bind_param('i', $provinceID);
/* execute query */
$stmt->execute();
/* Fetching results using bound variables */
$province_id = NULL;
$province_name = NULL;
$stmt->bind_result($province_id, $province_name);
/* fetch results from a prepared statement into the bound variables */
while($stmt->fetch()){
echo $province_id." - ".$province_name."<br>";
}
/* close statement */
$stmt->close();
}
?>
ในการใช้งาน prepared statement โดยค่าเริ่มต้นแล้ว ข้อมูลผลลัพธ์จะไม่ถูกจัดเก็บเป็น buffer ผลลัพธ์ที่เกิดขึ้นจะไม่มีการดึงออกมาแล้ว
ส่งไปยัง client ในรูปแบบของข้อมูล buffer ทำให้ชุดข้อมูลผลลัพธ์กินทรัพยากรในฝั่ง server ไปจนกว่า client จะดึงข้อมูลมาใช้งาน
ดังนั้น จึงเป็นสิ่งจำเป็นที่เมื่อเรามีการใช้งาน prepared statement แล้วต้องนำข้อมูลที่ได้มาใช้งานให้ถูกจังหวะหรือในทันที อย่างไรก็ตาม
หาก client เกิดข้อผิดพลาดในการนำข้อมูลมาใช้งานหรือ มีการปิดการใช้งาน statement ไปก่อนที่จะมีการดึงข้อมูล ตัว mysqli จะทำการ
เรียกข้อมูลมาใช้งานโดยปริยาย
การไม่ใช้ buffer อาจจะทำให้ความเร็วในการแสดงลดลงไปบ้าง แต่ก็ช่วยให้การใช้ memory ลดลงไปด้วย
เราสามารถใช้งาน prepared statement เพื่อส่งข้อมูลผลลัพธ์มาเก็บใน buffer โดยใช้คำสั่ง
/* store result to buffer */ $stmt->store_result();
โดยหากมีการใช้งาน ต้องกำหนดทุกครั้งหลังทำคำสั่ง execute ที่มีผลลัพธ์เกิดขึ้น เช่นคำสั่ง
(SELECT, SHOW, DESCRIBE, EXPLAIN) คำสั่งเหล่านี้ จะได้ผลลัพธ์ข้อมูลเกิดขึ้น
ข้อมูล buffer คือ ผลลัพธ์ที่ได้จาก prepared statement จะถูกส่งมาเก็บไว้ใน memory ผ่าน mysqli client
ซึ่งในบางกรณี ถ้ามีการเรียกใช้งาน statement ซ้ำ ก็จะทำให้มีการทำงานเร็วขึ้น แต่ก็อาจจะทำให้ memory ทำงานหนัก
หรือที่เรียกกันว่ากินแรม ดังนั้นจึงจำเป็นต้องมีการจัดการให้เหมาะสม เมื่อมีการใช้งาน โดยหลังจากที่เรานำข้อมูลมาใช้งานแล้ว
หรือ fetch ข้อมูลมาแล้ว ก็ให้ทำการ คืนพื้นที่หน่วยความจำ ด้วยการเรียกใช้คำสั่ง
/* free result */ $stmt->free_result();
ตัวอย่างการใช้งาน buffer
ข้อมูลผลัพธ์ โดยใช้ $stmt->store_result()
<?php
/* Prepared statement, stage 1: prepare */
$sql = "
SELECT province_id,province_name FROM tbl_provinces WHERE province_id>? LIMIT 5
";
/* create a prepared statement */
$stmt = $mysqli->stmt_init();
if($stmt->prepare($sql)){
/* Prepared statement, stage 2: bind and execute */
/* bind parameters */
$provinceID = 2;
$stmt->bind_param('i', $provinceID);
/* execute query */
$stmt->execute();
/* store result */
$stmt->store_result();
/* Fetching results using bound variables */
$province_id = NULL;
$province_name = NULL;
$stmt->bind_result($province_id, $province_name);
/* fetch results from a prepared statement into the bound variables */
while($stmt->fetch()){
echo $province_id." - ".$province_name."<br>";
}
/* free result */
$stmt->free_result();
/* close statement */
$stmt->close();
}
?>
หรือแบบใช้ $stmt->get_result()
/* execute query */
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_assoc()) {
echo $row['province_id']." - ".$row['province_name']."<br>";
}
/* free result */
$stmt->free_result();
เลี่ยงการใช้ตัวอักชระพิเศษ และการป้องกัน SQL Injection
เหตุผลหลักและสำคัญของการใช้งาน prepared statement ก็คือการเลี่ยงการใช้งานตัวอักขระพิเศษ ที่อาจจะถูกส่งมาปะปนกับคำสั่ง SQL
ในกรณีที่เป็น non-prepared statement ซึ่งช่วยในการป้องกันการเกิด SQL Injection
ทั้งนี้ก็เพราะว่า ในการใช้งาน prepared statement นั้น ตัวแปรข้อมูล และชุดคำสั่ง SQL แยกส่งไปไม่พร้อมกัน ดังที่ได้อธิบายไปแล้วในตอนต้น
นอกจากนั้น ข้อมูลที่ส่งก็ยังเป็นข้อมูลในรุปแบบ binary ไม่ได้เป็นรูปแบบ string จึงยากที่จะแทรกหรือปรับเปลี่ยนค่าได้ และเนื่องจากข้อมูลถูกส่ง
ไปในรูปแบบ binary จึงไม่จำเป็นที่จะต้องกังวลในเรื่องของตัวอักขระพิเศษที่จะไปมีผลกับคำสั่ง SQL อีก
การเปรียบเทียบ prepared และ non-prepared statement
รอบการส่งข้อมูลระหว่าง client - server กรณีทำคำสั่งครั้งเดียว
Prepared 2
Non-prepared 1
รูปแบบข้อความคำสั่งที่ส่งจาก client ไป server กรณีทำคำสั่งครั้งเดียว
Prepared 1
Non-prepared 1
รอบการส่งข้อมูลระหว่าง client - server กรณีทำคำสั่งซ้ำ (n) ครั้ง
Prepared 1 + n
Non-prepared n
รูปแบบข้อความคำสั่งที่ส่งจาก client ไป server กรณีทำคำสั่งซ้ำ (n) ครั้ง
Prepared 1 template กับ n ครั้งที่ใช้งาน paramenter ถ้ามี
Non-prepared n ครั้งที่ใช้งาน paramenter ถ้ามี
การจัดการตัวแปรข้อมูล นำเข้า
Prepared ป้องกันตัวอักขระพิเศษอัตโนมัติ
Non-prepared ต้องจัดการตัวอักขระพิเศษด้วยตัวเอง เช่นใช้ mysqli_real_escape_string
การจัดการตัวแปรข้อมูล ผลลัพธ์/ส่งออก
Prepared รองรับ * เช่น $stmt->bind_result($province_id, $province_name)
Non-prepared ไม่รองรับ
รองรับ mysqli_result API
Prepared รองรับ โดยใช้ผ่าน $stmt->get_result()
Non-prepared รองรับ
การใช้งาน buffer ผลลัพธ์ข้อมูล
Prepared มีการใช้งาน ผ่าน $stmt->get_result() หรือ $stmt->store_result()
Non-prepared มีการใช้งาน ผ่าน $mysqli->query()
การใช้งานแบบไม่ buffer ผลลัพธ์ข้อมูล
Prepared มีการใช้งาน ผ่าน output binding API
Non-prepared มีการใช้งาน ผ่าน $mysqli->real_query() กับ $mysqli->use_result()
ช่องทางการส่งข้อมูลของ MySQL Client Server
Prepared ส่งข้อมูลแบบ binary
Non-prepared ส่งข้อมูลแบบ sring
ประเภทข้อมูลของผลลัพธ์
Prepared ประเภทข้อมูลคงรูปแบบเดิม ตามที่กำหนดรูปแบบในฟิลด์ตาราง เช่น ฟิลด์ INT เป็น integer
Non-prepared ค่าเริ่มต้น ประเภทของข้อมูลจะถูก แปลงเป็น string ทั้งหมด แต่สามารถคงรูปแบบเดิมได้
การรองรับคำสั่ง SQL
Prepared รองรับเกือบหมด แต่ไม่ทั้งหมด
Non-prepared รองรับทั้งหมด
หวังว่าแนวทางการใช้งาน prepared statement ข้างต้น จะเป็นแนวทางในการทำความเข้าใจ และนำไปประยุกต์ใช้งานได้
เนื้อหาที่เกี่ยวข้อง