PostgreSQL+PDO使用jsonb_set更新混合字符验证码报错求助

问题现象 #

在PostgreSQL中用PDO编写更新用户验证码的upCode函数时，出现以下异常：

• 验证码为纯数字或纯字母时，更新操作正常执行
• 验证码为字母数字混合内容时，直接触发JSON语法错误，报错信息如下：

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 = '...'

相关代码 #

生成验证码的函数：

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

更新验证码的原函数：

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);
    }
}

问题原因 #

核心问题出在jsonb_set的参数规则上：它的第三个参数要求传入合法的JSON值。

• 纯数字字符串会被PostgreSQL自动解析为JSON数字类型，因此能正常执行
• 纯字母字符串偶尔会被隐式识别为JSON标识符（属于不规范场景）
• 字母数字混合的字符串既不是合法JSON数字，也不是带引号的JSON字符串，直接触发JSON解析失败

解决办法 #

推荐使用第一种方式，更安全省心：

方式一：用PostgreSQL的to_json函数转换参数

在SQL语句中用to_json()将传入的字符串自动转换为合法JSON值，它会处理引号添加和特殊字符转义：

public function upCode(Usuario $user){
    try {
        var_dump($user->getCode());
        // 给:code套上to_json()转换为JSON格式
        $sql = "UPDATE usuarios
        SET usuario = jsonb_set(usuario, '{code}', to_json(: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);
    }
}

方式二：手动给参数添加JSON字符串引号

绑定参数时，给验证码前后加上双引号，让它成为合法的JSON字符串格式：

// 替换原绑定code的代码行
$stmt->bindValue("code", '"' . $user->getCode() . '"', PDO::PARAM_STR);

注意：如果验证码可能包含双引号等特殊字符，需要自行处理转义，不如方式一可靠。

内容的提问来源于stack exchange，提问作者Ronaldo Ávila de Arruda Junior