Framework: Eloquent whereRaw method incorrectly binds floats

[slavicd]

• Laravel Version: 6.11.0
• PHP Version: 7.3.13
• Database Driver & Version: SQLITE

Description:

whereRaw method binds float values as strings.

Steps To Reproduce:

Provided a stations table with a REAL lat column:
Station::whereRaw('lat > ?', [40.0])->get()
will result in SQL:
select * from "stations" where "lat" > "40.0"
which clearly doesn't always work.

[driesvints]

Hmm that indeed seems problematic. I'm not too familiar with Sqlite here. Is this the "real" datatype you need? Is float a datatype at all in sqlite? https://www.sqlite.org/datatype3.html

[slavicd]

The float is called "REAL" in SQLite, however I doubt that this problem is SQLite specific.

[driesvints]

Welcoming some help with this.

[slavicd]

If nobody checks before I do, I will try to reproduce with MySQL and/or see the underlying code tomorrow.
Is the "needs more info" label still relevant?

[slavicd]

Confirmed the same issue with Mysql (MariaDb). The following is from the general query log:

30 Prepare   select * from `stations` where lat > ?
30 Execute   select * from `stations` where lat > '40'

If I change the code from
Station::whereRaw('lat > ?', [40.0])->get();
to
Station::whereRaw('lat > ?', [45])->get();
the query is logged as expected:

31 Prepare   select * from `stations` where lat > ?
31 Execute   select * from `stations` where lat > 45

[mantas-done]

PHP 7.2
MySQL 7
Laravel 6.12

Can't reproduce, both queries generate the same sql

[slavicd]

mantas-done, could you check the actual SQL logs, rather than Laravel reports?

[driesvints]

Marking it as a bug for now. Appreciating help with this.

[mo3000]

In Illuminate/Database/Connection.php, function bindValues is:

    public function bindValues($statement, $bindings)
    {
        foreach ($bindings as $key => $value) {
            $statement->bindValue(
                is_string($key) ? $key : $key + 1,
                $value,
                is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR
            );
        }
    }

If you pass a float to it, PDO::PARAM_STR will apply.
It's ok to me. Becase mysql/postresql will check the column datatype then cast it correctly no matter you quote it or not and there's no PDO::PARAM_FLOAT to use.
Some database like postgres will complain about comparing a int to a float, but that's your own fault.

[staudenmeir]

which clearly doesn't always work.

What's an example of where it doesn't work?

[driesvints]

@staudenmeir think they explain that here: https://github.com/laravel/framework/issues/31201#issuecomment-582354838

[staudenmeir]

@slavicd What's an example of a query that returns an incorrect result because of the binding?

[slavicd]

Check the "Steps to Reproduce" section in the main description. That query fails in SQLite

[rodrigopedra]

As noted by @mo3000 , this seems to be a limitation on PHP's PDO itself. PDO doesn't have a parameter type for floats:

https://www.php.net/manual/en/pdo.constants.php

There was a RFC for adding it but it was withdrawn:

https://wiki.php.net/rfc/pdo_float_type

So one cannot use PDO to bind a float value without it being converted to a string.

One solution might be to add 0.0 so SQLite converts the binding to a float before comparing:

~php
Station::whereRaw('lat > (? + 0.0)', [40.0])->get()
~

Didn't tested it though, hope it helps.

[driesvints]

Thanks for explaining @rodrigopedra.

@slavicd please see the answer above.