PHP و پایگاه داده: Prepared statements و دستور SELECT

Prepared Statement چیست؟

Prepared Statement ها قابلیت بسیار موثری برای مقابله با حملات SQL Injection هستند و نحوه ی کار آن ها به زبان ساده بدین شکل است:

1. Prepare (آماده سازی): یک قالب برای دستور SQL خاصی ساخته شده و به پایگاه داده ارسال می شود. برخی از مقادیر این دستور SQL به صورت خالی و مشخص نشده ارسال می شوند که به آن ها پارامتر می گوییم. مثال: (? ,? ,?) INSERT INTO MyGuests VALUES
2. پایگاه داده قالب ارسال شده را تجزیه و کامپایل می کند و انواع بهینه سازی را روی آن انجام می دهد و بدون آنکه آن را اجرا کند نتایج را ذخیره می کند.
3. Execute (اجرای دستور): بعدها و در زمان نیاز، مقادیر اصلی به جای پارامترها قرار می گیرند و پایگاه داده دستور را اجرا می کند. برنامه می تواند از این دستور با مقادیر مختلف استفاده کرده و هر تعداد باری که لازم داشت آن را اجرا کند.

اگر Prepared Statement ها را با اجرای عادی دستورات SQL مقایسه کنیم سه مزیت اصلی وجود خواهد داشت:

• Prepared Statement ها زمان لازم برای تجزیه (parsing) را کاهش می دهند چرا که کوئری تنها یک بار آماده سازی می شود (حتی اگر دستور چندین بار اجرا شود).
• bind کردن پارامترها مصرف/اشغال پهنای باند را به حداقل می رسانند چرا که هر بار فقط و فقط باید خود پارامتر ها را ارسال کنید نه تمام کوئری ها را.
• Prepared Statement ها در مقابل حملات SQL Injection به ما کمک می کنند. مقادیر پارامترها (که بعدا و از طریق پروتکلی جداگانه ارسال می شوند) دیگر نیازی به escape شدن ندارند، اگر قالب اصلی از منبع خارجی وارد وب سایت ما نشده باشد دیگر امکان SQL Injection وجود ندارد.

Prepared Statement در MySQLi به شکل شیء گرا:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// prepare and bind
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);

// set parameters and execute
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
$stmt->execute();

$firstname = "Mary";
$lastname = "Moe";
$email = "mary@example.com";
$stmt->execute();

$firstname = "Julie";
$lastname = "Dooley";
$email = "julie@example.com";
$stmt->execute();

echo "New records created successfully";

$stmt->close();
$conn->close();
?>

همانطور که در کد بالا می بینید به جای وارد کردن مستقیم مقادیر درون دستور، به جایشان از علامت سوال استفاده کرده ایم:

"INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)"

سپس از تابع ()bind_param برای قرار دادن مقادیر اصلی استفاده کرده ایم:

$stmt->bind_param("sss", $firstname, $lastname, $email);

رشته ی "sss" که در اول آمده است نوع مقادیر را برای MySQL مشخص می کند و می گوید که هر سه مقدار String (رشته) هستند. شما می توانید چهار نوع داده را به این شکل مشخص کنید:

• i - یعنی عدد صحیح (integer)
• d - یعنی عدد اعشاری (double)
• s - یعنی رشته (string)
• b - یعنی BLOB (اشیاء باینری بزرگ)

هشدار: استفاده ی خالی از prepared statement ها به صورت خالی از شما محافظت نخواهد کرد! شاید با این کار جلوی هکرهای تازه کار را بگیرید اما کسانی که حرفه ای باشند می توانند به شما آسیب برسانند. برای اطلاعات بیشتر به مقاله ی «خطر تزریق SQL با وجود Placeholder ها در PDO» سر بزنید.

