Token "'" is invalid - PHP PDO PostgreSQL

Question

When executing a prepared statement and inserting a jsonb value like '{}'::jsonb via a bound parameter, I get this error:

SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type json
DETAIL:  Token "'" is invalid.
CONTEXT:  JSON data, line 1: '...

I use the single quote ' to describe a string literal, which I then convert to jsonb type via 'string literal'::jsonb. But with prepared statements, it does not work and the abovementioned error is thrown. What am I doing wrong?

The string itself does not have any single quotes in it. The only single quotes are the ones used to wrap the string.

The string itself is the result of encoding a PHP array with using:

json_encode($array, JSON_HEX_AMP | JSON_HEX_APOS | JSON_HEX_TAG)

<?php

$stm = $pdo->prepare("INSERT INTO t (val) VALUES (:val)");
$stm->execute($stm, [':val' => "'{}'::jsonb"]);

Answer 1

This worked for me with PHP, PDO, and PostgreSQL with a column of type jsonb:

$obj = ["foo" => "bar"]; // create a typical PHP hash map array
$stringified = json_encode($obj); // becomes the string {"foo": "bar"}
$pdo->prepare("INSERT INTO orders (val) VALUES (:val)")->execute([':val' => $stringified]);

Later on I can either select the whole field:

SELECT val FROM orders;
// The field val will just be the full JSON string,
// and I'll need to run it through json_decode($field, true) in PHP land.

But I could also use PostgreSQL's JSON parsing features to pull out specific things:

SELECT val->>'foo' as fooey FROM orders;
// Dumping out $data['fooey'] will be just "bar" without the quotes
// No conversion needed in PHP