soil-report-ai/client-assets/table/gettable.php

<?php
/**
 * client-assets/table/gettable.php
 *
 * AJAX DataTable endpoint. Requires authentication and a whitelisted table name.
 */

require_once __DIR__ . '/../../config/database.php';
require_once __DIR__ . '/../../lib/auth.php';

if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

if (!isLoggedIn()) {
    http_response_code(401);
    echo json_encode(['error' => 'Unauthorised']);
    exit;
}

// Only these tables may be queried through this endpoint
$allowedTables = [
    'soil_records',
    'client_records',
    'plant_records',
    'animal_records',
    'water_records',
    'weather_station',
    'calendar_events',
    'fertiliser_specifications',
    'block_info',
    'crop_info',
];

$table = $_REQUEST['table'] ?? '';

if (!in_array($table, $allowedTables, true)) {
    http_response_code(400);
    echo json_encode(['error' => 'Invalid table']);
    exit;
}

$num_rows = isset($_REQUEST['num_rows']) ? max(1, min((int) $_REQUEST['num_rows'], 200)) : 30;
$page     = isset($_REQUEST['page'])     ? max(0, (int) $_REQUEST['page']) : 0;

// Allowed ORDER BY columns must also be whitelisted to prevent injection
$allowedOrders = [
    'id', 'date', 'client_name', 'email', 'created_at', 'date_sampled',
    'lab_no', 'site_id', 'crop_type', 'soil_type',
];
$allowedDirs = ['ASC', 'DESC'];

$order = isset($_REQUEST['order']) && in_array($_REQUEST['order'], $allowedOrders, true)
    ? $_REQUEST['order'] : null;
$dir = isset($_REQUEST['dir']) && in_array(strtoupper($_REQUEST['dir']), $allowedDirs, true)
    ? strtoupper($_REQUEST['dir']) : 'ASC';

try {
    $pdo = getDBConnection();

    // Column names
    $stmt   = $pdo->query("SHOW COLUMNS FROM `{$table}`");
    $fields = $stmt->fetchAll(PDO::FETCH_COLUMN);

    // Main query — table name is from whitelist so safe to interpolate
    $sql = "SELECT * FROM `{$table}`";
    if ($order !== null) {
        $sql .= " ORDER BY `{$order}` {$dir}";
    }
    $sql .= ' LIMIT :limit OFFSET :offset';

    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':limit',  $num_rows, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $num_rows * $page, PDO::PARAM_INT);
    $stmt->execute();
    $rows = $stmt->fetchAll(PDO::FETCH_NUM);

    // Count
    $countStmt = $pdo->query("SELECT COUNT(*) FROM `{$table}`");
    $total     = (int) $countStmt->fetchColumn();

    header('Content-Type: application/json');
    echo json_encode([
        'rows'          => $rows,
        'fields'        => $fields,
        'total_entries' => $total,
    ]);
} catch (PDOException $e) {
    error_log('gettable.php DB error: ' . $e->getMessage());
    http_response_code(500);
    echo json_encode(['error' => 'Database error']);
}