SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type json DETAIL: Token "06061c" is invalid

Question

I'm using a SQL clause in a PostgreSQL database with PDO. I have a function that generates a verification code and another that updates the user's code. However, there is an issue in this function that updates the code. It identifies it as a token when the code contains letters. If it's only numbers or only letters, it updates perfectly. I've read in some posts that it's necessary to enclose my field with quotes, but it doesn't work with parameters.

public function upCode(Usuario $user){
    try {
        var_dump($user->getCode());
        $sql = "UPDATE usuarios
        SET usuario = jsonb_set(usuario, '{code}', :code, false)
        WHERE usuario->>'email' = :email";
        $stmt = (parent::getCon())->prepare($sql);
        $stmt->bindValue("email", $user->getEmail());
        $stmt->bindValue("code", $user->getCode(), PDO::PARAM_STR);
        $stmt->execute();

    }catch (PDOException $e) {
    echo "". $e->getMessage();
    var_dump($e);
    }
}

public function gerarCode(){
        $this->code = bin2hex(random_bytes(3));
    }

Error:

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

But if I try this way, it works.

$stmt->bindValue("code", "456478", PDO::PARAM_STR);

Answer 1

You can use the function to_jsonb() to create valid jsonb for the new value:

UPDATE usuarios
SET usuario = jsonb_set(usuario, '{code}', to_jsonb(CAST(:code AS text)), false)
WHERE usuario->>'email' = :email;

Also CAST the content to TEXT, to be very explicit to the database about how to handle the input.