php - 删除行时 SQL 语法错误

Question

好的，我基本上有一个 HTML 表单，其中包含一个隐藏输入和一个提交按钮。当按下按钮时，它将删除我的 MySQL 表中的特定行。这些代码实际上都完成了它应该做的功能。但是，当我运行它时，我不断显示语法错误。一旦我收到错误，如果我返回，该行就消失了，这就是我想要的。我只是不确定如何使其在运行后重定向，而不是收到错误。

错误:

错误:您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行“1”附近使用的正确语法

第 1 行对我来说似乎很好(因此感到困惑)。

正在运行的 PHP 代码(campaignPostDelete.php):

<?php
$con=mysqli_connect("localhost","username","password","db_name");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$postID = $_POST['postID'];

$delete = mysqli_query($con,"DELETE FROM posts WHERE postID=" . $postID);

if (!mysqli_query($con,$delete))
  {
  die('Error: ' . mysqli_error($con));
  }

header("Location: index.php");
die();

mysqli_close($con);
?>

使用 PHP 的 HTML 表单(如果需要):

<?php
$con=mysqli_connect("localhost","username","password","db_name");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$campaignID = $_SESSION['campaignID'];


$result = mysqli_query($con,"SELECT posts.postID, posts.postDate, posts.postName, posts.postEntry FROM posts 
   INNER JOIN campaigns ON posts.campaignID= $campaignID
         AND posts.campaignID= campaigns.campaignID ORDER BY postDate desc");

while($row = mysqli_fetch_array($result))
  {
  echo "<div id='campaignPostContainer'>";
  echo "<ul class='campaignPostBox'>";
  echo "<p class='postInfo'>";
  echo "<form name='postDelete' action='campaignPostDelete.php' method='post'>
            <input type='hidden' name='postID' value=" . $row['postID'] . ">
            <input type='submit'>
        </form>";
  echo "Posted on:";
  echo "<li>" . $row['postDate'] . "</li>";
  echo "</p>";
  echo "<p class='postInfo'>";
  echo "Posted by:";
  echo "<li>" . $row['postName'] . "</li>";
  echo "</p>";
  echo "<li class='postEntry'>" . $row['postEntry'] . "</li>";
  echo "</ul>";
  echo "</div>";
  echo "<hr>";
  }


mysqli_close($con);
?>

Answer 1

您将 ID 用单引号引起来。它是一个整数，因此不应用引号引起来。

$delete = mysqli_query($con,"DELETE FROM posts WHERE postID='$postID'");

应该是:

$delete = mysqli_query($con,"DELETE FROM posts WHERE postID=$postID");

但是，您还传递了两次连接字符串。因此，请这样做:

$delete = "DELETE FROM posts WHERE postID=$postID";

if (!mysqli_query($con, $delete))
{
  die('Error: ' . mysqli_error($con));
}

但这仍然会让您容易受到 SQL 注入(inject)的攻击。 至少这样做可以改善整体情况:

$delete = sprintf("DELETE FROM posts WHERE postID=%s", mysql_real_escape_string($postID));

if (!mysqli_query($con, $delete))
{
  die('Error: ' . mysqli_error($con));
}

您还需要清理其他输入。