Helpful Eloquent Debug Snippet

[damms005]

I saw this handy macro from a blogpost and I believe it will be a good addition to Laravel, for a significantly improved DX, especially when working with DB/Eloquent.

use Illuminate\Database\Eloquent\Builder;

Builder::macro('toRawSql', function() {
    return array_reduce($this->getBindings(), function($sql, $binding) {
        return preg_replace('/\?/', is_numeric($binding)
            ? $binding
            : "'".$binding."'", $sql, 1);
    }, $this->toSql());
});

The above makes us go from this:

$books = Book::where('author', 'Ruskin Bond')->toSql();

dd($books);
// select * from `books` where `author` = ?

to this:

$books = Book::where('author', 'Ruskin Bond')->toRawSql();

dd($books);
// select * from `books` where `author` = 'Ruskin Bond'

[Sophist-UK]

I am somewhat unclear why toSql() doesn't already include the bindings.

I can only imagine two possible reasons for this:

a. That there is some sort of perceived security risk by including the variable e.g. if the code was $user = Users::where('password', 'my_secret_password')->toSql();. In the example given, the explicit use of toSql() by the developer is not going to be a security risk. It is likely only for development and debugging purposes and not likely to be included in production code. Also the parameter is already in the code, so there are no secrets to be given away. Of course if toSql is used in exception handlers, then maybe this would be a problem.

b. toSql() could be used by Eloquent under the covers e.g. to pre-compile and cache the query to improve performance (so the variables need to be left unbound).

Either way, it seems to me that renaming toSql() to e.g. toUnboundSql() and then using your proposed toRawSql() as toSql() migfht actually be a better solution.

The only other issue is if the example was used in a unit test, and tested against a string - in which case that test would then fail and this would be a breaking change. However surely you would want such tests to check that the binding had been substituted correctly anyway.

[damms005]

Your submission totally makes sense to me. FWIW, this kind of addition is clearly for testing/dev purposes only.

[gbradley]

I can sort of understand why the bindings aren't included - with the above technique, the SQL string generated may not accurately represent what the database server will execute e.g.

$books = Book::where("author", "Tim O' OR 1='1")->toRawSql();

would generate

select * from `books` where `author` = 'Tim O' OR 1='1'

which, if run, would provide very different results from the version that uses prepared statements.

[Sophist-UK]

Yes - it would rather!!!!

SQL injection by the programmer rather than a hacker!!

$books = Book::where("author", "Tim'; SELECT * FROM users WHERE name <> 'Tim")->toRawSql();

select * from `books` where `author` = 'Tim'; SELECT * FROM users WHERE name <> 'Tim'

[gbradley]

Haha yeah, maybe not a security risk but certainly misleading!

[crynobone]

Hey there,

We're closing this issue because it's inactive, already solved, old, or not relevant anymore. Feel free to open up a new issue if you're still experiencing this problem.

[shaedrich]

This has been added to Laravel 10 via #47507