Does Laravel's toSql() method mask ids? (column value being replaced by question mark)

Question

I'm trying to debug some SQL queries that I'm doing in a testing suite. Using the following debugging code:

\Log::debug(User::first()->jobs()->toSql());

The SQL that prints out is:

`select * from `jobs` where `jobs`.`deleted_at` is null and `jobs`.`managed_by_id` = ? and `jobs`.`managed_by_id` is not null`

What is that question mark doing there? I've tested the query, and it works as expected. Is it because i'm selecting that first() user that this is happening?

Answer 1

Laravel uses Prepared Statements. They're a way of writing an SQL statement without dropping variables directly into the SQL string. The ? you see are placeholders or bindings for the information which will later be substituted and automatically sanitised by PDO. See the PHP docs for more information on prepared statements http://php.net/manual/en/pdo.prepared-statements.php

To view the data that will be substituted into the query string you can call the getBindings() function on the query as below.

$query = User::first()->jobs();

dd($query->toSql(), $query->getBindings());

The array of bindings get substituted in the same order the ? appear in the SQL statement.

Answer 2

In addition to @wader's answer, a 'macroable' way to get the raw SQL query with the bindings.

1. Add below macro function in AppServiceProvider boot() method.

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

2. Add an alias to the Eloquent Builder.

   \Illuminate\Database\Eloquent\Builder::macro('toRawSql', function(){
       return ($this->getQuery()->toRawSql());
   });
   

3. Then debug as usual.

   \Log::debug(User::first()->jobs()->toRawSql());
   

Note: from Laravel 5.1 to 5.3, Since Eloquent Builder doesn't make use of the Macroable trait, cannot add toRawSql an alias to the Eloquent Builder on the fly. Follow the below example to achieve the same.

E.g. Eloquent Builder (Laravel 5.1 - 5.3)

\Log::debug(User::first()->jobs()->getQuery()->toRawSql());

Answer 3

Just to reiterate @giovannipds great answer... i'm doing like this:

vsprintf(str_replace(['?'], ['\'%s\''], $query->toSql()), $query->getBindings())

Answer 4

Use the below code to print RAW SQL in Laravel:

echo "<pre>";
print_r($query->toSql());
print_r($query->getBindings());

Adding a PRE tag helps you read the result more accurately.