首次使用Ajax开发聊天功能遇SQL语法错误及HTML显示undefined问题

API 探路者

2026-7-4

聊天功能Ajax开发问题：页面显示undefined + SQL语法错误

问题现象：首次使用Ajax开发聊天功能，尝试通过Ajax向HTML页面添加收发消息元素，但页面仅显示undefined文本。messageInsert.php及对应Ajax请求正常运行，但get_msg.php出现SQL语法错误，错误信息如下：

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND incoming_id = ) OR (outgoing_id = AND incoming_id = ) ORDER BY msg_id DESC' at line 1 in C:\xampp\htdocs\project\myProjects\Chatbot\php\get_msg.php:9 Stack trace: #0 C:\xampp\htdocs\project\myProjects\Chatbot\php\get_msg.php(9): mysqli->query('SELECT * FROM m...') #1 {main} thrown in C:\xampp\htdocs\project\myProjects\Chatbot\php\get_msg.php on line 9

相关代码

messageInsert.php

<?php
    session_start();
    require_once 'connection.php';
    if (isset($_SESSION['userID'])) {
        $outgoing_id = mysqli_real_escape_string(connection() , $_POST['outgoing_id']);
        $incoming_id = mysqli_real_escape_string(connection() , $_POST['incoming_id']);
        $message = mysqli_real_escape_string(connection() , $_POST['message']);
        if (!empty($message)) {
            $query = "INSERT INTO messages (outgoing_id, incoming_id, msg) VALUES ($outgoing_id , $incoming_id , '$message')";
            $result =connection()->query($query);
        }
    }

get_msg.php

<?php
    session_start();
    require_once 'connection.php';
    if (isset($_SESSION['userID'])) {
        $outgoing_id = mysqli_real_escape_string(connection() , $_POST['outgoing_id']);
        $incoming_id = mysqli_real_escape_string(connection() , $_POST['incoming_id']);
        $output = "";
        $query = "SELECT * FROM messages WHERE (outgoing_id = {$outgoing_id} AND incoming_id = {$incoming_id}) OR (outgoing_id = {$incoming_id} AND incoming_id = {$outgoing_id}) ORDER BY msg_id DESC";
        $result = connection()->query($query);
        while ($row = mysqli_fetch_assoc($result)) {
            if ($outgoing_id === $row['outgoing_id']) {
                $output ='<div class="message-send my-4">
                               <p>'.$row['msg'].'</p>
                           </div>';
            } else {
                $output = '<div class="message-receive">
                                <img class="message-receive-pic mb-3 me-2" src="style/ProfilePic/'.$_SESSION['userPic'].'" alt="">
                                <div class="message-receive-text-box my-4">
                                    <p> '. $row['msg']  .'</p>
                                </div>
                           </div>';
            }
            echo $output;
        }
    }

Ajax代码

let form = document.querySelector(".mySend"),
    sendInput = form.querySelector(".message"),
    sendBtn = form.querySelector(".sendBtn"),
    chatBox = document.querySelector(".chat-box");
form.onsubmit = (e)=> {
    e.preventDefault();
}
sendBtn.onclick = ()=> {
    let xhr = new XMLHttpRequest();
    xhr.open("POST" , "http://localhost/project/myProjects/Chatbot/php/messageInsert.php" , true);
    xhr.onreadystatechange = ()=> {
        if (xhr.readyState === XMLHttpRequest.DONE) {
            if (xhr.status === 200){
                sendInput.value = "";
            }
        }
    }
    let formData = new FormData(form);
    xhr.send(formData);
}
setInterval(()=> {
    let xhr = new XMLHttpRequest();
    let data = "";
    xhr.open("POST" , "php/get_msg.php" , true);
    xhr.onload = ()=> {
        if (xhr.readyState === XMLHttpRequest.DONE) {
            if (xhr.status === 200) {
                data = this.response;
                chatBox.innerHTML = data;
            }
        }
    }
    let formData = new FormData(form);
    xhr.send(formData);
}, 500);

HTML代码

