Why can't use placeholder inside JSON (RedBeanPHP)?

Question

PostgreSQL

Hello! I have a task: to check if "$username" has upvoted a record with id "$id". For this I wrote the following (PostgreSQL DB):

$foo = R::getRow('SELECT "votes"::jsonb @> \'{"upvote":[":username"]}\'::jsonb AS "is_upvoted" FROM "pages" WHERE "id" = :id', [":id"=>$id,":username"=>$username];

The problem is that I get error HY093 (googling says this error occurs when placeholders fail)

Then I tried to escape the quotes inside the JSON string, so that it turned out:
\'{\"upvote"\:[\":username\"]}\'
The result is the same.

After that, I tried to remove the quotes around ":username" altogether, so that it was:
\'{"upvote"\:[:username]}\'
But I get the same error.

But, when I don't use the placeholder in place ":username", i.e. when the code looks like this:

$foo = R::getRow('SELECT "votes"::jsonb @> \'{"upvote":["'.$username.'"]}\'::jsonb AS "is_upvoted" FROM "pages" WHERE "id" = :id', [":id"=>$id];

The operation is successful and I get my result. Of course, now I use my own, checked variable, but then I want to use a similar query for a custom search, and I understand that this is not safe, so I want to ask for help here: what is the reason and how can this be solved?

Answer 1

try '' (double single) instead of \'