php - 当语句在 MySQL 中正常工作时如何修复 PHP 中的语法错误？

Question

我正在使用 PHP/MySQL 来存储从网页收集的数据。我得到了

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO narrative_photos VALUES (`filename`, `narrative_id`) VALUES ('ash_02

当我将 PHP 生成的语句粘贴到 MySQL 控制台时，该语句工作正常。

这是 PHP 代码:

foreach ($files['pictures']['final_name'] as $key => $final_name) {
    $sql .= "INSERT INTO narrative_photos ";
    $sql .= "(`filename`, `narrative_id`) ";
    $sql .= "VALUES (";
    $sql .= "'" . db_escape($db, $final_name) . "', ";
    $sql .= "'LAST_INSERT_ID()'); ";
}

它产生的东西看起来像这样:

INSERT INTO narrative_photos VALUES (`filename`, `narrative_id`) VALUES ('ash_020819-140257.png', 3);

如果我将其粘贴到 MySQL 中，它就可以工作。但如果我注释掉 PHP 代码并替换为:

 $sql .= "INSERT INTO narrative_photos VALUES (`filename`, `narrative_id`) VALUES ('ash_020819-140257.png', 3);";

它继续抛出 MySQL 错误。

我已经玩了几个小时了，但我不知道我的错误在哪里。我希望有第二双眼睛。谢谢!

编辑:这是上下文的完整函数。

function insert_narrative($narrative, $files) {
    global $db;

    $sql = "INSERT INTO narratives ";
    $sql .= "(date, positive_thing, what_you_did, goals, plan, entered_by, library_id) ";
    $sql .= "VALUES (";
    $sql .= "'" . db_escape($db, $narrative['sqldate']) . "', ";
    $sql .= "'" . db_escape($db, $narrative['positive_thing']) . "', ";
    $sql .= "'" . db_escape($db, $narrative['what_you_did']) . "', ";
    $sql .= "'" . db_escape($db, $narrative['goals']) . "', ";
    $sql .= "'" . db_escape($db, $narrative['plan']) . "', ";
    $sql .= "'" . db_escape($db, $narrative['entered_by']) . "', ";
    $sql .= "'" . db_escape($db, $_SESSION['library_id']) . "'";
    $sql .= "); ";

    if (!empty($files['pictures']['final_name'])) {
        foreach ($files['pictures']['final_name'] as $key => $final_name) {
            $sql .= "INSERT INTO narrative_photos ";
            $sql .= "(`filename`, `narrative_id`) ";
            $sql .= "VALUES (";
            $sql .= "'" . db_escape($db, $final_name) . "', ";
            $sql .= "LAST_INSERT_ID()); ";

        }
    }
    $result = mysqli_query($db, $sql);
    if ($result) {
        return true;
    } else {
        echo mysqli_error($db);
        db_disconnect($db);
        exit;
    }
}

编辑#2:我刚刚意识到，与语法错误无关，我的方法不会起作用，因为 LAST_INSERT_ID 可能会获取每个插入的 id，而不是仅使用主表中的 id。我已经修改了该函数，但在 SET @narrative_id 处仍然出现语法错误。这是代码。

$sql = "INSERT INTO narratives ";
$sql .= "(date, positive_thing, what_you_did, goals, plan, entered_by, library_id) ";
$sql .= "VALUES (";
$sql .= "'" . db_escape($db, $narrative['sqldate']) . "', ";
$sql .= "'" . db_escape($db, $narrative['positive_thing']) . "', ";
$sql .= "'" . db_escape($db, $narrative['what_you_did']) . "', ";
$sql .= "'" . db_escape($db, $narrative['goals']) . "', ";
$sql .= "'" . db_escape($db, $narrative['plan']) . "', ";
$sql .= "'" . db_escape($db, $narrative['entered_by']) . "', ";
$sql .= "'" . db_escape($db, $_SESSION['library_id']) . "'";
$sql .= "); ";
$sql .= "SET @narrative_id = LAST_INSERT_ID()";

if (!empty($files['pictures']['final_name'])) {
    foreach ($files['pictures']['final_name'] as $key => $final_name) {
        $sql .= "INSERT INTO narrative_photos ";
        $sql .= "(`filename`, `narrative_id`) ";
        $sql .= "VALUES (";
        $sql .= "'" . db_escape($db, $final_name) . "', ";
        $sql .= "@narrative_id); ";

    }
}

Answer 1

如果您尝试进行一个查询来插入所有值，则无需每次都包含查询的 INSERT 部分，而只需添加一组新值即可。你可以使用这样的东西。请注意，LAST_INSERT_ID() 不应括在引号中，因为这将插入文字字符串 "LAST_INSERT_ID()" 而不是值。

$sql  = "INSERT INTO narrative_photos (`filename`, `narrative_id`) VALUES ";
$values = array();
foreach ($files['pictures']['final_name'] as $final_name) {
    $values[] = "('" . db_escape($db, $final_name) . "', LAST_INSERT_ID())";
}
$sql .= implode(', ', $values);

注释

我假设您实际上希望所有这些文件名在 narrative_id 中以相同的值结束，该值将链接回另一个表。

虽然从表面上看这些值已经被过滤了(我认为它们是实际的系统文件名)，但代码仍然可能容易受到 SQL 注入(inject)的攻击。 This question和 this question关于如何使用带有参数数组的准备好的语句提供一些好的建议。