<div class="chat-box">

    </div>
    <div class="type-box">
        <form class="mySend" method="post" autocomplete="off">
            <input type="hidden" value="<?php echo $userProfile['id'] ?>" name="incoming_id">
            <input type="hidden" value="<?php echo $_SESSION["profileID"] ?>" name="outgoing_id">
            <input class="message form-control" type="text" placeholder="Type your message here..." name="message">
            <button type="submit" class=" sendBtn btn btn-dark" name="sendBtn"><i class="fab fa-telegram-plane"></i></button>
        </form>
    </div>

问题分析与修复方案

1. 页面显示undefined的原因及修复

Ajax定时请求的onload使用了箭头函数，this不指向XMLHttpRequest对象，导致this.response为undefined。

修复代码：
将Ajax中data = this.response;改为data = xhr.response;：

xhr.onload = ()=> {
    if (xhr.readyState === XMLHttpRequest.DONE) {
        if (xhr.status === 200) {
            data = xhr.response; // 此处修改
            chatBox.innerHTML = data;
        }
    }
}

2. SQL语法错误的原因及修复

错误信息显示SQL语句中outgoing_id = 和incoming_id = 后无值，说明$_POST['outgoing_id']或$_POST['incoming_id']为空，同时字符串拼接SQL存在注入风险。

修复步骤：

增加参数存在性校验，避免空值进入SQL；
使用预处理语句替代字符串拼接，同时解决语法错误和注入问题；
修复消息拼接逻辑，当前代码每次循环覆盖$output，导致仅输出最后一条消息，改为拼接模式。

修改后的get_msg.php：

<?php
session_start();
require_once 'connection.php';
// 同时校验session和POST参数
if (isset($_SESSION['userID'], $_POST['outgoing_id'], $_POST['incoming_id'])) {
    $outgoing_id = $_POST['outgoing_id'];
    $incoming_id = $_POST['incoming_id'];
    $output = "";
    
    // 使用预处理语句
    $query = "SELECT * FROM messages 
              WHERE (outgoing_id = ? AND incoming_id = ?) 
                 OR (outgoing_id = ? AND incoming_id = ?) 
              ORDER BY msg_id DESC";
    $stmt = connection()->prepare($query);
    // 绑定参数，i表示整数类型（根据字段类型调整）
    $stmt->bind_param("iiii", $outgoing_id, $incoming_id, $incoming_id, $outgoing_id);
    $stmt->execute();
    $result = $stmt->get_result();
    
    while ($row = mysqli_fetch_assoc($result)) {
        if ((string)$outgoing_id === (string)$row['outgoing_id']) { // 统一类型避免判断错误
            $output .= '<div class="message-send my-4">
                           <p>'.$row['msg'].'</p>
                       </div>';
        } else {
            $output .= '<div class="message-receive">
                            <img class="message-receive-pic mb-3 me-2" src="style/ProfilePic/'.$_SESSION['userPic'].'" alt="">
                            <div class="message-receive-text-box my-4">
                                <p> '. $row['msg']  .'</p>
                            </div>
                       </div>';
        }
    }
    echo $output ?: '<p class="text-center">暂无消息</p>'; // 无消息时提示
}

3. 额外检查点

确认HTML中$_SESSION["profileID"]和$userProfile['id']有有效值，可通过var_dump($_SESSION)和var_dump($userProfile)输出验证；
确认connection.php中的数据库连接函数connection()返回有效的mysqli对象；
messageInsert.php建议改为预处理语句，避免SQL注入风险：

<?php
session_start();
require_once 'connection.php';
if (isset($_SESSION['userID'], $_POST['outgoing_id'], $_POST['incoming_id'], $_POST['message'])) {
    $outgoing_id = $_POST['outgoing_id'];
    $incoming_id = $_POST['incoming_id'];
    $message = $_POST['message'];
    
    if (!empty(trim($message))) {
        $query = "INSERT INTO messages (outgoing_id, incoming_id, msg) VALUES (?, ?, ?)";
        $stmt = connection()->prepare($query);
        $stmt->bind_param("iis", $outgoing_id, $incoming_id, $message);
        $stmt->execute();
    }
}

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

首次使用Ajax开发聊天功能遇SQL语法错误及HTML显示undefined问题

相关代码 #

messageInsert.php #

get_msg.php #

Ajax代码 #

HTML代码 #

问题分析与修复方案 #

1. 页面显示undefined的原因及修复 #

2. SQL语法错误的原因及修复 #

3. 额外检查点 #