Error when using functions in Where clauses

[Genie23]

Hello,

First of all, congratulations for this very useful project. However, while developing an ORM based on this module, I need to use a MySQL function (and more precisely DATABASE()) in a Where clause of a query.

For the record, it's a query in the informations_schema database to get the list of columns of a table and then adapt the creation of my object accordingly.

Anyway, I logically did something like this to get my list of columns:

        $fields = $h->table("information_schema.columns")
            ->select(["column_name", "column_type"])
            ->where("table_schema", new Func("DATABASE"))
            ->where("table_name", "my_tablename")
            ->get();

Except that I realized that the new Func("DATABASE") object was not translated into SQL, which inevitably crashes PDO when applying the parameters of the request to its execution.

For all those who would have this problem, and for a future update of the module, I can propose the following correction which works for me:

// In the MySQL translator class, I modified the param() method as follows :
    /**
     * creates an parameter and adds it
     *
     * @param mixed $value
     * @return string
     */
    protected function param($value)
    {
        if (!$this->isExpression($value)) {
            // ************************ START HERE ************************
            if ($value instanceof Func)
                // If the value of the parameter is an instance of the Func class, then it must be inserted as is after
                // escaping in the query, otherwise it will be escaped as a string when the query is executed.
                return $this->escapeFunction($value);
            // ************************* END HERE *************************
            $this->addParameter($value);
            return '?';
        }

        return $value;
    }

I don't know if this was the best way to do it, but being able to use MySQL functions in Where clauses seems to me indispensable at least for some uses (which are probably not limited to my case alone). That's why I propose this modification. If I have time, I'll see to make a pull request, but in the meantime I gave you my solution here.

I hope this report and my proposed solution have been useful to you.

Regards,

[Genie23]

Hello,

By chance I have just updated the module for my site (deleting then reinstalling all the composer modules I use, including this one), and I took the opportunity to see if you could correct the problem I had raised.

Unfortunately I still have the same problem: a Func object in a Where clause is not interpreted correctly.

For more details, here is the error message I get:
Fatal error: Uncaught Error: Object of class ClanCats\Hydrahon\Query\Sql\Func could not be converted to string

As well as the lines of code concerned by the bug (it's the initialization of hydrahon - the line that triggers the bug is indicated by the arrow in comment):

new Builder("mysql", function ($query, $queryString, $queryParameters) use ($model, $table) {
    // Préparation de la requête
    $statement = self::get()->prepare($queryString);

    // Retourne toujours un tableau associatif
    $statement->setFetchMode(\PDO::FETCH_ASSOC);

    // Exécution de la requête préparée
    $statement->execute($queryParameters); // <------------------------------ HERE ---------------------------<

    if ($query instanceof \ClanCats\Hydrahon\Query\Sql\FetchableInterface) {
        // Si la requête retourne un resultset, récupération du résultat
        // Retourne le resultset
        return $statement->fetchAll();
    } elseif ($query instanceof \ClanCats\Hydrahon\Query\Sql\Insert) {
        // Si la requête est un INSERT, retourne l'identifiant auto-incrémenté de la dernière ligne insérée
        return self::get()->lastInsertId();
    } else {
        // Dans les autres cas, retourne le nombre de lignes affectées
        return $statement->rowCount();
    }
});

However, I come back with a new modification to propose to you so that this bug is corrected (it fixed the bug for me).

In the vendor/clancats/hydrahon/src/Translator/Mysql.php file, modify the param method as follows:

protected function param($value)
{
    if (!$this->isExpression($value) and !$this->isFunction($value)) {
        $this->addParameter($value); return '?';
    }
    return $this->escape($value);
}

Unfortunately I'm afraid I don't have time yet to propose a pull request to make my patch available in this module.

Best regards,
Cédric