含双引号的MySQL数据本地PHP正常，线上PHP无法返回问题

我本地有一个MySQL数据库，包含若干表及数据，已成功将数据导出并导入到线上网站的MySQL数据库。为每个表编写了对应的PHP数据查询文件，本地服务器上所有文件均可正常获取数据，但部署到线上服务器后，仅quotes表的查询文件无法返回数据，且无报错信息。手动删除该表所有数据后插入不含双引号的条目时，偶尔可正常返回。

经排查发现问题出在quote字段：当该字段值包含双引号时，线上PHP无法获取数据；移除双引号后则恢复正常。

数据库表结构 #

CREATE TABLE quotes (
    id int(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
    quote TEXT NOT NULL, -- 我认为这是有问题的列
    created DATETIME NOT NULL,
    character_id int(11) NOT NULL,
    word TEXT NOT NULL,
    approved TINYINT(1) NOT NULL,
    source TEXT NULL,
    FOREIGN KEY (character_id) REFERENCES characters (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

插入SQL示例 #

含双引号的条目（本地PHP可正常获取，线上PHP无法获取） #

INSERT INTO quotes (quote, created, character_id, word, approved, source) VALUES ('“Oh, speaking of a giant flying bird, there\'s one right there!”', NOW(), 1, "bird", 1, "Super Monkey Ball Gameplay")

不含双引号的条目（线上PHP可正常获取） #

INSERT INTO quotes (quote, created, character_id, word, approved, source) VALUES ('Hi', NOW(), 1, "Hi", 1, "Super Monkey Ball Gameplay")

查询用PHP代码 #

<?php

include 'dbh.inc.php'; // 数据库连接文件

if ($_SERVER['REQUEST_METHOD'] == 'GET') {

$q = "SELECT * FROM quotes WHERE character_id = ? AND approved = 1";
$stmt = mysqli_prepare($conn, $q);
mysqli_stmt_bind_param($stmt, 'i', $characterId);

$characterId = $_GET['characterId'];

mysqli_stmt_execute($stmt);

$r = mysqli_stmt_get_result($stmt);

if (mysqli_num_rows($r) != 0) {

    while ($row = mysqli_fetch_array($r)) {

        $data['quoteId'] = $row['id'];
        $data['quote'] = $row['quote'];
        $data['character_id'] = $row['character_id'];
        $data['word'] = $row['word'];
        $data['approved'] = $row['approved'];
        $data['source'] = $row['source'];
        $results[] = $data;

    }

    echo json_encode($results);

} else {

    echo mysqli_stmt_error($stmt);

}

}

已尝试的操作 #

• 查看sql_mode，结果为空；尝试设置global sql_mode=''但因无SUPER权限失败

疑问 #

为什么本地PHP可正常返回含双引号的数据，线上PHP却不行？是否需修改线上数据库的某项设置？

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