Secure MySQL query to prevent SQL injections gives Invalid parameter number error

Question

I am trying to secure MySQL code to prevent SQL injections so in my code below i use prepared statements and placeholders. For this reason I replaced the code:

return '(r.recipe_name LIKE "%' . $term . '%" OR (ri.ingredient_name IS NOT NULL AND ri.ingredient_name LIKE "%' . $term . '%"))';

with:

return '(r.recipe_name LIKE :term OR (ri.ingredient_name IS NOT NULL AND ri.ingredient_name LIKE :term))';     

so here is my edited PHP snippet:

$all_text_filter = '';
$ttAll = '';

if (isset($_REQUEST['q']) && $_REQUEST['q'] != '') {
    $search_terms = array_map('trim', explode(' ', $_REQUEST['q']));
    
    $text_conditions = array_filter($search_terms, function ($term) {
        return !empty($term);
    });

    $text_conditions = array_map(function ($term) {
        return '(r.recipe_name LIKE :term OR (ri.ingredient_name IS NOT NULL AND ri.ingredient_name LIKE :term))';
    }, $text_conditions);

    if (!empty($text_conditions)) {
        $all_text_filter = ' AND (' . implode(' OR ', $text_conditions) . ')';
        $ttAll = $all_text_filter;
    }
   
}
echo "Debug: ttAll = $ttAll";

$searchText = isset($_REQUEST['q']) ? htmlspecialchars($_REQUEST['q']) : '';
$searchTerms = explode(' ', $searchText);


$searchConditions = array_map(function($index, $term) {
  $paramName = ":searchTerm" . $index;
  return "r.recipe_name LIKE $paramName";
}, array_keys($searchTerms), $searchTerms);

try {

$q = $pdo->prepare("SELECT r.*,
    rc.recipe_category_name,
    rc.recipe_category_slug,
    rc1.recipe_cuisine_name,
    u.name,
    u.username,
    u.photo
    FROM recipes as r
    JOIN recipe_categories as rc ON r.recipe_category_id = rc.id
    JOIN recipe_cuisines rc1 ON r.recipe_cuisine_id = rc1.id
    JOIN users u ON r.user_id = u.id
    LEFT JOIN recipe_ingredients ri ON r.id = ri.recipe_id
    WHERE r.recipe_status='Active' " . $tt1 . $tt2 . $tt3 . $tt4 . $tt5 . $tt6 . $ttAll . "
    GROUP BY r.id
    ORDER BY 
      CASE 
        WHEN " . implode(' OR ', $searchConditions) . " THEN 1 
        ELSE 2 
      END,
      r.id DESC
    LIMIT :offset, :resultsPerPage");

$q->bindParam(':offset', $offset, PDO::PARAM_INT);
$q->bindParam(':resultsPerPage', $resultsPerPage, PDO::PARAM_INT);


// Here I bind the parameters for text conditions
  foreach ($text_conditions as $index => $termCondition) {
  $paramName = ":term" . $index;
  $q->bindValue($paramName, '%' . $termCondition . '%', PDO::PARAM_STR);
}


// Bind parameters for search terms
foreach ($searchTerms as $index => $term) {
    $paramName = ":searchTerm" . $index;
    $q->bindValue($paramName, '%' . $term . '%', PDO::PARAM_STR);    
}



//echo $q->queryString; // Display the SQL query for debugging
$q->execute();
$res_data = $q->fetchAll();
} catch (PDOException $e) {
  // Handle the exception, log the error, or show an error message
  echo "Error executing the query: " . $e->getMessage();
}

The problem is that now when I search for some terms eg: apple+lemon I get the error:

Error executing the query: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens 

when i execute the query for example:

SELECT r.*, rc.recipe_category_name, rc.recipe_category_slug, rc1.recipe_cuisine_name, u.name, u.username, u.photo FROM recipes as r JOIN recipe_categories as rc ON r.recipe_category_id = rc.id JOIN recipe_cuisines rc1 ON r.recipe_cuisine_id = rc1.id JOIN users u ON r.user_id = u.id LEFT JOIN recipe_ingredients ri ON r.id = ri.recipe_id WHERE r.recipe_status='Active' AND ((r.recipe_name LIKE :term OR (ri.ingredient_name IS NOT NULL AND ri.ingredient_name LIKE :term)) OR (r.recipe_name LIKE :term OR (ri.ingredient_name IS NOT NULL AND ri.ingredient_name LIKE :term))) GROUP BY r.id ORDER BY CASE WHEN r.recipe_name LIKE :searchTerm0 OR r.recipe_name LIKE :searchTerm1 THEN 1 ELSE 2 END, r.id DESC LIMIT :offset, :resultsPerPage

with the code:

return '(r.recipe_name LIKE "%' . $term . '%" OR (ri.ingredient_name IS NOT NULL AND ri.ingredient_name LIKE "%' . $term . '%"))';

Everything works fine !! I am struggling for hours to find a solution.

Answer 1

You are over-complicating it. Let's suppose you have a $terms array. Then you can do:

$params = [];
$criterias = [];
for ($i = 0; $i < $terms; $i++) {
    $params[":term_" . $i] = "%" . $terms[$i] . "%";
    $criterias[]="(r.recipe_name LIKE :term_{$i} OR (ri.ingredient_name IS NOT NULL AND ri.ingredient_name LIKE :term_{$i}))";
}
$criteria = implode(" OR ", $criterias);

Make sure you add your criteria properly to the query, add any further parameters you may have and make sure you apply the parameters when you execute the query.