How can PHP variables be properly passed in SQL statements to avoid issues with pagination?

When passing PHP variables in SQL statements for pagination, it's important to properly sanitize and escape the variables to avoid SQL injection vulnerabilities. One way to do this is by using prepared statements with placeholders for the variables, which allows the database to handle the values safely. This approach helps prevent any malicious SQL code from being executed. 

// Assuming $page and $itemsPerPage are the variables used for pagination
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$itemsPerPage = 10;

// Connect to the database
$pdo = new PDO('mysql:host=localhost;dbname=database', 'username', 'password');

// Prepare the SQL statement with placeholders
$stmt = $pdo->prepare("SELECT * FROM table LIMIT :offset, :itemsPerPage");
$offset = ($page - 1) * $itemsPerPage;
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->bindParam(':itemsPerPage', $itemsPerPage, PDO::PARAM_INT);

// Execute the statement
$stmt->execute();

// Fetch the results
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Display the results
foreach ($results as $row) {
    echo $row['column_name'] . "<br>";
}

Keywords

PHP variables SQL statements pagination security

Related Questions