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"]);

possible duplicate of https://stackoverflow.com/questions/36038454/parsing-string-as-json-with-single-quotes — gogaz, Sep 17 '19 at 09:02
It was intentionally that you didn't add any code to your question? — Your Common Sense, Sep 17 '19 at 09:05
@gogaz the string I'm wrapping with single-quotes does not have any single quotes in it. So does not look like a duplicate question. — Meglio, Sep 17 '19 at 09:14
@YourCommonSense just standard PHP PDO prepare and then execute. — Meglio, Sep 17 '19 at 09:15
Please post your code together with some sample data that causes your issue. Have a look at [How to create a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) and edit your question accordingly. — M. Eriksson, Sep 17 '19 at 09:15
Shouldn't it be enough to just json_encode() the value and add it "as is"? The problem with your current code is that you're trying to store the actual string `'{}'::jsonb` in a json field. When you're using prepared statements, functions etc in the values won't be executed. — M. Eriksson, Sep 17 '19 at 09:29
thank you for the code. Try to make it `$pdo->prepare("INSERT INTO t (val) VALUES (:val::jsonb)")->execute([':val' => "'{}'"]);` I bet it wouldn't work but just in case? — Your Common Sense, Sep 17 '19 at 09:34

score 0 · Answer 1 · answered Mar 06 '22 at 20:27

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

Token "'" is invalid - PHP PDO PostgreSQL

1 Answers1