Binding issue with ? operator using join->on with Postgres

[elsconfairmed]

Laravel Version

11.45.2

PHP Version

8.3

Database Driver & Version

psql (PostgreSQL) 14.10 (Homebrew)

Description

I got the same Problem described in this Issue when I try to use the ? with joins.

There error is SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "$1"...

It looks like the ? does not get escape with ??

After some digging i found that for basic where('column', '?', $key) this was fixed in Illuminate\Database\Query\Grammars\Grammer::295

Possible workarounds that I found were to use the customOperators() function to add the escaped operators or to write raw queries every time you deal with json columns

Steps To Reproduce

Model::leftJoin('table_2', fn($join) => $join->on(DB::raw("model_table.jsonb->'ids'"),'?','table_2.id'))->get();

The raw query is needed so that the ' not get replaced with '' and break the query

[github-actions]

Thank you for reporting this issue!

As Laravel is an open source project, we rely on the community to help us diagnose and fix issues as it is not possible to research and fix every issue reported to us via GitHub.

If possible, please make a pull request fixing the issue you have described, along with corresponding tests. All pull requests are promptly reviewed by the Laravel team.

Thank you!

[epcgrs]

I’ll take a look at this.

[epcgrs]

Hmm, I created a dedicated test in my local fork of laravel/framework to isolate the bug. The test checks if the Query Builder preserves the ? operator in a PostgreSQL join using JSONB, and then actually runs the query against a real database.

<?php
public function test_join_on_jsonb_key_operator_is_preserved()
{
    $q = DB::table('a_items as a')->leftJoin('b_refs as b', function ($join) {
        $join->on(new Expression("(a.attrs->'ids')"), '?', new Expression('b.id::text'));
    })->select('a.id','b.id as bid');

    $sql = $q->toSql();
    $this->assertStringContainsString("(a.attrs->'ids') ? b.id::text", $sql);
}

public function test_query_runs_in_database()
{
    $result = null;
    try {
        $result = DB::table('a_items as a')->leftJoin('b_refs as b', function ($join) {
            $join->on(new Expression("(a.attrs->'ids')"), '?', new Expression('b.id::text'));
        })->select('a.id','b.id as bid')->get();
    } catch (\Exception $e) {
        $this->fail('Database error: ' . $e->getMessage());
    }
    $this->assertIsIterable($result);
}

Result:

• The first test passes, confirming the SQL is generated as expected.
• The second test fails with a PostgreSQL syntax error: the ? is treated as a parameter placeholder, not as the intended operator.

This demonstrates the issue is not with the query itself, but with how Laravel (or PDO) compiles and binds the SQL when using custom operators in joins.

[epcgrs]

For now, you can use the following workaround:

#22472

I just ran into this issue when trying to use the ?| operator, and it works as of PHP 7.4:

See:

https://wiki.php.net/rfc/pdo_escape_placeholders

https://stackoverflow.com/questions/36173440/how-to-ignore-question-mark-as-placeholder-when-using-pdo-with-postgresql

You can escape the question mark by using double question marks ?? in your raw SQL, or use a custom approach to avoid PDO treating it as a placeholder. This allows you to use operators like ?| in PostgreSQL without triggering parameter binding errors.