DB binding is wailing or broken with some PG operators

[DeBug88]

PHP 8.3 Clockwork 5.3.* DB: PostgreSQL 14.3 (any with json operations)

Prepare:

CREATE TABLE temp (
    id INT PRIMARY KEY,
    json jsonb NOT NULL DEFAULT '{}'
);

INSERT INTO temp(id, json) VALUES (1, '{"a": 5}'), (2, '{"a": 1, "b": 3}');

Case 1

SELECT id, json ?? 'a' AS has_a
FROM temp t
WHERE id = ?

Gives us wrong binding results

Case 2

SELECT id, json ?? 'a' AS has_a
FROM temp t
WHERE id = :id

Cause Fatal error "message":"Undefined array key 1", "file":"/app/vendor/itsgoingd/clockwork/Clockwork/DataSource/EloquentDataSource.php:274"

Issue is in following preg pattern $query = preg_replace_callback('/\?/', function ($matches) use ($bindings, $connection, &$index) {

it's not working with json operations like in PG: https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-PROCESSING see Table 9.46. Additional jsonb Operators

jsonb ? text → boolean jsonb @? jsonpath → boolean

See https://jdbc.postgresql.org/documentation/query/#using-the-statement-or-preparedstatement-interface

[DeBug88]

I build the followin binding using laravel core functionality to bind values when debug queries

$bindings = $this->databaseManager->connection($connection)->prepareBindings($bindings);
$query = $this->databaseManager->connection($connection)->getQueryGrammar()->substituteBindingsIntoRawSql($query, $bindings);
foreach ($bindings as $key => $value) {
    // index bindings already done in substitue binding before
    if (str_starts_with($key, ':')) {
        $query = str_replace($key, $value, $query);
    }
}

but i'm unsure about everything inside closure

$query = preg_replace_callback('/\?/', function ($matches) use ($bindings, $connection, &$index) {
	$binding = $this->quoteBinding($bindings[$index++], $connection);

	// convert binary bindings to hexadecimal representation
	if (! preg_match('//u', (string) $binding)) $binding = '0x' . bin2hex($binding);

	// escape backslashes in the binding (preg_replace requires to do so)
	return (string) $binding;
}, $query, count($bindings));

[itsgoingd]

Hey, thanks for the report. Another issue in our implementation is an improper handling of ? characters inside 'quoted strings'. I think we can fix our regex to fix these cases, or we could just use Laravel's implementation.

[itsgoingd]

I've improved the query generation code in master (https://github.com/itsgoingd/clockwork/commit/29095a574d918e761c4b217d51a85e830a969c43), can you please try if this solves your issue?

[itsgoingd]

Fixed in Clockwork 5.3.5.