A Dive Into toRawSql()

In the recent past, we were able to dump out the SQL our query builder was generating like so:

$filter = 'wew, dogs';

// Using the `toSql()` helper
DB::table('foo')
  ->select(['id', 'col1', 'col2'])
  ->join('bar', 'foo.bar_id', 'bar.id')
  ->where('foo.some_colum', $filter)
  ->toSql();

// SELECT id, col1, cole2
//     FROM foo
//     INNER JOIN nar on foo.bar_id = bar.id
//     WHERE foo.some_column = ?

This was useful for debugging complicated queries, but note how we didn’t get the value in our WHERE statement!
All we got was a pesky ? – a placeholder for whatever value we’re passing into the query.
The actual value is hidden from us.

“That’s not what I need”, you may have asked yourself. Assuming we aren’t debugging SQL syntax, our query bindings are what we likely care about the most.

Getting the Full Query

New to Laravel 10.15 is the ability to get the full sql query! That’s much more useful for debugging.

$filter = 'wew, dogs';

// Using the `toRawSql()` helper
DB::table('foo')
  ->select(['id', 'col1', 'col2'])
  ->join('bar', 'foo.bar_id', 'bar.id')
  ->where('foo.some_colum', $filter)
  ->toRawSql();

// SELECT "id", "col1", "col2"
//     FROM "foo" 
//     INNER JOIN "bar" ON "foo"."bar_id" = "bar"."id"
//     WHERE "foo"."some_colum" = 'wew, dogs'"

Much better!

The trick to this is that PDO (the core library used to connect to databases) doesn’t just give this to us – we can only get the SQL with the binding placeholders (hence the old toSql() limitation).

So, we need to build the query with our values within the query ourselves! How’s that done?

How It’s Done

This is tricky business, as we’re dealing with user input – any crazy thing a developer (or their users)
might throw into a sql query needs to be properly escaped.

The new toRawSql() helper stuffs the important logic into a method named substituteBindingsIntoRawSql(). Here’s the PR, for reference.

If we dig into that method code a bit, we’ll
see what’s going on!

The first thing the function does is escape all of the values. This lets Laravel print out the query as a string without worrying about mis-aligned quotes or similar issues.

$bindings = array_map(fn ($value) => $this->escape($value), $bindings);

The call to $this->escape() goes down to a database connection object, and deeper into the underlying PDO object. PDO does the work of
actually escaping the query values in a safe way.

Following the escaping-of-values, the method goes through the query character by character!

for ($i = 0; $i < strlen($sql); $i++) {
    $char = $sql[$i];
    $nextChar = $sql[$i + 1] ?? null;

    // and so on
}

The major supported databases use different conventions for escape characters. The code here attempts to find escaped characters and ignore them, lest it tries to
substitute something that looks like a query binding character but isn’t. This is the most fraught bit of code in this new feature.

$query = '';


for ($i = 0; $i < strlen($sql); $i++) {
    $char = $sql[$i];
    $nextChar = $sql[$i + 1] ?? null;

    // Single quotes can be escaped as '' according to the SQL standard while
    // MySQL uses \'. Postgres has operators like ?| that must get encoded
    // in PHP like ??|. We should skip over the escaped characters here.
    if (in_array($char.$nextChar, ["\'", "''", '??'])) {
        // We are building the query string back up - We ignore escaped characters
        // and append them to our rebuilt query string. Since we append
        // two characters, we `$i += 1` so the loop skips $nextChar in our `for` loop
        $query .= $char.$nextChar;
        $i += 1;
    } ...
}

The for loop is rebuilding the query string, but with values substituted in for their ? placeholders. The first check here
is looking for certain escape characters. It needs to know the current character AND the next one to know if it’s an escaped character
and therefore should not do any substitutions.

The next part of our conditional is this:

} elseif ($char === "'") { // Starting / leaving string literal...
    $query .= $char;
    $isStringLiteral = ! $isStringLiteral;
}

If we’re opening an unescaped quote, it means we’re at the start (or end) of a string literal. We set a flag for this case, which
is important in our next check.

elseif ($char === '?' && ! $isStringLiteral) { // Substitutable binding...
    $query .= array_shift($bindings) ?? '?';
}

Here’s the magic. If we’re NOT inside of a string literal, AND we’re not finding an escaped character, AND we find a ? character,
then we can assume it’s a query binding to be substituted with the actual value. We take our array of values and shift it – we remove the
first item in that array and append its value to our query string. (The array of values are in the same order of query binding characters – ? – in the query).

Finally, if we just have a regular character that doesn’t have special meaning, we just append it to our query string:

else { // Normal character...
    $query .= $char;
}

Here’s the whole method, as it stands as I write this:

public function substituteBindingsIntoRawSql($sql, $bindings)
{
    $bindings = array_map(fn ($value) => $this->escape($value), $bindings);

    $query = '';

    $isStringLiteral = false;

    for ($i = 0; $i < strlen($sql); $i++) {
        $char = $sql[$i];
        $nextChar = $sql[$i + 1] ?? null;

        // Single quotes can be escaped as '' according to the SQL standard while
        // MySQL uses \'. Postgres has operators like ?| that must get encoded
        // in PHP like ??|. We should skip over the escaped characters here.
        if (in_array($char.$nextChar, ["\'", "''", '??'])) {
            $query .= $char.$nextChar;
            $i += 1;
        } elseif ($char === "'") { // Starting / leaving string literal...
            $query .= $char;
            $isStringLiteral = ! $isStringLiteral;
        } elseif ($char === '?' && ! $isStringLiteral) { // Substitutable binding...
            $query .= array_shift($bindings) ?? '?';
        } else { // Normal character...
            $query .= $char;
        }
    }

    return $query;
}

That’s basically all there is to the story. We want the entire query to be available with our values!
Here are some (light) caveats we have to get this feature:

1. We need to make a database connection (thanks to using the PDO library for safe escaping)
2. The above code MAY contain the occasional bug depending on what values are used
3. Very long (e.g. binary) values in a query would likely return a complete mess of a query string

Those trade-offs seem fine to me!