What are the best practices for creating a more robust interface in PHP when querying a database via URL?

When querying a database via URL in PHP, it is important to create a more robust interface to handle the input parameters securely and efficiently. One way to achieve this is by using prepared statements to prevent SQL injection attacks and validating the input data to ensure it meets the expected format. 

<?php
// Establish a database connection
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

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

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

// Validate and sanitize input parameters from URL
$id = isset($_GET['id']) ? $_GET['id'] : null;
$id = filter_var($id, FILTER_SANITIZE_NUMBER_INT);

// Prepare a SQL statement using a prepared statement
$stmt = $conn->prepare("SELECT * FROM table WHERE id = ?");
$stmt->bind_param("i", $id);

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

// Process the results
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // Output the data
    echo $row['column_name'] . "<br>";
}

// Close the statement and connection
$stmt->close();
$conn->close();
?>

Keywords

PHP database querying URL interface design robustness

Related Questions