Laravel DB::statement is not able to prepare values properly for the queries that involves column expressions

Question

I wanna know the problem that is arising between DB::statement and DB::unprepared queries, So apparently DB::statement methods are not functioning with queries that do expressions on columns itself, something like this:

UPDATE tags SET ? = ? + 2 WHERE ? > ? AND user_id = ? AND tree_id = ?

This results into SQL as:

UPDATE tags SET rgt = rgt + 2 WHERE rgt > 2 AND user_id = 1 AND tree_id = 1

^This query when used within mysql interpreter works absolutely fine but shits brick with laravel's DB::statement method (it works fine with unprepared method btw).

Whats the reason behind this mismatch?

The error it pops up with is SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax which is weird because its correct syntax and already works fine with mysql.

So the statement method is doing this:

    {
        return $this->run($query, $bindings, function ($query, $bindings) {
            if ($this->pretending()) {
                return true;
            }
            $statement = $this->getPdo()->prepare($query);
            $this->bindValues($statement, $this->prepareBindings($bindings));
            $this->recordsHaveBeenModified();
            return $statement->execute();
        });
    }

While the unprepared one:

    {
        return $this->run($query, [], function ($query) {
            if ($this->pretending()) {
                return true;
            }
            $this->recordsHaveBeenModified(
                $change = ($this->getPdo()->exec($query) === false ? false : true)
            );
            return $change;
        });
    }

I think the issue is arriving at the prepare method.

Issue References elsewhere that couldn't pinpoint the problem either:

User suggesting that unprepared method should be used for the advanced queries? This query is as basic as it can possibly get: https://laracasts.com/discuss/channels/general-discussion/raw-queries-1?page=0

Another user suggesting to use unprepared instead of breaking down to simpler queries: https://laracasts.com/discuss/channels/laravel/why-does-my-raw-query-not-work-inside-laravel-db?page=2

Bonus Question: Moreover, if you know any Eloquent way of handling this query, it'd be awesome! (In the sense how to handle column level expressions via native, where, update, etc methods.)

Edit: This question is not a duplicate to How to do update query on laravel fluent using db::raw because it still involves hardcoding the column value. DB::raw('column * 2')

My question is purely from not doing any hardcoding statement and let laravel do the bindings.

For example: This query -

DB::update(DB::raw(
            'UPDATE tags SET ? = ? ? ? WHERE ? >= ? AND user_id = ? AND tree_id = ?'),
            [$flag, $flag, $operator, $integer, $flag, $controlIndex, $user_id, $tree_id]
        );

produces error as follows: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? = ? ? ? WHERE ? >= ? AND user_id = ? AND tree_id = ?' at line 1 (SQL: UPDATE tags SET rgt = rgt + 2 WHERE rgt >= 1 AND user_id = 1 AND tree_id = 1)

Just look at the SQL query: UPDATE tags SET rgt = rgt + 2 WHERE rgt >= 1 AND user_id = 1 AND tree_id = 1 - This is completely legit and works if put in mysql interpreter!

Answer 1

Okay after more inspection, I've figured that you cannot bind a table/column name and only values in your query

For more reference: https://stackoverflow.com/a/15990488/6303162

To understand why binding a table (or column) name doesn't work, you have to understand how the placeholders in prepared statements work: they are not simply substituted in as (suitably escaped) strings, and the resulting SQL executed. Instead, a DBMS asked to "prepare" a statement comes up with a complete query plan for how it would execute that query, including which tables and indexes it would use, which will be the same regardless of how you fill in the placeholders.

Seemingly you cannot bind database names either - so its not a defect of Laravel or PHP but just the way databases are designed.

The plan for SELECT name FROM my_table WHERE id = :value will be the same whatever you substitute for :value, but the seemingly similar SELECT name FROM :table WHERE id = :value cannot be planned, because the DBMS has no idea what table you're actually going to select from.

Hence what should be done is to have a whitelist of columns and make sure you are filtering on the basis of that before executing the query. The task of preventing any SQL Injection is provided to the developers. I would have had thought that Laravel could probably implement a column based binding but it could have its own merits and cons.

1. Run a query to find all columns for the cases where column binding is being done.
2. Take the whitelist as some kind of variable in the Model class itself like $fillable exists.

Nevertheless, the most elegant solution I could come-up with went as follows:

DB::table('tags')->where([
            ['user_id', $user_id],
            ['tree_id', $tree_id],
            [$flag, '>', $controlIndex]
        ])->update([
            $flag => DB::raw($flag . $operator . $integer)
        ]);

Where $flag, $operator and $integer are simply enums.

Update:

I asked around the same question around some chat forums when a person recommended me the increment and decrement methods, which totally fits the solution to the problem! So my updated query call looks as follows:

Tags::where([
            ['user_id', $user_id],
            ['tree_id', $tree_id],
            [$flag, '>', $controlIndex]])
    ->crement($flag, $integer, $operator);

crement is just a local scope method attached to Tags model that directs whether the call should be of increment or decrement.