Call SQL function in prepared request

Question

i'm trying to create a restaurant dynamic webpage displaying the menu of a week selected with a scrolling menu . It seems like the pdo query i'm making to go search my products on the database based on the scrolling menu choice doesn't take into account my call to the function WEEK because it is included in a bindvalues since i want to have the possibility to add some spec to my sql statement depending on the visitor scrolling menu choice. I build it on a MVC model. When i var_dump() it returns an empty array.

Here is my controller

        if ($_SERVER['REQUEST_METHOD'] == 'POST'){
            if ($_POST['week'] == 'currentWeek'){
                $entree = $menumodel->displayTypeOfProduitsPerWeek('entree','WEEK(NOW())');
                $plat = $menumodel->displayTypeOfProduitsPerWeek('plat','WEEK(NOW())');
                $dessert = $menumodel->displayTypeOfProduitsPerWeek('dessert','WEEK(NOW())');
            }
            elseif ($_POST['week'] == 'nextWeek'){
                $entree = $menumodel->displayTypeOfProduitsPerWeek('entree','WEEK(DATE_ADD(NOW(), INTERVAL 7 DAY))');
                $plat = $menumodel->displayTypeOfProduitsPerWeek('plat','WEEK(DATE_ADD(NOW(), INTERVAL 7 DAY))');
                $dessert = $menumodel->displayTypeOfProduitsPerWeek('dessert','WEEK(DATE_ADD(NOW(), INTERVAL 7 DAY))');
            }

Here is my model

    public function displayTypeOfProduitsPerWeek($type, $semaine = 'WEEK(NOW())')
    {
        $query = "SELECT annee, semaine, titre, description, type, prix 
                    FROM produit 
                    WHERE semaine=:semaine 
                    and type=:type 
                    and annee=:annee";
        $statement = $this->pdo->prepare($query);
        $statement->bindValue(':semaine', $semaine, PDO::PARAM_STR);
        $statement->bindValue(':annee', 'YEAR(NOW())', PDO::PARAM_STR);
        $statement->bindValue(':type', $type, PDO::PARAM_STR);
        $statement->execute();
        return $statement->fetchAll(PDO::FETCH_ASSOC);
    }
    

Here is my scrolling menu

<select name="week">
    <option value="currentWeek">This week/option>
    <option value="nextWeek">Next Week</option>
</select>

Answer 1

If you parameterise a SQL function call like this, it will not be interpreted as SQL code by the database - that's a key safety feature you get with parameterisation, to help prevent SQL injection. Instead, the database engine would attempt to use that value as literal text in the query. That would explain why it returns no rows in this scenario, since you're (presumably) trying to compare week and year strings in your table to those pieces of text.

Instead you'll need to just concatenate these values into your query. Because your own controller code is pre-defining the SQL to be used (rather than directly using any input provided by the front-end), there should be no SQL injection risk. You also shouldn't parameterise the the fixed 'YEAR(NOW())' string, for the same reason.

This should work:

public function displayTypeOfProduitsPerWeek($type, $semaine = 'WEEK(NOW())')
{
    $query = "SELECT annee, semaine, titre, description, type, prix 
                FROM produit 
                WHERE semaine = $semaine 
                and type = :type 
                and annee = YEAR(NOW())";
    $statement = $this->pdo->prepare($query);
    $statement->bindValue(':type', $type, PDO::PARAM_STR);
    $statement->execute();
    return $statement->fetchAll(PDO::FETCH_ASSOC);
}