关于INT的php分页安全性

Question

I'm looking to update pagination on a page to PDO. However, I want to make sure it is 100% free from any SQL injection etc.

Below is the content of the pagination script I have found which I think will work without any issues. However as it will be pulling data from the URL I'm a bit concerned regarding the line:

if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; };

I can see isset is there to check if the variable is NULL (I think) but I can't see any checks for if it is not a number.

I was thinking of changing to:

if (isset($_GET["page"])) { $page  = (int)$_GET["page"]; } else { $page=1; };

As I think this will check if the page variable is a number. or should it be:

if (isset((int)$_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; };

Or do I use INT on both? I think in old mysql you would have used striptags etc but not sure with PDO (still learning).

Here is the full code before the change mentioned above.

<?php
        include('connect.php');
        if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; };
        $start_from = ($page-1) * 3;        
        $result = $db->prepare("SELECT * FROM members ORDER BY id ASC LIMIT $start_from, 3");
        $result->execute();
        for($i=0; $row = $result->fetch(); $i++){
    ?>
    <tr class="record">
        <td><?php echo $row['a']; ?></td>
        <td><?php echo $row['b']; ?></td>
        <td><?php echo $row['c']; ?></td>
    </tr>
    <?php
        }
    ?>
</tbody>
</table>
<div id="pagination">
    <?php 

    $result = $db->prepare("SELECT COUNT(id) FROM members");
    $result->execute(); 
    $row = $result->fetch(); 
    $total_records = $row[0]; 
    $total_pages = ceil($total_records / 3); 

    for ($i=1; $i<=$total_pages; $i++) { 
                echo "<a href='index.php?page=".$i."'";
                if($page==$i)
                {
                echo "id=active";
                }
                echo ">";
                echo "".$i."</a> "; 
    }; 
    ?>

Connect.php contains

$db = new PDO('mysql:host='.$db_host.';dbname='.$db_database, $db_user, $db_pass);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Any help or guidance would be greatly appreciated. Also if you can spot anything else security wise, please let me know.

EDITS:

Added line:

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

to connect.php

Any other security tips?

Answer 1

Use parameterized queries this will avoid the possibility of injections. To do that using PDO you can

1. Pass the user provided values into the execute in an array
2. Place a question mark as a placeholder in place of the user data

$result = $db->prepare("SELECT * FROM members ORDER BY id ASC LIMIT ?, 3");
$result->execute(array((int)$start_from));

You also can bind it, http://php.net/manual/en/pdostatement.bindparam.php (based on doc and other threads, I don't usually bind).

$result = $db->prepare("SELECT * FROM members ORDER BY id ASC LIMIT :start, 3");
$result->bindParam(':start', (int)$start_from, PDO::PARAM_INT);
$result->execute();

Here's a longer thread on it, How can I prevent SQL injection in PHP?

Also PHP notes on it, http://php.net/manual/en/pdo.prepared-statements.php

For a longer sample, if you had multiple values coming in:

$result = $db->prepare("SELECT * FROM members where username = ? and email = ? ORDER BY id ASC LIMIT ?, 3");
$result->execute(array($_GET['name'], $_GET['email'], $start_from));