Get Raw SQL Query String from Query Builder in PHP

• Introduction
• Step-by-Step Guide
• Code Example
• Additional Notes
• Summary
• Conclusion
• References

Sometimes you need to see the raw SQL query that your Laravel Query Builder instance will execute. This can be useful for debugging, logging, or simply understanding how your code translates to SQL. Here's how to get the raw SQL query from a Laravel Query Builder object:

1. Start with your Laravel Query Builder instance:

   $query = DB::table('users')->where('active', 1);

2. Access the SQL query and bindings:

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

3. Replace the placeholders in the SQL query with the actual bindings:

   $rawQuery = str_replace(
       array_map(fn($v) => "?", $bindings),
       array_map(fn($v) => is_string($v) ? "'$v'" : $v, $bindings),
       $sql
   );

Now, $rawQuery will contain the raw SQL query string that would be executed. For example:

"select * from `users` where `active` = '1'" 

This PHP code demonstrates how to convert a Laravel Query Builder instance into a raw SQL query string with values injected. It first builds a query to select active users. Then, it extracts the SQL query and bindings from the Query Builder. Finally, it replaces placeholders in the SQL query with the actual values from the bindings, resulting in a complete raw SQL query ready for execution. However, this approach is highly vulnerable to SQL injection and should not be used with user-provided input.

<?php

use Illuminate\Support\Facades\DB;

// 1. Start with your Laravel Query Builder instance
$query = DB::table('users')->where('active', 1);

// 2. Access the SQL query and bindings
$sql = $query->toSql();
$bindings = $query->getBindings();

// 3. Replace the placeholders in the SQL query with the actual bindings
$rawQuery = str_replace(
    array_map(fn($v) => "?", $bindings),
    array_map(fn($v) => is_string($v) ? "'$v'" : $v, $bindings),
    $sql
);

// Output the raw SQL query
echo $rawQuery; // Output: "select * from `users` where `active` = '1'" 

?>

Explanation:

1. Building the Query:

   • We start by creating a Laravel Query Builder instance targeting the users table.
   • The where('active', 1) clause filters for users where the active column is equal to 1.

2. Extracting SQL and Bindings:

   • $query->toSql(): Retrieves the raw SQL query string with placeholders ("?") for bindings.
   • $query->getBindings(): Gets an array of values that will replace the placeholders.

3. Constructing the Raw Query:

   • array_map(fn($v) => "?", $bindings): Creates an array of "?" placeholders matching the number of bindings.
   • array_map(fn($v) => is_string($v) ? "'$v'" : $v, $bindings): Processes each binding:
     • If it's a string, it's enclosed in single quotes to be used directly in the SQL.
     • If it's not a string (e.g., an integer), it's used as is.
   • str_replace(): Replaces the "?" placeholders in the $sql string with the processed bindings.

Important Note:

• Security: This code is for demonstration purposes only. Directly injecting values into SQL queries like this is highly vulnerable to SQL injection attacks. Never use this approach with user-provided input. Always rely on Laravel's built-in query builder or Eloquent ORM for safe parameter binding.
• Database-Specific: The placeholder character ("?") might vary depending on your database system. Adjust accordingly if you're not using MySQL or a compatible database.

• Debugging: This technique is particularly helpful when debugging complex queries. Seeing the raw SQL allows you to understand exactly what's being sent to the database and identify potential issues.
• Learning SQL: For developers newer to SQL, this can be a useful way to see how Laravel's fluent query builder syntax translates into standard SQL queries.
• Alternative to dd() and dump(): While Laravel's debugging helpers like dd() and dump() can display query information, they don't always show the fully constructed SQL query, especially with complex joins and subqueries. This method provides a more direct way to see the final SQL.
• Logging: You can incorporate this code into your application's logging to record the raw SQL queries being executed. This can be valuable for performance analysis and troubleshooting.
• Eloquent ORM: While this example uses the Query Builder, a similar approach can be applied to Eloquent models. You can call toSql() and getBindings() on an Eloquent query builder instance as well.
• Prepared Statements: Remember that Laravel uses prepared statements by default. The placeholders in the raw SQL represent values that are sent to the database separately, preventing SQL injection vulnerabilities.
• Database Portability: Be mindful of database-specific syntax when working with raw SQL. The placeholder character and quoting conventions might differ between database systems.

Caution:

• User Input: Never directly embed user-provided data into the raw SQL query string. Always use parameterized queries or Laravel's query builder methods to prevent SQL injection vulnerabilities.
• Performance: For most use cases, Laravel's query builder and Eloquent ORM offer better performance and security than manually constructing raw SQL queries. Only use this technique when absolutely necessary for debugging or specific situations.

This article explained how to get the raw SQL query from a Laravel Query Builder object for debugging or logging purposes. While this approach can be helpful in understanding how Laravel constructs SQL queries, it's crucial to remember its limitations. Directly injecting values into raw SQL strings is highly susceptible to SQL injection vulnerabilities and should never be done with user-provided input. Always rely on Laravel's parameterized queries or Eloquent ORM for secure database interactions. This method is best suited for development and debugging, providing insights into the underlying SQL generated by your Laravel code.

• How to get the raw SQL query from the Laravel Query Builder ... | Learn how to get the raw SQL query from a Laravel Eloquent call – or all if there are multiple ones.
• In PHP with PDO, how to check the final SQL parametrized query ... | Nov 23, 2009 ... How do I get the query builder to output its raw SQL query as a string? 557 · How to show the last queries executed on MySQL? 213 · Doctrine ...
• Six Ways to Get Raw SQL Output From Query Builder in Laravel ... | Welcome to my personal website where I share some blog posts and other resources about PHP, Laravel and web development in general.
• php - How can I get the raw query string from Laravel's query builder ... | Nov 18, 2013 ... I have a complex query created by a few conditions, and I would like to get the final SQL query from the builder object is about to execute. Can I do that?
• How to Extract Raw SQL Output from the Laravel Query Builder? 2 ... | Working with Laravel Query Builder becomes highly convenient when constructing database queries without writing raw SQL.
• php - How to print out the real sql, not just the prepared statement? | Jan 19, 2010 ... Doctrine is not sending a "real SQL query" to the database server : it is actually using prepared statements, which means :.
• Printing raw SQL string formed by Query Builder | IMHO, the best way to understand Query builder logic is to print formed query string (but not it's result). ... PHP, Laravel, Vue, and much more. Get ...
• Writing Migrations - 0.16 | Sep 7, 2024 ... It is preferred that you write your migrations using the Phinx PHP API, but raw SQL is also supported. ... query builder is done by calling the ...
• Stop using Knex.js. Using SQL query builder is an… | by Gajus ... | Using SQL query builder is an anti-pattern