Prepared Statement در PDO:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // prepare sql and bind parameters
    $stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) 
    VALUES (:firstname, :lastname, :email)");
    $stmt->bindParam(':firstname', $firstname);
    $stmt->bindParam(':lastname', $lastname);
    $stmt->bindParam(':email', $email);

    // insert a row
    $firstname = "John";
    $lastname = "Doe";
    $email = "john@example.com";
    $stmt->execute();

    // insert another row
    $firstname = "Mary";
    $lastname = "Moe";
    $email = "mary@example.com";
    $stmt->execute();

    // insert another row
    $firstname = "Julie";
    $lastname = "Dooley";
    $email = "julie@example.com";
    $stmt->execute();

    echo "New records created successfully";
    }
catch(PDOException $e)
    {
    echo "Error: " . $e->getMessage();
    }
$conn = null;
?>

در مورد prepared statement ها در دوره ی آموزشی PDO به طور کامل توضیح داده شده است.

دستور SELECT در PHP

دستور SELECT در PHP برای انتخاب کردن داده ها و برگرداندنشان استفاده می شود:

SELECT column_name(s) FROM table_name

در ساختار بالا اگر از علامت * استفاده کنیم می توانیم تمام ستون ها را برگردانیم:

SELECT * FROM table_name

در مثال زیر که با MySQLi و به حالت شیء گرایانه نوشته شده است می خواهیم ستون های id و firstname  و lastname را از جدول MyGuests انتخاب کنیم:

<!DOCTYPE html>
<html>
<body>

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "<br> id: ". $row["id"]. " - Name: ". $row["firstname"]. " " . $row["lastname"] . "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?> 

</body>
</html>

مشاهده ی خروجی

در مثال بالا ابتدا یک کوئری query نوشته ایم که ستون های مورد نظر ما را از جدول MyGuests انتخاب می کند. خط بعدی کد نیز کوئری ما را اجرا کرده و نتیجه را در متغیری به نام result$ قرار می دهد. تابع ()function num_rows بررسی می کند که آیا تعداد ردیف های برگشتی بیشتر از صفر است یا خیر (یعنی آیا نتیجه ای داشته ایم یا نه). اگر نتیجه ای داشتیم تابع ()fetch_assoc نتایج را در یک آرایه ی متناظر میریزد که بعدا می توانیم در آن گردش کنیم. در آخر حلقه ی while در آرایه گردش کرده و نتایج را نمایش می دهد.

در مثال زیر دقیقا همین عملیات بالا را به صورت رویه ای (نه شیء گرا) انجام داده ایم:

<!DOCTYPE html>
<html>
<body>

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
    }
} else {
    echo "0 results";
}

mysqli_close($conn);
?> 

</body>
</html>

مشاهده ی خروجی

البته می توانیم به روش زیر نتایج را در یک جدول HTML قرار دهیم:

<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
    border: 1px solid black;
}
</style>
</head>
<body>

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<table><tr><th>ID</th><th>Name</th></tr>";
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "<tr><td>" . $row["id"]. "</td><td>" . $row["firstname"]. " " . $row["lastname"]. "</td></tr>";
    }
    echo "</table>";
} else {
    echo "0 results";
}

$conn->close();
?> 

</body>
</html>

مشاهده ی خروجی

حالا همین کار را با استفاده از رابط PDO انجام می دهیم:

<!DOCTYPE html>
<html>
<body>

<?php
echo "<table style='border: solid 1px black;'>";
 echo "<tr><th>Id</th><th>Firstname</th><th>Lastname</th></tr>";

class TableRows extends RecursiveIteratorIterator { 
    function __construct($it) { 
        parent::__construct($it, self::LEAVES_ONLY); 
    }

    function current() {
        return "<td style='width: 150px; border: 1px solid black;'>" . parent::current(). "</td>";
    }

    function beginChildren() { 
        echo "<tr>"; 
    } 

    function endChildren() { 
        echo "</tr>" . "\n";
    } 
} 

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests"); 
    $stmt->execute();

    // set the resulting array to associative
    $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); 

    foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) { 
        echo $v;
    }
}
catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$conn = null;
echo "</table>";
?> 

</body>
</html>

مشاهده ی خروجی