如何构建带条件筛选及排序的SQL查询语句？

Hey there! Let's break down how to safely and correctly build that dynamic SQL query with conditional filtering and sorting in PHP—because directly splicing user input into SQL is a huge security risk, and messy string concatenation leads to hard-to-debug errors.

Key Principles First #

Before diving into code, remember two non-negotiables:

1. Never trust user input: Always validate and sanitize values from $_GET/$_POST to prevent SQL injection.
2. Use prepared statements: Separate SQL logic from user data to avoid injection attacks entirely.

Step-by-Step Implementation #

Here's a clean, secure way to rewrite your logic using PDO (you can adapt this to mysqli too):

// Initialize query parts (easier to manage than messy string concatenation)
$querySegments = [
    'SELECT * FROM products',
    'WHERE 1=1' // Acts as a placeholder to simplify adding AND conditions later
];
$boundParams = [];

// Get and validate user input (set defaults for missing/invalid values)
$category = $_GET['Category'] ?? '';
$priceRange = $_GET['price'] ?? '';

// Define allowed price ranges to reject malicious input
$validPriceRanges = ['', '0-500', '500-1000', '1000+'];
if (!in_array($priceRange, $validPriceRanges)) {
    $priceRange = ''; // Fallback to default if input is invalid
}

// Handle category-specific filtering
if ($category === 'X') {
    switch ($priceRange) {
        case '0-500':
            $querySegments[] = 'price BETWEEN ? AND ?';
            $boundParams[] = 0;
            $boundParams[] = 500;
            break;
        case '500-1000':
            $querySegments[] = 'price BETWEEN ? AND ?';
            $boundParams[] = 500;
            $boundParams[] = 1000;
            break;
        case '1000+':
            $querySegments[] = 'price >= ?';
            $boundParams[] = 1000;
            break;
        default:
            // Add your default "some conditions" for empty price input
            $querySegments[] = 'status = ?';
            $boundParams[] = 'active';
            break;
    }
} else {
    // Logic for non-X categories
    switch ($priceRange) {
        case '0-500':
            $querySegments[] = 'price BETWEEN ? AND ?';
            $boundParams[] = 0;
            $boundParams[] = 500;
            break;
        case '500-1000':
            $querySegments[] = 'price BETWEEN ? AND ?';
            $boundParams[] = 500;
            $boundParams[] = 1000;
            break;
        default:
            // Your default conditions for non-X categories
            $querySegments[] = 'stock_quantity > ?';
            $boundParams[] = 0;
            break;
    }
}

// Add safe sorting logic (validate sort field and direction)
$sortField = $_GET['sort'] ?? 'price';
$validSortFields = ['price', 'product_name', 'created_date'];
if (!in_array($sortField, $validSortFields)) {
    $sortField = 'price'; // Fallback to safe default
}

$sortDir = strtoupper($_GET['dir'] ?? 'ASC');
if (!in_array($sortDir, ['ASC', 'DESC'])) {
    $sortDir = 'ASC';
}
$querySegments[] = "ORDER BY $sortField $sortDir";

// Assemble the final query
$finalQuery = implode(' ', $querySegments);

// Execute with prepared statements (100% injection-safe)
try {
    $pdo = new PDO('mysql:host=localhost;dbname=your_database', 'username', 'password');
    $stmt = $pdo->prepare($finalQuery);
    $stmt->execute($boundParams);
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    die('Database error: ' . $e->getMessage());
}

Why This Works Better #

• White-list validation: We only accept predefined values for price ranges and sort fields—no malicious input can sneak in.
• Prepared statements: User data is passed separately from the SQL query, eliminating SQL injection risks entirely.
• Cleaner structure: Using an array to build query segments avoids messy string concatenation errors (like missing spaces or extra AND keywords).
• Fallback defaults: Invalid or missing user input won't break the query—we use safe defaults instead.

内容的提问来源于stack exchange，提问作者Ria