What are some potential pitfalls when using PHP to query a database based on geographic proximity?

One potential pitfall when using PHP to query a database based on geographic proximity is not properly sanitizing user input, which can leave your application vulnerable to SQL injection attacks. To solve this issue, always use prepared statements or parameterized queries to securely pass user input to your database queries. 

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

// Sanitize user input for latitude and longitude
$latitude = filter_var($_POST['latitude'], FILTER_VALIDATE_FLOAT);
$longitude = filter_var($_POST['longitude'], FILTER_VALIDATE_FLOAT);

// Prepare and execute query using prepared statements
$stmt = $pdo->prepare("SELECT * FROM locations WHERE SQRT(POW(latitude - :latitude, 2) + POW(longitude - :longitude, 2)) < :radius");
$stmt->bindParam(':latitude', $latitude);
$stmt->bindParam(':longitude', $longitude);
$stmt->bindParam(':radius', $radius);
$stmt->execute();

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

// Process results
foreach ($results as $result) {
    echo $result['name'] . '<br>';
}

Keywords

SQL injection geospatial queries distance calculations PDO error handling

Related Questions