Facebook Messenger API换行JSON错误：MySQL存储异常修复求助

本人对JSON略有了解，搭建了集成Facebook Messenger API的系统，通过Webhook将数据存入MySQL数据库以便后续处理。正常运行无问题，但用户发送带换行（SHIFT+ENTER）的消息时出现异常。尝试过直接存储$input内容、使用nl2br均无效。以下是相关代码、错误返回示例及预期格式，请求解决方法：

相关代码（My_Webhook_File.php） #

$input = file_get_contents('php://input');
$data = json_decode($input, true);
$sender = $data['entry'][0]['messaging'][0]['sender']['id'];
$message = $data['entry'][0]['messaging'][0]['message']['text'];
$message_id = $data['entry'][0]['messaging'][0]['message']['mid'];
$recipient = $data['entry'][0]['messaging'][0]['recipient']['id'];

if(!empty($sender)){
     $db = db("INSERT INTO FB (senderid, sender_name, recipient, the_time, data, is_read) VALUES ('".numbersonly($sender)."', '".$fbname."', '".$recipient."', NOW(), '".json_encode($data)."', '".$is_read."')");
}

错误返回示例 #

{"object":"page","entry":[{"id":"MY_PAGE_ID","time":1672599591761,"messaging":[{"sender":{"id":"SENDER_ID"},"recipient":{"id":"RECIPIENT_ID"},"timestamp":1672599591554,"message":{"mid":"MESSAGE_ID","text":"maybe
this
will

work"}}],"hop_context":[{"app_id":MY_APP_ID,"metadata":""}]}]}

预期正确格式 #

{"object":"page","entry":[{"id":"MY_PAGE_ID","time":1672599591761,"messaging":[{"sender":{"id":"SENDER_ID"},"recipient":{"id":"RECIPIENT_ID"},"timestamp":1672599591554,"message":{"mid":"MESSAGE_ID","text":"maybe\nthis\nwill\nwork"}}],"hop_context":[{"app_id":MY_APP_ID,"metadata":""}]}]}

问题根源 #

用户发送带换行的消息时，原始JSON的text字段包含实际换行符，这些换行符未被正确转义，导致存储或后续处理时JSON结构异常。

解决方法 #

1. 预处理输入，转义换行符
   在解码JSON前，先将输入中的所有换行符替换为\n，确保JSON格式合法：

   $input = file_get_contents('php://input');
   // 替换各种换行符为转义后的\n
   $input = str_replace(["\r\n", "\n", "\r"], "\\n", $input);
   $data = json_decode($input, true);
   
   $sender = $data['entry'][0]['messaging'][0]['sender']['id'];
   $message = $data['entry'][0]['messaging'][0]['message']['text'];
   $message_id = $data['entry'][0]['messaging'][0]['message']['mid'];
   $recipient = $data['entry'][0]['messaging'][0]['recipient']['id'];
   
   if(!empty($sender)){
        $db = db("INSERT INTO FB (senderid, sender_name, recipient, the_time, data, is_read) VALUES ('".numbersonly($sender)."', '".$fbname."', '".$recipient."', NOW(), '".json_encode($data)."', '".$is_read."')");
   }
   

   也可以单独提取message字段替换换行符后，再更新回$data数组，效果一致。

2. 修复SQL注入漏洞
   当前代码直接拼接SQL语句存在严重安全风险，建议改用预编译语句：

   if(!empty($sender)){
        // 假设$pdo是已初始化的PDO连接
        $stmt = $pdo->prepare("INSERT INTO FB (senderid, sender_name, recipient, the_time, data, is_read) VALUES (?, ?, ?, NOW(), ?, ?)");
        $stmt->execute([numbersonly($sender), $fbname, $recipient, json_encode($data), $is_read]);
   }
   

内容的提问来源于stack exchange，提问作者EagleTalonTim