Building Dynamic Search Queries Without SQL Injection
Building a search feature for a blood donor application requires more than just concatenating strings into a query. The core challenge in your current implementation is how you handle conditional logic when constructing the WHERE clause. When filtering by a single criterion, your approach works because the condition is always present or absent. However, attempting to combine multiple filters with simple string concatenation leads to syntax errors and, more critically, exposes your application to SQL injection vulnerabilities.
The primary issue in your search_donar function is the lack of parentheses around the conditional clauses. In SQL, a WHERE statement expects a single logical condition or a boolean expression enclosed in brackets. Your current code generates something like:
SELECT * FROM donar WHERE name='John' sex='M' blood_group='A'
This is syntactically invalid because the database engine cannot distinguish between multiple column comparisons without an operator connecting them. You need to wrap the concatenated conditions in parentheses so they are evaluated as a single logical unit.
Furthermore, using mysql_query and directly inserting $_POST variables into the query string is a recipe for disaster. Even if you fix the syntax by adding brackets like (WHERE name='$by_name' AND sex='$by_sex'), you leave the application wide open to SQL injection attacks. An attacker could input '; DROP TABLE donar; -- into the name field and execute arbitrary commands.
The robust solution is to construct the query using prepared statements. This separates the SQL logic from the user data, ensuring that special characters are escaped automatically. In modern PHP development, this is often handled by PDO or MySQLi. Here is how you can refactor the logic to safely handle multiple filters:
function search_donar($postData) {
$by_name = isset($postData['by_name']) ? trim($postData['by_name']) : '';
$by_sex = isset($postData['by_sex']) ? trim($postData['by_sex']) : '';
$by_group = isset($postData['by_group']) ? trim($postData['by_group']) : '';
$by_level = isset($postData['by_level']) ? trim($postData['by_level']) : '';
// Initialize the base query with a placeholder for the WHERE clause
$sql = "SELECT * FROM donar WHERE 1=1";
$params = [];
if (!empty($by_name)) {
$sql .= " AND name LIKE ?";
$params[] = "%$by_name%"; // Use LIKE for partial matches in names
}
if (!empty($by_sex)) {
$sql .= " AND sex = ?";
$params[] = $by_sex;
}
if (!empty($by_group)) {
$sql .= " AND blood_group = ?";
$params[] = $by_group;
}
if (!empty($by_level)) {
$sql .= " AND e_level = ?";
$params[] = $by_level;
}
// Execute the query using prepared statements
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
This pattern uses 1=1 as a placeholder for the first condition. This allows you to append additional AND clauses without worrying about whether it is the first condition in the list. If no filters are applied, the query simply becomes SELECT * FROM donar WHERE 1=1, which returns all records efficiently.
When implementing this, ensure your HTML inputs use proper types and validation. For example, blood groups should ideally be selected from a dropdown rather than a text input to prevent invalid values like "A+" or "B-" from bypassing simple string checks if your database only stores normalized codes. Additionally, always sanitize output when displaying results back to the user to prevent Cross-Site Scripting (XSS).
For larger applications, consider using an ORM or a framework that abstracts these concerns entirely. Laravel, for instance, provides expressive query builders that handle dynamic filtering elegantly through methods like where chained with optional conditions. If you are maintaining legacy codebases using the old MySQL extension, migrating to PDO is a necessary step to ensure security and maintainability.
Remember that performance matters as well. As your donor database grows, indexing columns used in filters (name, sex, blood_group) becomes critical. Without indexes on these columns, every search will require a full table scan, slowing down response times significantly. Always verify that your database schema supports the query patterns you are building.