Question mark in laravel toSQL()

Question

I get this output when trying to use toSQL() to debug my queries.

Laravel code:

$services = Service::latest()->where('status', '=', '0');

Output SQL:

"select * from `services` where `status` = ? order by `created_at` desc"

How can I get a proper query without ? mark? Thanks!

@derpirscher I can know what he said. and in my answer, i describe and write a code for a complete query. i think he want to know the result last query that send to mysql. — saber tabatabaee yazdi, Aug 06 '23 at 06:49

score 4 · Accepted Answer · answered Dec 27 '21 at 07:34

4

To view the data that will be substituted into the query string you can call the getBindings() function on the query like 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.

check this link

answered Dec 27 '21 at 07:34

Ossama Abd

464
2
10

i can write a code to return whole query with parameters as he want – saber tabatabaee yazdi Aug 06 '23 at 08:15

score 0 · Answer 2 · answered Aug 05 '23 at 17:39

If you want to retrieve the full query that is sent to MySQL, including the actual parameter values, you can use the getBindings() method in conjunction with toSql().

The getBindings() method returns an array of the parameter values bound to the query. By combining this array with the query string obtained from toSql(), you can replace the question marks with their corresponding values to get the complete query.

Here's an example of how you can achieve that:

$query = DB::table('users')
            ->where('name', 'John')
            ->where('age', '>', 25);

$sql = $query->toSql();
$bindings = $query->getBindings();

// Replace the question marks with the actual values
$fullQuery = str_replace('?', "'%s'", $sql);
$fullQuery = vsprintf($fullQuery, $bindings);

echo $fullQuery;

In this example, toSql() is used to get the raw SQL query, and getBindings() retrieves the parameter values. The str_replace() function replaces each question mark with '%s', which is a placeholder for the values. Finally, vsprintf() is used to substitute the placeholders with the actual values, resulting in the complete query string.

Keep in mind that this approach is primarily intended for debugging purposes or inspecting the generated queries. When executing queries through Laravel's query builder, parameter binding is automatically handled for you, providing security against SQL injection attacks.

This will create a query like `select * from \`services\` where \`status\` = '0' order by \`created_at\` desc` which is not per se wrong. But it (1) has unnecessary type conversions (2) depending on the localization handling of decimal values it may generate indeed an invalid query and (3) if one of the paramamerts contains a `'` it will surely generate an invalid query. — derpirscher, Aug 06 '23 at 10:57
questioner of this question said: I get this output when trying to use toSQL() to debug my queries. How can I get a proper query without : ? mark? he didn't want anything else. you can ask your question in separate new question and send link here for us — saber tabatabaee yazdi, Aug 07 '23 at 18:24

Question mark in laravel toSQL()

2 Answers2